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

Bhavesh23
 
on Nov 07, 2022 10:11 PM
307 Views

I have date wise record in table now i want to display no of record from table date wise.

I implemented as below to display no of count on crystal report.

But issue is when no record on particular date that date is skipped.

I want to display zero on that date.

SELECT S_ISSUEDATE ,COUNT(*) as 'Record'  FROM STUDENT_MASTER_NEW 
WHERE CONVERT(DATETIME,S_ISSUEDATE,103) BETWEEN CONVERT(DATETIME,@FromDate,103) AND CONVERT(DATETIME,@ToDate,103) 
group by S_ISSUEDATE
order by CONVERT(DATETIME,S_ISSUEDATE ,103)

Above query result is as below. Suppose we select date range 01/08/2022 to 07/08/2022 and 3, 4 date has no record so that record is skipped.

Date              Record

01/08/2022      10

02/08/2022      15

05/08/2022      14

06/08/2022      12

07/08/2022      11

I want to display record if the 3 and 4 dates have no record then it will display as below.

Date              Record

01/08/2022     10

02/08/2022     10

03/08/2022      0

04/08/2022      0

05/08/2022     14

06/08/2022     12

07/08/2022     11

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

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

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