Monday, August 6, 2018

Missing Date

I need to check in a table, is the all day data available, here is the handy script to check the same..
DECLARE @startdate DATETIME
       , @enddate DATETIME;


SET @startdate = '07/01/2018';
SET @enddate = '08/04/2018';

WITH CalendarDates
AS ( SELECT dt = @startdate
     UNION ALL
     SELECT DATEADD(DAY, 1, dt)
     FROM calendardates
     WHERE DATEADD(DAY, 1, dt) <= @enddate
), Summary AS(
SELECT DISTINCT dt, ReportUTCDate
FROM tblData
FULL OUTER JOIN CalendarDates ON dt = ReportUTCDate
WHERE ReportUTCDate IS NULL
)
SELECT * FROM Summary

Adding here for quick reference and to share with colleagues. 

No comments:

Post a Comment

Engineering Excellence

Engineering Excellence Having solid engineering process and tools in place help to improve the agility of the system. Here is the high-level...