Display Zero if Date has no record in Database without using TempTable in SQL Server

Bhavesh23
 
on Nov 08, 2022 10:02 PM
351 Views

Display Zero if Date has no record in Database using While loop in SQL Server

Is there having any other way to do this query? Temp table use may be creating application slow or rendering problem.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Nov 09, 2022 02:06 AM

Hi Bhavesh23,

Use Common table expression.

SQL

CREATE TABLE #TempTable([Date] DATE,Record INT)
INSERT INTO #TempTable VALUES('2022/08/01', 10)
INSERT INTO #TempTable VALUES('2022/08/02', 11)
INSERT INTO #TempTable VALUES('2022/08/05', 12)
INSERT INTO #TempTable VALUES('2022/08/06', 13)
INSERT INTO #TempTable VALUES('2022/08/07', 14)

DECLARE @StartDate DATE, @EndDate DATE
SET @StartDate = '2022/08/02'
SET @EndDate = '2022/08/06'

;WITH CTE 
AS 
(
	SELECT @StartDate AS 'Date'
	UNION ALL
	SELECT DATEADD(DAY,1,[Date]) AS StartDate
	FROM CTE
	WHERE DATEADD(DAY,1,[Date]) <= @EndDate
)
SELECT *,ISNULL((SELECT Record FROM #TempTable WHERE [Date] = c.[Date]), '0') AS 'Record' FROM CTE c

DROP TABLE #TempTable

Output

Date Record
8/2/2022 11
8/3/2022 0
8/4/2022 0
8/5/2022 12
8/6/2022 13