Tomaz.tsql

WITH CTE calculating difference in days

Wanted to calculate number of days on vacation? or you want to break it down by months?

Solution for 2005/2008 using WITH CTE:

create table TBL_Vacation

(Name varchar(10)

,DateFrom smalldatetime

,DateTo smalldatetime

)

insert into TBL_Vacation

select 'Vacation1',getdate()-16, getdate()+2

union all select 'Vacation2',getdate()-25, getdate()-13

union all select 'Vacation3',getdate()-48, getdate()-36

union all select 'Vacation4',getdate()-5, getdate()+12

union all select 'Vacation5',getdate()-53, getdate()+12

 

set datefirst 1;

with f_cte (Name, month_,day_,start_day,end_,diff,vacation)

as

(

select

Name

,month_ = month(DateFrom)

,day_ = day(DateFrom)

,start_day = DateFrom

,end_ = DateTo

,diff = datediff(day, DateFrom, DateTo)

,vacation= case when datepart(dw,DateFrom) in (1,2,3,4,5) then 'work' else 'weekend' end

from TBL_Vacation

union all

select

Name

,mesec = month(start_day)

,dan = day(start_day)

,start_day = start_day + 1

,end_

,diff = diff - 1

,vacation =case when datepart(dw,start_day) in (1,2,3,4,5) then 'work' else 'weekend' end

from

f_cte

where

end_ >= start_day

 

)

select

Name

,month(start_day) as MonthOf_vacation

,count(vacation) as DayOf_vacation

from f_cte

where diff >= 0

and vacation = 'work'

group by month(start_day)

,Name

order by Name

Solution for 2000 using a derived time table:

create table TBL_Vacation

(Namevarchar(10)

,DateFromsmalldatetime

,DateTo smalldatetime

)

insert into TBL_Vacation

select 'Vacation1',getdate()-16, getdate()+2

union all select 'Vacation2',getdate()-25, getdate()-13

union all select 'Vacation3',getdate()-48, getdate()-36

union all select 'Vacation4',getdate()-5, getdate()+12

union all select 'Vacation5',getdate()-53, getdate()+12

create table TBL_Time

(date_ datetime

,y smallint

,m tinyint

,d tinyint

,work_day varchar(10)

)

set datefirst 1;

declare @int int

set @int = 0

while @int <= 2000

begin

insert into TBL_Time

select

convert(varchar(10), getdate()-1000+@int,102) as date_

,year(getdate()-1000+@int) as y

,month(getdate()-1000+@int) as m

,datepart(day,getdate()-1000+@int) as d

,case when datepart(dw,getdate()-1000+@int) in (1,2,3,4,5) then 'work' else 'weekend' end

 

set @int = @int + 1end

 

 

select

f.Name

,f.DateFrom

,f.DateTo

,count(c.Date_) as DaysOf_Vacation

,c.m

,c.work_day

from TBL_Vacation as f

cross join TBL_Time as c

where

c.date_ between f.DateFrom and f.DateTo

and c.work_day = 'work'

group by

f.name

,f.DateFrom

,f.DateTo

,c.m

,c.work_day

order by f.name, c.m

drop table TBL_Vacation

drop table TBL_Time

Which one do you fancy? Big Smile

 

Posted: Sep 20 2010, 11:49 AM by Tomaz.tsql | with no comments
Filed under: , ,

Comments

No Comments