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