Consecutive Day Count - 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)

Consecutive Day Count

Author

Topic

Sleepless
Starting Member

9 Posts

Posted - 2008-03-24 : 14:05:46

Hi there. New to the forums, been lurking for a bit though. Hope to get some syntax help. I have a table created that assigns a sequence number for each business day. In this table are absences, I compare the absence date with the assigned sequence number for that day and insert it for that record. What I am trying to find is anyone with 10 consecutive days absent by checking the sequence number. I was able to do this in Excel by creating a formula that looks at the previous line, checks to see if it is the same person, and that the current rows sequence is 1+ the previous row and adds 1 to the count...otherwise the count starts over again.

This is proving to be difficult (for me anyway) to reproduce in SQL. I need it in SQL so I can create a SQL custom report in Reporting Services. I have read that you can use variables to store previous row data but I know I am missing something. This is uncharted waters for me. I'm assuming that I am missing some kind of loop statement to get the counter to add to itself. Right now the statment correctly assigns a 1 if there is a consecutive day, it just doesn't add 1 more to the previous row if it is consecutive...just another 1.

Any help would be appreciated!
Thanks,
Bob

DECLARE @count int
SET @count = 0
select a.schtype,a.schname,a.stugr,a.stuln,a.stufn,a.stumn,a.stunum,a.abstype,a.absdate,a.dayseq,a.consday,
CASE WHEN (select count(t.dayseq)
from TenDayConsecutive t
where t.stunum=a.stunum and t.dayseq=(a.dayseq-1))=1
THEN @count+1
ELSE 0
END as counter
from TenDayConsecutive a

ee_rlee
Starting Member

1 Post

Posted - 2008-03-25 : 04:19:56

hi, try this

SELECT *
FROM Employees E
WHERE
EXISTS(
SELECT 1
FROM Absents A
WHERE A.EmpID=E.EmpID AND
(SELECT COUNT(*) FROM Absents WHERE EmpID=A.EmpID AND SeqID Between A.SeqID AND A.SeqID+9)>=10
)

Sleepless
Starting Member

9 Posts

Posted - 2008-03-25 : 10:30:59

OMFG!!! You ROCK!! It worked!! I've been struggling with for a couple of days racking my brain. Thank you so much for your help, I really appreciate it!!

Bob

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 11:32:20

[code]-- Prepare sample data
DECLARE @Sample TABLE (EmpID INT, dt DATETIME)

INSERT @Sample
SELECT 1, '20080325' UNION ALL
SELECT 1, '20080326' UNION ALL
SELECT 1, '20080327' UNION ALL
SELECT 2, '20080301' UNION ALL
SELECT 2, '20080309' UNION ALL
SELECT 2, '20080312' UNION ALL
SELECT 3, '20080303' UNION ALL
SELECT 3, '20080304' UNION ALL
SELECT 3, '20080315'

SELECT q.EmpID,
q.dt
FROM (
SELECT EmpID,
dt,
dt + 1 AS dtFrom,
dt + 10 AS dtTo
FROM @Sample
) AS q
LEFT JOIN @Sample AS s ON s.EmpID = q.EmpID
AND s.dt BETWEEN q.dtFrom AND q.dtTo
INNER JOIN (
SELECT EmpID,
MAX(dt) AS maxdt
FROM @Sample
GROUP BY EmpID
) AS y ON y.EmpID = q.EmpID
WHERE q.dt < y.maxdt
AND s.EmpID IS NULL[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 11:59:48

[code]SELECT s.EmpID,
s.dt
FROM @Sample AS s
WHERE NOT EXISTS (SELECT * FROM @Sample AS x WHERE x.EmpID = s.EmpID AND (x.dt BETWEEN s.dt + 1 AND s.dt + 10 OR x.dt BETWEEN s.dt - 10 AND s.dt - 1))[/code]

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

Sleepless
Starting Member

9 Posts

Posted - 2008-03-26 : 09:47:42

More than one way to skin a cat. Thanks all!!

rdavidson
Starting Member

1 Post

Posted - 2011-06-14 : 15:29:16

Sorry to bring up this old topic again, but I am trying to the exact same thing. With these examples I have been able to successfully reproduce a similar query, however I require the count of consucetive days in the results set. My query is built almost exactly like ee_rlee's reply just a little more complicated. So using his reply how would one get the count of consecutive days in the results set? Is this even possible without a cursor?

SELECT *
FROM Employees E
WHERE
EXISTS(
SELECT 1
FROM Absents A
WHERE A.EmpID=E.EmpID AND
(SELECT COUNT(*) FROM Absents WHERE EmpID=A.EmpID AND SeqID Between A.SeqID AND A.SeqID+9)>=10
)
If you wish to see specific information about my exact query please see this thread:
http://forums.asp.net/t/1689502.aspx/1?Return+consecutive+count

BTW- I'm actually on SQL 2008, just didnt think there was a need to start a new thread.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources