Date density for a date range - 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)

Date density for a date range

Author

Topic

sotian
Starting Member

8 Posts

Posted - 2007-02-27 : 05:02:05

Hello,

I'm using SQL Server 2000.

I wonder if anyone can help, I have a header record, and sub records. The sub records have a date.

I'm looking for an efficient way of returning header id's that have a close density of dates within a larger date range. The density is defined as n dates or more in d days - this can vary.

e.g for 3 dates in 5 days:

01 Jan 2007
05 Jan 2007
08 Jan 2007
12 Jan 2007
18 Jan 2007
20 Jan 2007
29 Jan 2007

So i'm not interested in this one there isn't any three dates within 5 days.

But for his one

08 Jan 2007
20 Jan 2007
21 Jan 2007
22 Jan 2007
23 Jan 2007
02 Feb 2007

I'm interested in this Header ID, there are actually 4 within the 5 days window between 20 - 23 Jan.

Note there could be hundreds of dates per header, and 100,000's of header records. So processing this by looping around with a cursor will probably be very slow.

I'm guessing there isn't an easy way to do this. The data will be moved across to .NET at some point, but if I could get a small a list as possible from SQL that would be great.

If the density definition was fixed I could store a list of them as the records were written, however it can vary.

Is there a way of detecting densities withing SQL server. If not, does anyone have any suggestions?

Thanks,
Jon

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 05:06:19

Which records do you want for these sample data?

08 Jan 2007
20 Jan 2007
21 Jan 2007
22 Jan 2007
23 Jan 2007
02 Feb 2007
---------------------------

1) 20-22 Jan 2007 (20, 21 or 22)
2) 21-23 Jan 2007 (21, 22 or 23)

Peter Larsson
Helsingborg, Sweden

sotian
Starting Member

8 Posts

Posted - 2007-02-27 : 05:10:09

I don't mind if this returns the header id once or twice really. The fact there is at least one group of three is good enough to return the header id.

Thanks,
Jon

quote:
Originally posted by Peso

Which records do you want for these sample data?

08 Jan 2007
20 Jan 2007
21 Jan 2007
22 Jan 2007
23 Jan 2007
02 Feb 2007
---------------------------

1) 20-22 Jan 2007 (20, 21 or 22)
2) 21-23 Jan 2007 (21, 22 or 23)

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 05:12:48

[code]-- prepare sample data
declare @t table (dt datetime)

insert @t
select '08 Jan 2007' union all
select '20 Jan 2007' union all
select '21 Jan 2007' union all
select '22 Jan 2007' union all
select '23 Jan 2007' union all
select '02 Feb 2007'

-- Example 1
SELECT t1.dt
FROM @t AS t1
INNER JOIN @t AS t2 ON t2.dt BETWEEN t1.dt - 2 AND t1.dt + 2
GROUP BY t1.dt
HAVING COUNT(t2.dt) > 2

-- Example 2
SELECT t1.dt AS FromDate,
t1.dt + 2 AS ToDate
FROM @t AS t1
INNER JOIN @t AS t2 ON t2.dt BETWEEN t1.dt AND t1.dt + 2
GROUP BY t1.dt
HAVING COUNT(t2.dt) > 2[/code]

Peter Larsson
Helsingborg, Sweden

sotian
Starting Member

8 Posts

Posted - 2007-02-27 : 05:34:34

Hello,

Thanks for your reply, that looks good. How would I adapt this to say look for 3 calls within 30 days for example:
02-Jan-2007
01-Feb-2007
10-Feb-2007
29-Feb-2007
18-Mar-2007

Returning 01-Feb-2007 - 29-Feb-2007

Thanks,
Jon

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 05:44:45

Is 29th of February 2007 available?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 05:46:50

[code]-- prepare sample data
declare @t table (dt datetime)

insert @t
select '02-Jan-2007' union all
select '01-Feb-2007' union all
select '10-Feb-2007' union all
select '28-Feb-2007' union all
select '18-Mar-2007'

-- Example
SELECT t1.dt AS FromDate,
t1.dt + 30 AS ToDate
FROM @t AS t1
INNER JOIN @t AS t2 ON t2.dt BETWEEN t1.dt AND t1.dt + 30
GROUP BY t1.dt
HAVING COUNT(t2.dt) > 2[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 05:48:08

More generic...

-- prepare sample data
declare	@t table (dt datetime)

insert	@t
select	'02-Jan-2007' union all
select	'01-Feb-2007' union all
select	'10-Feb-2007' union all
select	'28-Feb-2007' union all
select	'18-Mar-2007'


DECLARE	@DaysRange INT,
	@NumOfCalls INT

SELECT	@DaysRange = 30,
	@NumOfCalls = 3

SELECT		t1.dt AS FromDate,
		t1.dt + @DaysRange AS ToDate
FROM		@t AS t1
INNER JOIN	@t AS t2 ON t2.dt BETWEEN t1.dt AND t1.dt + @DaysRange
GROUP BY	t1.dt
HAVING		COUNT(t2.dt) >= @NumOfCalls

Peter Larsson
Helsingborg, Sweden

sotian
Starting Member

8 Posts

Posted - 2007-02-27 : 05:54:04

Peter,

Wow that's fantastic, your a genius thanks, I'll have to use those recursive joins in the future when I get my head fully around them.

There are few situations in the past where they would have been useful.

Thanks,
Jon

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-27 : 06:10:38

"I'll have to use those recursive joins in the future when I get my head fully around them"

SELF-JOIN is the more appropriate term !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 06:25:51

Thank you.

I call them SELF JOINs, not recursive JOINs.
If your columns also include TIME information, you can use this

-- prepare sample data
declare	@t table (dt datetime)

insert	@t
select	'02-Jan-2007' union all
select	'01-Feb-2007' union all
select	'10-Feb-2007' union all
select	'28-Feb-2007' union all
select	'18-Mar-2007'

DECLARE	@DaysRange INT,
	@NumOfCalls INT

SELECT	@DaysRange = 35,
	@NumOfCalls = 4

SELECT		t1.dt AS theDate
FROM		@t AS t1
CROSS JOIN	@t AS t2
WHERE			t2.dt >= DATEADD(day, DATEDIFF(day, 0, t1.dt), 0)
			AND t2.dt < DATEADD(day, DATEDIFF(day, 0, t1.dt), @DaysRange)
		OR
			t2.dt >= DATEADD(day, DATEDIFF(day, @DaysRange, t1.dt), 1)
			AND t2.dt < DATEADD(day, DATEDIFF(day, 0, t1.dt), 1)
GROUP BY	t1.dt
HAVING		COUNT(*) >= @NumOfCalls

If unsure, look for "Operator Precedence" in Books Online

Peter Larsson
Helsingborg, Sweden

sotian
Starting Member

8 Posts

Posted - 2007-02-27 : 09:18:53

So Does the original version not work with times? Why is that?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 12:23:11

When you add a number of days to a datetime, the new date has same time as original datetime.
And if you want to make sure all times for certain date are included, that is 00:00:00.000 to 23:59:59.997, it is not enough to just add a number of days to calculate, because the original date might have the time 15:15:15.467 as an example.

Peter Larsson
Helsingborg, Sweden

sotian
Starting Member

8 Posts

Posted - 2007-02-28 : 04:00:50

Ah I understand, however thats not a big deal.

The solution is implemented works well, and is very fast.

Thanks,
Jon

quote:
Originally posted by Peso

When you add a number of days to a datetime, the new date has same time as original datetime.
And if you want to make sure all times for certain date are included, that is 00:00:00.000 to 23:59:59.997, it is not enough to just add a number of days to calculate, because the original date might have the time 15:15:15.467 as an example.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-28 : 04:19:52

Great!

Try this. I believe this is even faster.

-- prepare sample data
declare	@t table (dt datetime)

insert	@t
select	'02-Jan-2007' union all
select	'01-Feb-2007' union all
select	'10-Feb-2007' union all
select	'28-Feb-2007' union all
select	'18-Mar-2007'

-- Initialize search
DECLARE	@DaysRange INT,
	@NumOfCalls INT

SELECT	@DaysRange = 35,
	@NumOfCalls = 4

SELECT		t1.dt AS theDate
FROM		@t AS t1
CROSS JOIN	@t AS t2
WHERE		t2.dt BETWEEN t1.dt - @DaysRange AND t1.dt + @DaysRange
GROUP BY	t1.dt
HAVING		COUNT(*) >= @NumOfCalls

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources