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

Hi Bhavesh23,

Use WHILE loop for inserting the missing Date value as zero.

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'

CREATE TABLE #TempTable1([Date] DATE,Record INT)

WHILE @StartDate <= @EndDate
BEGIN
    IF NOT EXISTS(SELECT * FROM #TempTable WHERE [Date] = @StartDate) 
        BEGIN 
            INSERT INTO #TempTable1 VALUES (@StartDate, '0')
        END
    ELSE
        BEGIN 
            INSERT INTO #TempTable1 ([Date] ,Record) 
			SELECT [Date], Record FROM #TempTable WHERE [Date] = @StartDate
        END
         
    SET @StartDate = DATEADD(DAY, 1, @StartDate)
END

SELECT * FROM #TempTable1

DROP TABLE #TempTable
DROP TABLE #TempTable1

Output