November 21, 2011 at 5:05 am
I am trying to work out how many days a person has had off sick in each month
quite easy when the sickness is just in one month, can anyone advise me on how to do it when the sick ness is over say 4 months
IE
first day of sickness is 17/10/2010
last day of sickness is 24/01/2011
basically, this person now has sick days in October / November / December and January
I need to show that he had
15 days sick in October
30 Days sick in November
31 days sick in December and
24 days sick in January
Help someone can help
Steve
November 21, 2011 at 5:17 am
This assumes that the guy doesn't have to work 31 days of the month. I use this calendar table => http://qa.sqlservercentral.com/Forums/Attachment8839.aspx
BEGIN TRAN
--IE
--first day of sickness is 17/10/2010
--last day of sickness is 24/01/2011
--just added for clarity and different environements.
SET DATEFORMAT YMD
--SELECT * FROM dbo.Calendar C WHERE C.dt BETWEEN '2010-10-17' AND '2011-01-24'
SELECT
C.Y
, C.M
, C.monthname
, SUM(C.IsBusDay) AS CntSickDays
FROM
dbo.Calendar C
WHERE
C.dt BETWEEN '2010-10-17' AND '2011-01-24'
GROUP BY
C.Y
, C.M
, C.monthname
ORDER BY
C.Y
, C.M
ROLLBACK
You can use the same code in a join (I'd possibly use outer apply here).
November 21, 2011 at 5:20 am
steve-433846 (11/21/2011)
I am trying to work out how many days a person has had off sick in each monthquite easy when the sickness is just in one month, can anyone advise me on how to do it when the sick ness is over say 4 months
IE
first day of sickness is 17/10/2010
last day of sickness is 24/01/2011
basically, this person now has sick days in October / November / December and January
I need to show that he had
15 days sick in October
30 Days sick in November
31 days sick in December and
24 days sick in January
Help someone can help
Steve
Your question is flawed, unless you are actually expecting people to work every day of the month.
DECLARE @firstDayOfSickness DATETIME, @lastDayOfSickness DATETIME
SET @firstDayOfSickness = '2010-10-17'
SET @lastDayOfSickness = '2011-01-24'
;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
tally AS (SELECT DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),'2000-01-01') AS dates
FROM t4 x, t4 y)
SELECT CAST(SickDays AS VARCHAR(2)) + ' days sick in ' + DATENAME(MONTH,dates) + ' ' + DATENAME(YEAR,dates)
FROM (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, dates), 0) AS dates, COUNT(*) AS SickDays
FROM tally
WHERE dates >= @firstDayOfSickness AND dates <= @lastDayOfSickness
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, dates), 0)) work
ORDER BY dates
--EDIT--
Beaten to the punch. BTW, the above post is correct. Use a calendar table.
November 21, 2011 at 5:24 am
Ninja's_RGR'us (11/21/2011)
@cadavreWhat about week-ends, holidays and non-production days?
You don't realllllllllly want to work 365 days / year, do you? 😉
I did write "Your question is flawed".
Either way, the on the fly calendar table I used is always going to be slower than a properly constructed calendar table as you used.
November 21, 2011 at 5:34 am
Thanks for this I will try those solutions now,
about the question being flawed, not really, but maybe the way I explained what I wanted was. I am counting the total numbers of days a person has been signed on the sick, not the total number of workdays that he is sick,
Once again thanks for your very prompt replies
S
November 21, 2011 at 5:37 am
steve-433846 (11/21/2011)
Thanks for this I will try those solutions now,about the question being flawed, not really, but maybe the way I explained what I wanted was. I am counting the total numbers of days a person has been signed on the sick, not the total number of workdays that he is sick,
Once again thanks for your very prompt replies
S
Then what's wrong with a simple datediff?
If you only need to see the cnt of days, there should be no need to list them by month?!?!
November 21, 2011 at 5:37 am
Cadavre (11/21/2011)
Ninja's_RGR'us (11/21/2011)
@cadavreWhat about week-ends, holidays and non-production days?
You don't realllllllllly want to work 365 days / year, do you? 😉
I did write "Your question is flawed".
Either way, the on the fly calendar table I used is always going to be slower than a properly constructed calendar table as you used.
Must have missed that disclaimer. The code spoke too loud apparently ;-).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply