Calculate the number of sick days in each month over a number of months

  • 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

  • This assumes that the guy doesn't have to work 31 days of the month. I use this calendar table => http://www.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).

  • steve-433846 (11/21/2011)


    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

    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.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • @cadavre

    What about week-ends, holidays and non-production days?

    You don't realllllllllly want to work 365 days / year, do you? 😉

  • Ninja's_RGR'us (11/21/2011)


    @cadavre

    What 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.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • 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

  • 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?!?!

  • Cadavre (11/21/2011)


    Ninja's_RGR'us (11/21/2011)


    @cadavre

    What 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