SQL 2008 query

Question

Post reply

SQL 2008 query

techzone12

SSCommitted

Points: 1798
More actions
December 31, 2010 at 9:53 am

#231638

I have SQL 2008 server
I have tables in the database that have a unique identifier called "Date_Stamp".
I need to run a query on the table between a predifined date (say '11/1/2010' and '12/31/2010'). The output of the query should tell me if there are any missing dates (all dates are sequential, so ideally there should be no gaps). I have no idea how to do this.
What do you think experts?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

Does this "Date_Stamp" column store just the date portion, or does it include the time also?

How big of a date range are you talking about?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 2

Here's one method that will get what you want. It may not be the best performing, but depending on the size of your data it may be acceptable.

-- See how you start off by actually creating a table and then

-- inserting the data into it? Your doing this makes it a lot easier

-- for all of us volunteers to help you. So, help us help you.

-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/

-- for more details on how to do all of this.

DECLARE @MyTable TABLE (Date_Stamp DATE);

INSERT INTO @MyTable

SELECT '20101101' UNION ALL

SELECT '20101102' UNION ALL

SELECT '20101103' UNION ALL

SELECT '20101104' UNION ALL

SELECT '20101105' UNION ALL

SELECT '20101106' UNION ALL

SELECT '20101107' UNION ALL

SELECT '20101108' UNION ALL

SELECT '20101109' UNION ALL

SELECT '20101111' UNION ALL

SELECT '20101112' UNION ALL

SELECT '20101113' UNION ALL

SELECT '20101115' UNION ALL

SELECT '20101116' UNION ALL

SELECT '20101118' UNION ALL

SELECT '20101119' UNION ALL

SELECT '20101122' UNION ALL

SELECT '20101125';

-- declare and initialize some variables.

DECLARE @StartDate DATE,

@EndDate DATE,

@Dates INTEGER;

SET @StartDate = '20101101';

SET @EndDate = '20101231';

SET @Dates = DateDiff(day, @StartDate, @EndDate)+1;

;WITH

TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),

THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),

MILLIONS (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2),

TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS)

DATES AS (SELECT TOP (@Dates) N, MyDate = DateAdd(day, N-1, @StartDate) FROM TALLY)

SELECT MyDate

FROM DATES

WHERE MyDate NOT IN (SELECT Date_Stamp FROM @MyTable);

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

techzone12 SSCommitted Points: 1798 More actions · Answer 3

Just the date . Like '12-31-2010'.

Compare few months (full year) worth of data (one row for each day).

sivaj2k Ten Centuries Points: 1188 More actions · Answer 4

-- check this will resolve your solution

declare @t table (id int identity(1,1),time_stamp date)

declare @st date,@en date

select @st = '2011-01-04'

select @en = '2011-01-08'

insert into @t

select convert(varchar(10),GETDATE(),110) from INFORMATION_SCHEMA.COLUMNS

update @t set time_stamp = dateadd(day,id-1,time_stamp)

select time_stamp as Missing_Dates from @t where time_stamp between @st and @en

except

select time_stamp from <your table name> where time_stamp between @st and @en

thanks

Siva Kumar J.

techzone12 SSCommitted Points: 1798 More actions · Answer 5

I used your suggestion which is an implementation to "CELKO's" idea.

I created a table called cal with a "date_stamp" colum. This is going to be the calendar table that I will use for reference. I populate the table with dates for a full year.

SET NOCOUNT ON

DECLARE @dt SMALLDATETIME

SET @dt = '2010-01-01'

WHILE @dt < '2011-01-05'

BEGIN

INSERT into cal (date_stamp) SELECT @dt

SET @dt = @dt + 1

END

select date_stamp from cal

where date_stamp

not in

(select date_stamp from

myTable)

order by date_stamp