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

thuelin
Starting Member

4 Posts

Posted - 2009-04-01 : 12:21:09

Hi,
I have a table that holds employee annual leave bookings.
I wish to find the 1st date and last date of each “instance” of annual leave. Weekends & public holidays are causing me the problem.

Example:
Userid Date
1 2009-01-02
1 2009-01-05
1 2009-01-06
1 2009-03-26
1 2009-03-27

The results I am looking for are:

Date_From Date_To
2009-01-02 2009-01-06
2009-03-26 2009-03-27

I have a calendar table setup as per http://www.aspfaq.com/show.asp?id=2519.

Thank you in advance.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-04-01 : 12:37:46

Not following why the results are not

Date_FROM | Date_TO
2009-01-02 | 2009-03-27

Please explain more

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-01 : 14:04:32

see this

http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-01 : 14:29:05

what is the definition of an "instance of annual leave"? your sample data shows the Jan leave days as (mon, thur, and fri). Shouldn't that be 2 different instances?

EDIT:
Woops - sorry I was looking at the wrong month. I see those days are in fact (fri, mon, and tues). That makes more sense now

Be One with the Optimizer
TG

thuelin
Starting Member

4 Posts

Posted - 2009-04-02 : 07:56:07

Thanks for your input.

If an employee books his holiday (UK language, so bear with me!) from Fiday (2009-01-02) through to Tuesday (2009-01-06) then he is booking 3 days off, assuming the business is closed on weekends. This is what I mean by an "instance"
He then books another holiday in March for 2 days. This is another instance of annual leave.

2009-01-02 to 2009-01-06
2009-03-26 to 2009-03-27

As per the original post I would like a query to return 2 results showing the first & last day of the 2x holidays.

visakh16: I'll take a look at the article. Thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-02 : 08:21:38

try this one
declare @t table (Userid int, Date datetime)
insert into @t select 1, '2009-01-02'
insert into @t select 1, '2009-01-05'
insert into @t select 1, '2009-01-06'
insert into @t select 1, '2009-03-26'
insert into @t select 1, '2009-03-27'

select date , todate from (select *,row_number() over (partition by todate order by date)as rid
from (select date , (select max(date) from @t where month(date) = month(t.date))as todate from @t t) s)k
where rid = 1

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-02 : 08:43:12

I guess this is a much more simple way

select min(date) as startdate,MAX(date)as enddat from @t group by DATEPART(mm,date)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-02 : 08:53:38

ya that fine ayamas,
it is more simpler query that my query...............

thuelin
Starting Member

4 Posts

Posted - 2009-04-02 : 09:01:42

That does indeed work on the simple example given but if a further date in January is added (12th Jan 2009)it is incorrect.
The 12th would be another seperate instance as the employee is returning to work between the 6th and the 12th.
I hope this makes sense.

Actual Result:
2009-01-02 2009-01-12
2009-03-26 2009-03-27

Desired Result:
2009-01-02 2009-01-06
2009-01-12 2009-01-12
2009-03-26 2009-03-27

declare @t table (Userid int, Date datetime)
insert into @t select 1, '2009-01-02'
insert into @t select 1, '2009-01-05'
insert into @t select 1, '2009-01-06'
insert into @t select 1, '2009-01-12'
insert into @t select 1, '2009-03-26'
insert into @t select 1, '2009-03-27'

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-02 : 09:16:07

I fail to understand on what logic you want the expected output.As far as I think you want the startdate and enddate of continous dates in a particular month.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-02 : 14:59:40

There's got to be a better way but this is my first attempt (that works)


set nocount on
declare @t table (Userid int, Date datetime)
insert into @t select 1, '2009-01-02'
insert into @t select 1, '2009-01-05'
insert into @t select 1, '2009-01-06'
insert into @t select 1, '2009-01-12'
insert into @t select 1, '2009-03-26'
insert into @t select 1, '2009-03-27'


;with cte
as (
select c.userid
       ,c.date
       ,n.date as NextDate
       ,p.date as PrevDate
       ,row_number() over (partition by c.userid order by c.date) as rn 
from   @t c
left outer join @t n 
       on n.userid = c.userid
       and dateadd(day, case when datepart(weekday, c.date) between 2 and 5 then 1 else 3 end, c.date) = n.date
left outer join @t p
       on p.userid = c.userid
       and dateadd(day, case when datepart(weekday, c.date) between 3 and 6 then -1 else -3 end, c.date) = p.date
)
select c.date as StartDate
       ,ca.date as EndDate
from cte c
cross apply (
       select top 1 date
       from   cte
       where  userid = c.userid
       and    NextDate is null
       and    rn >= c.rn
       order by rn
       ) ca
where  PrevDate is null

OUTPUT:
StartDate               EndDate
----------------------- -----------------------
2009-01-02 00:00:00.000 2009-01-06 00:00:00.000
2009-01-12 00:00:00.000 2009-01-12 00:00:00.000
2009-03-26 00:00:00.000 2009-03-27 00:00:00.000

Be One with the Optimizer
TG

thuelin
Starting Member

4 Posts

Posted - 2009-04-06 : 05:59:44

TG: That's it! Thanks very much for your help. Most appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources