Is this possible with just SQL

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 2005 Forums

Transact-SQL (2005)

Is this possible with just SQL

Author

Topic

chrisg229
Starting Member

12 Posts

Posted - 2007-09-06 : 19:06:24

I need to get the count of rows that fall within a date span of seven days of any other row. The catch here is that the first date of any span will not be included in the count.

For example:
1/5/2007 (Start span so add nothing)
1/6/2007 (count = 1)
1/9/2007 (count = 2)
3/22/2007(Next start span so add nothing)
3/24/2007(count = 3)
3/25/2007(count = 4)

Does anyone know of some kind of clever SQL statement, perhaps using sub queries, that can accomplish this?

Thanks in advance.
Chris

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-09-06 : 19:39:10

what constitutes the start of a span?

what if you have records for 01/01/2007 each day to 01/15/2007, is the start of the span 01/01/2007 for the 01/15 record, or is will it be 01/08/2007 since that's 7 days prior?

Or is the span start the a specific day of each week?

chrisg229
Starting Member

12 Posts

Posted - 2007-09-06 : 19:57:14

Sorry I should have been clearer.
It would always be just the first date that starts a span even if the span continues past 7 days and only a 7 day break would call for starting a new span.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-06 : 22:19:22

quote:
Originally posted by chrisg229

Sorry I should have been clearer.
It would always be just the first date that starts a span even if the span continues past 7 days and only a 7 day break would call for starting a new span.

That is a confusing answer to Vinnie's question. What is it that starts the first span in the series for which to base the sequence thereafter?

Future guru in the making.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-07 : 08:06:19

[code]DECLARE @Sample TABLE (dt DATETIME)

INSERT @Sample
SELECT '1/5/2007' UNION ALL
SELECT '1/6/2007' UNION ALL
SELECT '1/9/2007' UNION ALL
SELECT '3/22/2007' UNION ALL
SELECT '3/24/2007' UNION ALL
SELECT '3/25/2007'

;WITH Yak (dt, cnt)
AS (
SELECT dt,
ROW_NUMBER() OVER (ORDER BY dt)
FROM @Sample
)

SELECT dt,
CASE
WHEN cnt IS NULL THEN NULL
ELSE ROW_NUMBER() OVER (PARTITION BY cnt ORDER BY dt)
END AS seq
FROM (
SELECT y1.dt,
CASE
WHEN DATEDIFF(DAY, y2.dt, y1.dt) > 7 THEN NULL
WHEN y2.cnt IS NULL THEN NULL
ELSE 1
END AS cnt
FROM Yak AS y1
LEFT JOIN Yak AS y2 ON y2.cnt = y1.cnt - 1
) AS d
ORDER BY dt[/code]

E 12°55'05.25"
N 56°04'39.16"

chrisg229
Starting Member

12 Posts

Posted - 2007-09-07 : 08:32:54

Zoroaster,

What starts the span is the first date (in ASC order) that can be identifide as having another rows date within seven days. That sequence continues as long there is a span of less than 7 days after each subsiqent date. As soon a a date is found beyond a seven day span from the previous date we look for next start date that may have its own 7 day span.

It's hard to explain so I've added to the my first example

1/5/2007 (Start span so add nothing)
1/6/2007 (count = 1)
1/9/2007 (count = 2)
1/14/2007 (count = 3)
1/20/2007 (count = 4)
1/29/2007 (span bewteen this and previous date[1/20/2007] is > 7 so no count)
3/22/2007(Next start span so add nothing)
3/24/2007(count = 5)
3/25/2007(count = 6)
3/30/2007(count = 7)
4/9/2007(span bewteen this and previous date[3/30/2007] is > 7 so no count)

....

Peso,

Thanks for the help, I'll plug this in a see what I get.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-07 : 11:06:54

[code]DECLARE @Sample TABLE (dt DATETIME)

INSERT @Sample
SELECT '1/5/2007' UNION ALL
SELECT '1/6/2007' UNION ALL
SELECT '1/9/2007' UNION ALL
SELECT '3/22/2007' UNION ALL
SELECT '3/24/2007' UNION ALL
SELECT '3/25/2007'

;WITH YAK(dt, cnt)
AS (
SELECT s1.dt,
MIN(s2.dt) AS cnt
FROM @Sample AS s1
LEFT JOIN @Sample AS s2 ON s2.dt >= DATEADD(DAY, -7, s1.dt) AND s2.dt < s1.dt
GROUP BY s1.dt
)

SELECT dt,
NULL AS Seq
FROM Yak
WHERE cnt IS NULL

UNION ALL

SELECT dt,
ROW_NUMBER() OVER (ORDER BY dt) AS cnt
FROM Yak
WHERE cnt IS NOT NULL

ORDER BY dt[/code]

E 12°55'05.25"
N 56°04'39.16"

chrisg229
Starting Member

12 Posts

Posted - 2007-09-07 : 11:58:54

Peso,
I think I understand where youre going with this.

Many thanks for all of you help!

chrisg229
Starting Member

12 Posts

Posted - 2007-09-07 : 13:15:28

I have to keep this as generic as possible. Is there something else I can use in place of
ROW_NUMBER() OVER (ORDER BY dt) AS cnt
?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-08 : 02:48:21