Get Empty or NULL Data Rows with Available Data Between Given Date Range in SQL Server

In this post, we will see how to get empty or NULL data rows with available data between given date range in SQL Server.

Get empty data rows when data is not available between a given date range.

Here, take a look at the table:

In the above, the data is presented for the dates 01, 02, 03 and 07. But I also want data that has not existed in the table with empty rows. (i.e… for the dates 04, 05 and 06 with NULL)

Implementation

Query to Get Null data records when data is not present in the table SQL Server:

DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME

SET @StartDate ='2014-03-01' SET @EndDate = GETDATE()

;WITH Dates(Date) AS
(
    SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @StartDate)) AS Date
    UNION ALL
    SELECT DATEADD(day, 1, Date) AS Date
    FROM Dates
    WHERE Date <= @EndDate
)
SELECT d.Date, r.Value
FROM Dates d
LEFT JOIN Times r ON d.Date = r.Date

Full Query with table if you want to test:

declare @StartDate datetime
declare @EndDate datetime

set @StartDate ='2013-12-05'
set @EndDate =GETDATE()

CREATE TABLE Times (Date smalldatetime, Value VARCHAR(100))
INSERT INTO Times (Date, Value) values ('2013-03-05', 'test')

;WITH Dates(Date) AS
(
    SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @StartDate)) AS Date
    UNION ALL
    SELECT DATEADD(day, 1, Date) AS Date
    FROM Dates
    WHERE Date <= @EndDate
)
SELECT d.Date, r.Value
FROM Dates d
LEFT JOIN Times r ON d.Date = r.Date

DROP TABLE Times

Output

The post Get empty or NULL data rows with available data between given date range in SQL Server appeared first on Venkat Baggu Blog.