Click here to Skip to main content
15,943,578 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 26.2K   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 
Jeff Moden is a popular poster on more SQL oriented venues that I've learned a lot from even when I thought I knew a lot about SQL. He coined a term "RBAR" (pronounced rebar) that stands for Row by agonizing row. It describes several processes that do NOT use set based logic. SQL is designed to work in set based environments and it under-performs in RBAR situations, sometimes spectacularly so. (Over a thousand times slower, generally ten times slower) You've just described one RBAR process.

Your dates in the table are unusual in that most tables that use DATETIME tables also employ the time factor in their data and almost none of their data would show up. The following scripts remove RBAR and the second one would work with REAL data too. I of course added a bogus table in my script to join:
SQL
DECLARE @DTS table(Date DATETIME NULL, Value VARCHAR(200) NULL)
insert @DTS values('2014-03-01', 'test'), ('2014-03-02', null), ('2014-03-12', 'new'), (null,'this data won''t show')
DECLARE @StartDate DATETIME = '2014-03-01', @EndDate DATETIME = GETDATE()
select @StartDate -=1

;WITH 
Dates(Date) AS
(
    SELECT @StartDate+ROW_NUMBER() over(order by a.column_id) Date FROM master.sys.all_columns a
)
SELECT d.Date, r.Value
FROM Dates d
LEFT JOIN @DTS r ON d.Date = r.Date
WHERE d.Date < @EndDate
ORDER BY d.Date

;WITH 
Dates(Date) AS
(
    SELECT @StartDate+ROW_NUMBER() over(order by a.column_id) Date FROM master.sys.all_columns a
)
,DateRange (Date, DateE) AS
(
	SELECT Date, DATEADD(MS, -2 ,Date + 1) FROM Dates
)
SELECT d.Date, r.Value
FROM DateRange d
LEFT JOIN @DTS r ON r.Date between d.Date and d.DateE
WHERE d.Date < @EndDate
ORDER BY d.Date
select * from @DTS

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.