question

DataAnalyst avatar image
DataAnalyst asked

Missing Date

Dear All, I have 'datecreated' column in cst tabel and i need to find the missing date between the given range where (cst.datecreated between '2011-07-01 00:00:00.000' AND '2011-07-12 23:59:59.997') Date exist in table 2011-07-01 2011-07-02 2011-07-05 2011-07-08 2011-07-09 2011-07-12 Result (missing date) 2011-07-03 2011-07-04 2011-07-06 2011-07-07 2011-07-10 2011-07-11 Any help regarding this is highly appreciated.Many Thanks. Regards, SG
sql-server-2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
Do you already have a query you have attempted to write that isn't working? Can you post it for us to assist?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
the easiest way I know is by using a numbers table DECLARE @DateStart DATE = '20110101' DECLARE @DateEnd DATE = '20120101' -- create a number table with all dates for required range DECLARE @numbers TABLE ( N INT NOT NULL , Ndate DATE PRIMARY KEY CLUSTERED ( N ) ) INSERT INTO @numbers ( N ) SELECT TOP ( 365 ) -- a number big enough to cover range you need ROW_NUMBER() OVER ( ORDER BY c1.column_id ) FROM master.sys.all_columns c1 CROSS JOIN master.sys.all_columns c2 UPDATE @numbers SET [Ndate] = DATEADD(d, n.[N] - 1, @DateStart) FROM @numbers AS n -- create a table with some random dates in it IF OBJECT_ID('tempdb..#TestTable', 'U') IS NOT NULL DROP TABLE #TestTable ; SELECT TOP ( 280 ) -- SomeDate = DATEADD(d, ABS(CHECKSUM(NEWID()) % 365 + 1), @DateStart) INTO #TestTable FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2 ; -- locate the dates that are missing from the #testtable by joining to a contiguous series of dates (supplied by @numbers table) and returning the rowsthat dont join SELECT [n].ndate FROM @numbers AS n LEFT JOIN [#TestTable] AS t ON [t].[SomeDate] = n.[NDate] WHERE [t].[SomeDate] IS NULL AND ( [n].[Ndate] BETWEEN @DateStart AND @DateEnd )
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.