January 30, 2009 at 9:12 am
Here is my problem. I need to do a report that shows employee taking sick time off 5 days in a row.
CREATE TABLE #tempsick(Employeeid INT,
FromDate DATETIME,
ToDate DATETIME)
INSERT INTO #tempsick(Employeeid, FromDate, ToDate)
SELECT 1, '01/01/09', '01/01/09'
UNION
SELECT 1, '01/04/09', '01/04/09'
UNION
SELECT 1, '01/05/09', '01/05/09'
UNION
SELECT 1, '01/06/09', '01/06/09'
UNION
SELECT 1, '01/07/09', '01/07/09'
UNION
SELECT 1, '01/08/09', '01/08/09'
UNION
SELECT 2, '01/11/09', '01/11/09'
UNION
SELECT 2, '01/20/09', '01/20/09'
SELECT Employeeid, FromDate, ToDate,
ROW_NUMBER() OVER(PARTITION BY Employeeid ORDER BY FromDate) AS sick_sequence
FROM #tempsick
I tried to use RANK and it gave me the same result.
I want the result of employee 1 with sick day from 1/4 to 1/8.
Thanks
January 30, 2009 at 9:38 am
Does FromDate & ToDate span across more than 1 day?
--Ramesh
January 30, 2009 at 9:45 am
Why not do it as a five-layer join?
select *
from #tempsick t1
inner join #tempsick t2
on t1.employeeid = t2.employeeid
and t1.fromdate = t2.fromdate - 1
inner join #tempsick t3
on t1.employeeid = t3.employeeid
and t2.fromdate = t3.fromdate - 1
... up to t5
That'll give you any where it's five days in a row.
Do you need a different handling if it's six in a row? (That'll give two rows in this query.)
How about if it's Wed-Fri and Mon-Tue? That's not exactly "in a row", but it's five business days in a row. How about if it's three before a holiday and one after? Does that count?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 30, 2009 at 10:05 am
There is a span between fromdate to Todate but I just extract the record where fromdate = todate.
I am trying to figure out how to calculate Sat and Sunday.
January 30, 2009 at 10:58 am
Where the following date is 3 days later and is a Monday. Of course, that doesn't work for three-day weekends, etc., but it at least gets you the basic structure.
If you want to include holidays, you'll need to create a calendar table and join to that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 2, 2009 at 7:03 am
I think you need to do something like
select * from (
SELECT a.Employeeid, a.FromDate, a.ToDate,
ROW_NUMBER() OVER(PARTITION BY a.Employeeid ORDER BY a.FromDate) AS sick_sequence
FROM #tempsick a
join #tempsick b on a.fromdate=dateadd(d,1,b.todate)
) x
where sick_sequence >= 4
Otherwise you'll just get everyone as soon as the number of sick days exceeds the threshhold, since you've nothing in the basic select which restarts the row_number() after areturn to work.
Of course, this doesn't handle weekends and for that you probably do need a calendar table (as GSquared suggests). Addtionally, you don't have anything to handle ranges, but this can also use a calendar table.
To test, I had to change one of the inserts to
SELECT 1, '01/08/09', '01/09/09'
since, in your original set, 4th Jan 2009 is a Sunday, so you then don't have an unbroken sequence of 5 working days.
Here's a rough attempt using a calendar table.
--creat calendar table
create table #cal (date datetime, workingday bit, relativewd int)
-- populate it (somehow).
-- this just grabs a range using a table which is known to have quite a few rows in in
insert #cal (date)
select dateadd(d,n.n,'2008/12/31')
from (select row_number() over(order by id) as n from sysobjects) n
-- set (non-)working days
update #cal
set
workingday = case datename(weekday,date)
when 'saturday' then 0 when 'sunday' then 0 else 1 end
,relativewd = case datename(weekday,date)
when 'saturday' then 0 when 'sunday' then 0 else 1 end
-- if #cal needs holidays and other non-working days, add them here.
-- calculate the relative working day value
update #cal
set relativewd=t.rwd
from (
select
y.date,
(select sum(relativeWD) from #cal x where x.date<=y.date) as rwd
from
#cal y
) t
where #cal.date=t.date
-- now select what's required.
-- first expand ranges using the calendar table
;with n1 as (
select s.Employeeid,t.date
from #cal t
join #tempsick s on t.date between s.fromdate and s.todate
)
-- then reduce that to only working days
, n2 as (
select a.employeeid,a.date,b.relativewd as 'rwd'
from n1 a
join #cal b on a.date=b.date and b.workingday=1
)
-- finally select sequences based on relative working day, so weekends and holidays are ignored.
select a.employeeid,b.date as 'fromdate',a.date as 'todate'
from (
SELECT a.Employeeid, a.Date, a.rwd,
ROW_NUMBER() OVER(PARTITION BY a.Employeeid ORDER BY a.Date) AS sick_sequence
FROM n2 a
join n2 b on a.rwd=(b.rwd+1)
) a
join (
select min(date) as 'date', relativewd as 'rwd' from #cal group by relativewd
) b
on (a.rwd-4)=b.rwd
where sick_sequence >= 4
There are probably more efficient ways and, obviously, the calendar table (#cal) would be better as a permanent table (and you might then use it for other purposes, e.g. fast lookup of quarters, fiscal periods, etc.), but I hope this gives you something to start from.
Derek
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply