Detecting gaps in dates - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Detecting gaps in dates

Author

Topic

Antonio
Posting Yak Master

168 Posts

Posted - 2006-10-12 : 06:53:31

Hi there,

I have a simple table consisting of two columns :

Cost Centre.....Target_Date

1180............20061001
1180............20061002
1180............20061003
1180............20061004
1180............20061005
1180............20061006
1180............20061007
1180............20061008

...etc

What I want to do is find whether there gaps in the dates for other cost centre number as I only want to select cost centres that have 2 moths worth of consecutive dates.

Is there a solution that doesn't use a temporary table with a complete list of dates?

Thanks for your help in advance.

_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-12 : 07:07:07

Are the dates stored as DATETIME/SMALLDATETIME or INT?

Peter Larsson
Helsingborg, Sweden

Antonio
Posting Yak Master

168 Posts

Posted - 2006-10-12 : 07:12:01

quote:
Originally posted by Peso

Are the dates stored as DATETIME/SMALLDATETIME or INT?

Peter Larsson
Helsingborg, Sweden

They are stored as integers.

_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-12 : 07:15:49

Wrong answer!

Wait some minutes...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-12 : 07:33:31

[code]-- Prepare test data
DECLARE @Test TABLE (CostCentre INT, TargetDate INT)

INSERT @Test
SELECT 1180, 20061001 UNION ALL
SELECT 1180, 20061002 UNION ALL
SELECT 1180, 20061003 UNION ALL
SELECT 1180, 20061005 UNION ALL
SELECT 1180, 20061006 UNION ALL
SELECT 1180, 20061008 UNION ALL
SELECT 1180, 20061009 UNION ALL
SELECT 1180, 20061010

-- Prepare platform
declare @Consecutive tinyint

select @consecutive = 3

-- Do the work
select d1.CostCentre,
d1.TargetDate
from (
SELECT DISTINCT CostCentre,
DATEADD(month, TargetDate % 10000 / 100 - 1, DATEADD(day, TargetDate % 100 - 1, DATEADD(year, TargetDate / 10000 - 1900, 0))) TargetDate
FROM @Test
) d1
inner join (
SELECT DISTINCT CostCentre,
DATEADD(month, TargetDate % 10000 / 100 - 1, DATEADD(day, TargetDate % 100 - 1, DATEADD(year, TargetDate / 10000 - 1900, 0))) TargetDate
FROM @Test
) d2 on d2.costcentre = d1.costcentre and d2.targetdate between d1.targetdate and dateadd(day, @consecutive - 1, d1.targetdate)
group by d1.CostCentre,
d1.TargetDate
having count(*) = @consecutive[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-12 : 08:03:42

Oops! I read your question more closely now, and had to rewrite last select to DISTINCT.

-- Prepare test data
DECLARE	@Test TABLE (CostCentre INT, TargetDate INT)

INSERT	@Test
SELECT	1180, 20061001 UNION ALL
SELECT	1180, 20061002 UNION ALL
SELECT	1180, 20061003 UNION ALL
SELECT	1180, 20061005 UNION ALL
SELECT	1180, 20061006 UNION ALL
SELECT	1181, 20061002 UNION ALL
SELECT	1181, 20061003 UNION ALL
SELECT	1181, 20061005 UNION ALL
SELECT	1181, 20061006 UNION ALL
SELECT	1181, 20061007 UNION ALL
SELECT	1181, 20061008 UNION ALL
SELECT	1181, 20061010

-- Prepare platform
declare @Consecutive tinyint 

select @consecutive = 3

-- Do the work
select distinct	d1.CostCentre
from		(
			SELECT DISTINCT	CostCentre,
					DATEADD(month, TargetDate % 10000 / 100 - 1, DATEADD(day, TargetDate % 100 - 1, DATEADD(year, TargetDate / 10000 - 1900, 0))) TargetDate
			FROM		@Test
		) d1
inner join	(
			SELECT DISTINCT	CostCentre,
					DATEADD(month, TargetDate % 10000 / 100 - 1, DATEADD(day, TargetDate % 100 - 1, DATEADD(year, TargetDate / 10000 - 1900, 0))) TargetDate
			FROM		@Test
		) d2 on d2.costcentre = d1.costcentre and d2.targetdate between d1.targetdate and dateadd(day, @consecutive - 1, d1.targetdate)
group by	d1.CostCentre,
		d1.TargetDate
having		count(*) = @consecutive

Peter Larsson
Helsingborg, Sweden

Antonio
Posting Yak Master

168 Posts

Posted - 2006-10-12 : 08:04:05

quote:

Originally posted by Peso

-- Prepare test data
DECLARE	@Test TABLE (CostCentre INT, TargetDate INT)

INSERT	@Test
SELECT	1180, 20061001 UNION ALL
SELECT	1180, 20061002 UNION ALL
SELECT	1180, 20061003 UNION ALL
SELECT	1180, 20061005 UNION ALL
SELECT	1180, 20061006 UNION ALL
SELECT	1180, 20061008 UNION ALL
SELECT	1180, 20061009 UNION ALL
SELECT	1180, 20061010

-- Prepare platform
declare @Consecutive tinyint 

select @consecutive = 3

-- Do the work
select		d1.CostCentre,
		d1.TargetDate
from		(
			SELECT DISTINCT	CostCentre,
					DATEADD(month, TargetDate % 10000 / 100 - 1, DATEADD(day, TargetDate % 100 - 1, DATEADD(year, TargetDate / 10000 - 1900, 0))) TargetDate
			FROM		@Test
		) d1
inner join	(
			SELECT DISTINCT	CostCentre,
					DATEADD(month, TargetDate % 10000 / 100 - 1, DATEADD(day, TargetDate % 100 - 1, DATEADD(year, TargetDate / 10000 - 1900, 0))) TargetDate
			FROM		@Test
		) d2 on d2.costcentre = d1.costcentre and d2.targetdate between d1.targetdate and dateadd(day, @consecutive - 1, d1.targetdate)
group by	d1.CostCentre,
		d1.TargetDate
having		count(*) = @consecutive

Peter Larsson
Helsingborg, Sweden

Hi Peso,

I have just tried your solution and it seems to work. I am now trying to understand what exactly is going on within the code. Will get back to you once I've understood the query.

Thanks for your time and effort. Much appreciated!

_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-12 : 08:16:27

Here is a more effective query

-- Prepare test data
DECLARE	@Test TABLE (CostCentre INT, TargetDate INT)

INSERT	@Test
SELECT	1180, 20061001 UNION ALL
SELECT	1180, 20061002 UNION ALL
SELECT	1180, 20061003 UNION ALL
SELECT	1180, 20061003 UNION ALL
SELECT	1180, 20061005 UNION ALL
SELECT	1180, 20061006 UNION ALL
SELECT	1181, 20061002 UNION ALL
SELECT	1181, 20061003 UNION ALL
SELECT	1181, 20061005 UNION ALL
SELECT	1181, 20061006 UNION ALL
SELECT	1181, 20061007 UNION ALL
SELECT	1181, 20061008 UNION ALL
SELECT	1181, 20061010

-- Prepare platform
declare @Consecutive tinyint 

select @consecutive = 3

-- Do the work
select distinct	d1.CostCentre
from		(
			SELECT	CostCentre,
				DATEADD(month, TargetDate % 10000 / 100 - 1, DATEADD(day, TargetDate % 100 - 1, DATEADD(year, TargetDate / 10000 - 1900, 0))) TargetDate
			FROM	@Test
		) d1
inner join	(
			SELECT	CostCentre,
				DATEADD(month, TargetDate % 10000 / 100 - 1, DATEADD(day, TargetDate % 100 - 1, DATEADD(year, TargetDate / 10000 - 1900, 0))) TargetDate
			FROM	@Test
		) d2 on d2.costcentre = d1.costcentre and d2.targetdate between d1.targetdate and dateadd(day, @consecutive - 1, d1.targetdate)
group by	d1.CostCentre,
		d1.TargetDate
having		count(distinct d2.targetdate) = @consecutive

The reason for having derived tables is that your dates are stored int, and hence I can't check for if 20061101 is consecutive to 20061031 because of the 70 number differences.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-13 : 10:36:25

>>They are stored as integers.

Use proper Datatype DATETIME

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources