Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / SQL

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

5.00/5 (1 vote)
9 Feb 2015CPOL 26.5K  
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:

Image 1

Get Null data record when data is not present in SQL Server

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:

SQL
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:

SQL
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

Image 2

Get Null data record when data is not present in SQL Server

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)