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

Transact-SQL (2005)

Consecutive Dates

Author

Topic

stormcandi
Starting Member

46 Posts

Posted - 2007-06-05 : 14:26:44

Hello all,

I need to find a query that will query a table I have. The table stores students, dates, and if they were absent or tardy on that date. (I do not store anything if they are Present.)

I need to query if a student was absent for 3 or more consecutive dates and what the dates were. (I need to know any 3 consecutive dates that occured, so I am not looking at a specific start date.) Also I need to take into account Saturday and Sunday. So if a student is out Mon, Tues, Wed it shows that student or if the student is out on Fri, Mon, Tues it also shows that student.

I am not even sure where to begin on this one. Can anyone assist?

Thanks in advance!

Candi

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-06-05 : 15:36:24

Here's one way :

select startdate,userid
from attendence us1
where exists
(
select startdate,userid
from attendence us2
where us1.userid=us2.userid
and datediff(day,us1.startdate,us2.startdate)=1
)
and exists
(
select startdate,userid
from attendence us3
where us1.userid=us3.userid
and datediff(day,us1.startdate,us3.startdate)=3
)

stormcandi
Starting Member

46 Posts

Posted - 2007-06-05 : 18:37:06

Thanks rudesyle! I was able to make it work (by changing the 3 to a 2 since it will be 2 days after the first "occurance".

This helped alot and works as a great starting point for other things I need to do!!!

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-06-06 : 04:45:55

Here's a little addition to account for weekends. Note that this assumes Sunday is the first day of the week (i.e. SET DATEFIRST 7). Check your setting using SELECT @@DATEFIRST and amend the offsets if required.

select startdate,userid
from attendence us1
where exists
(
select startdate,userid
from attendence us2
where us1.userid=us2.userid 
and datediff(day,us1.startdate,us2.startdate)=  case 
                                                    when datepart(weekday, us1.startdate) = 6 
                                                    then 3 
                                                    else 1 
                                                end
)
and exists
(
select startdate,userid
from attendence us3
where us1.userid=us3.userid 
and datediff(day,us1.startdate,us3.startdate)=  case 
                                                    when datepart(weekday, us1.startdate) in (5, 6) 
                                                    then 4 
                                                    else 2 
                                                end
)

Mark

stormcandi
Starting Member

46 Posts

Posted - 2007-06-07 : 11:56:42

Thanks mwjdavidson! I was looking at it for the last few days trying to figure out how to do it, but was hitting a wall.

Thanks again!

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-06-07 : 12:11:29

no worries.

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources