How to find missing date range

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to find missing date range

Author

Topic

TechAbhi
Starting Member

8 Posts

Posted - 2010-08-12 : 17:58:57

Hello All,

I have a table with date start and date end, and i have about 1000 rows. How do i find out the missing range.
Ex:
2008-11-18 00:00:00 2008-11-23 00:00:00
2008-11-24 00:00:00 2008-11-30 00:00:00
2008-12-01 00:00:00 2008-12-07 00:00:00
2008-12-08 00:00:00 2008-12-14 00:00:00
2008-12-22 00:00:00 2008-12-28 00:00:00

If there is a missing entry in this table how do i find it.
Ex: Missing Entry :
2008-12-15 00:00:00 2008-12-21 00:00:00

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-08-12 : 18:33:52

Try this. If you do not use SQL Serve 2005/2008 change APPLT to Subqery.

SELECT DATEADD(DAY, 1, T1.end_date) AS start_date,
       DATEADD(DAY, -1, D.start_date) AS end_date
  FROM table_name AS T1
    CROSS APPLY 
      (SELECT MIN(start_date) AS start_date
         FROM table_name AS T
        WHERE T.start_date > T1.start_date) D
 WHERE DATEADD(DAY, 1, T1.end_date) <> D.start_date

______________________

TechAbhi
Starting Member

8 Posts

Posted - 2010-08-13 : 11:24:40

Nope this does not work!

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-08-13 : 11:31:28

quote:
Originally posted by TechAbhi

Nope this does not work!

Which edition of SQL Server are you using?
I have tested it. It was correct.

______________________

TechAbhi
Starting Member

8 Posts

Posted - 2010-08-13 : 12:08:01

I am working on sql 2005. I created a view to show me just the start_date and end_date.
ID Start_date End_Date
1040 1/25/2008 0:00 1/31/2008 0:00
1040 2/23/2008 0:00 2/29/2008 0:00
1040 3/25/2008 0:00 3/31/2008 0:00
1040 4/24/2008 0:00 4/30/2008 0:00
1040 5/25/2008 0:00 5/31/2008 0:00
1040 6/24/2008 0:00 6/30/2008 0:00
1040 7/25/2008 0:00 7/31/2008 0:00
1040 8/25/2008 0:00 8/31/2008 0:00
1040 9/24/2008 0:00 9/30/2008 0:00
1040 10/25/2008 0:00 10/31/2008 0:00
1040 11/24/2008 0:00 11/30/2008 0:00
1040 12/25/2008 0:00 12/31/2008 0:00

And when i run this query:
SELECT contract_id,DATEADD(DAY, 1, T1.end_date) AS start_date,
DATEADD(DAY, -1, D.start_date) AS end_date
FROM table_name AS T1
CROSS APPLY
(SELECT MIN(start_date) AS start_date
FROM table_name AS T
WHERE T.start_date > T1.start_date) D
WHERE DATEADD(DAY, 1, T1.end_date) <> D.start_date
group by contract_id, T1.end_date, D.start_date

This is the result i get:

1040 2008-02-01 00:00:00 2008-02-22 00:00:00
1040 2008-03-01 00:00:00 2008-03-24 00:00:00
1040 2008-04-01 00:00:00 2008-04-23 00:00:00
1040 2008-05-01 00:00:00 2008-05-24 00:00:00
1040 2008-06-01 00:00:00 2008-06-23 00:00:00
1040 2008-07-01 00:00:00 2008-07-24 00:00:00
1040 2008-08-01 00:00:00 2008-08-24 00:00:00
1040 2008-09-01 00:00:00 2008-09-23 00:00:00
1040 2008-10-01 00:00:00 2008-10-24 00:00:00
1040 2008-11-01 00:00:00 2008-11-23 00:00:00
1040 2008-12-01 00:00:00 2008-12-24 00:00:00

This gives me missing start date and end date. Is there a way to break that in a date range?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-13 : 12:10:59

quote:
Originally posted by TechAbhi

This is the result i get:

1040 2008-02-01 00:00:00 2008-02-22 00:00:00
1040 2008-03-01 00:00:00 2008-03-24 00:00:00
1040 2008-04-01 00:00:00 2008-04-23 00:00:00
1040 2008-05-01 00:00:00 2008-05-24 00:00:00
1040 2008-06-01 00:00:00 2008-06-23 00:00:00
1040 2008-07-01 00:00:00 2008-07-24 00:00:00
1040 2008-08-01 00:00:00 2008-08-24 00:00:00
1040 2008-09-01 00:00:00 2008-09-23 00:00:00
1040 2008-10-01 00:00:00 2008-10-24 00:00:00
1040 2008-11-01 00:00:00 2008-11-23 00:00:00
1040 2008-12-01 00:00:00 2008-12-24 00:00:00

This gives me missing start date and end date. Is there a way to break that in a date range?

I'm confused. It already is in a date range. So, what do you want exactly?

TechAbhi
Starting Member

8 Posts

Posted - 2010-08-13 : 12:14:37

Sorry should have elaborated more on that. I mean weekly date range like
2/1/2008 2/7/2008
2/8/2008 2/14/2008
2/15/2008 2/21/2008

TechAbhi
Starting Member

8 Posts

Posted - 2010-08-13 : 13:07:26

And this statement fails when there are more than one id's. Example:
1040 2008-02-01 00:00:00 2008-01-27 00:00:00
1040 2008-03-01 00:00:00 2008-02-24 00:00:00
1040 2008-04-01 00:00:00 2008-03-30 00:00:00
1040 2008-05-01 00:00:00 2008-04-27 00:00:00
1040 2008-06-01 00:00:00 2008-05-25 00:00:00
1040 2008-07-01 00:00:00 2008-06-29 00:00:00
1040 2008-08-01 00:00:00 2008-07-27 00:00:00
1040 2008-10-01 00:00:00 2008-09-28 00:00:00
1040 2008-11-01 00:00:00 2008-10-26 00:00:00
1040 2009-01-01 00:00:00 2008-12-28 00:00:00
1041 2008-01-28 00:00:00 2008-01-24 00:00:00
1041 2008-02-25 00:00:00 2008-02-22 00:00:00
1041 2008-03-31 00:00:00 2008-03-24 00:00:00
1041 2008-04-28 00:00:00 2008-04-23 00:00:00
1041 2008-05-26 00:00:00 2008-05-24 00:00:00
1041 2008-06-30 00:00:00 2008-06-23 00:00:00
1041 2008-07-28 00:00:00 2008-07-24 00:00:00
1041 2008-09-29 00:00:00 2008-09-23 00:00:00
1041 2008-10-27 00:00:00 2008-10-24 00:00:00
1041 2008-12-29 00:00:00 2008-12-24 00:00:00
1041 2009-05-12 00:00:00 2009-05-10 00:00:00
1041 2009-07-21 00:00:00 2009-07-19 00:00:00
1041 2010-04-19 00:00:00 2010-04-25 00:00:00
1041 2010-06-07 00:00:00 2010-06-20 00:00:00

And when i run a query on this table:

1040 2008-02-01 00:00:00 2008-01-27 00:00:00
1040 2008-03-01 00:00:00 2008-02-24 00:00:00
1040 2008-04-01 00:00:00 2008-03-30 00:00:00
1040 2008-05-01 00:00:00 2008-04-27 00:00:00
1040 2008-06-01 00:00:00 2008-05-25 00:00:00
1040 2008-07-01 00:00:00 2008-06-29 00:00:00
1040 2008-08-01 00:00:00 2008-07-27 00:00:00
1040 2008-10-01 00:00:00 2008-09-28 00:00:00
1040 2008-11-01 00:00:00 2008-10-26 00:00:00
1040 2009-01-01 00:00:00 2008-12-28 00:00:00
1041 2008-01-28 00:00:00 2008-01-24 00:00:00
1041 2008-02-25 00:00:00 2008-02-22 00:00:00
1041 2008-03-31 00:00:00 2008-03-24 00:00:00
1041 2008-04-28 00:00:00 2008-04-23 00:00:00
1041 2008-05-26 00:00:00 2008-05-24 00:00:00
1041 2008-06-30 00:00:00 2008-06-23 00:00:00
1041 2008-07-28 00:00:00 2008-07-24 00:00:00
1041 2008-09-29 00:00:00 2008-09-23 00:00:00
1041 2008-10-27 00:00:00 2008-10-24 00:00:00
1041 2008-12-29 00:00:00 2008-12-24 00:00:00
1041 2009-05-12 00:00:00 2009-05-10 00:00:00
1041 2009-07-21 00:00:00 2009-07-19 00:00:00
1041 2010-04-19 00:00:00 2010-04-25 00:00:00
1041 2010-06-07 00:00:00 2010-06-20 00:00:00

So the cross apply fails to validate the id here, trying to figure out how to solve this problem.

TechAbhi
Starting Member

8 Posts

Posted - 2010-08-13 : 13:29:43

Ok its working now, tweaked the query.

SELECT
contract_id,project_id,title,
DATEADD(DAY, 1, T1.end_date) AS start_date,
DATEADD(DAY, -1, D.start_date) AS end_date
FROM table_name AS T1
CROSS APPLY
(SELECT MIN(start_date) AS start_date
FROM table_name AS T
WHERE T.start_date > T1.start_date
and T.contract_id = T1.contract_id) D
WHERE DATEADD(DAY, 1, T1.end_date) <> D.start_date
group by contract_id, T1.end_date, D.start_date, project_id,title

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources