Click here to Skip to main content
15,868,141 members
Articles / Programming Languages / SQL

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

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
9 Feb 2015CPOL 25.7K   1   1
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)


Written By
Software Developer (Senior) eBiz Solutions http://venkatbaggu.com/
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionSome comments Pin
KP Lee10-Feb-15 13:56
KP Lee10-Feb-15 13:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.