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

Teams PowerShell

 The PowerShell helps to get quick meta data around Teams. Install-Module -Name MicrosoftTeams Connect-MicrosoftTeams Get-TeamAllChannel...