Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2012
    Posts
    10

    Unanswered: Absence Calculation

    Hi, I need to calculate monthly absence days for an employee using SQL Server 2008.

    Need to calculate the number of absence days for each month when start and end of the absence dates are given

    INPUT: 01/15/2010 05/25/2010

    OUTPUT:
    Jan 16
    Feb 28
    Mar 31
    APR 30
    May 25

    Can anyone of you help me out ?

    Thanks in Advance!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why output for Jan not "Jan 31"?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Weekends? Holidays?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    What causes the posted "output" dates to be shown?

    The question is quite unclear.

  5. #5
    Join Date
    Mar 2012
    Posts
    10
    Hi, Please find the clarification -
    No worries about weekends and holidays.. everything will be included in the count..
    Input would be
    absence start date -01/15/2010
    Absence End date - 05/25/2010

    Employee goes on leave starting from 01/15. And he is returning on 05/25.
    So for january month, number of absence days is 16. For feb, he is not available for the entire month. so, total would be 28. And for mar - 31 and so on. He is returning on 05/25. so for May, Number of absence days is 25. For june - 0. etc

    Output: Need total number of absence taken by monthly basis

    Total number of Absence days for January(01/31/2012 - 01/15/2012) - 16
    Total number of Absence days for Feb - 28
    Total number of Absence days for Mar - 31
    Total number of Absence days for April - 30
    Total number of Absence days for May (05/31/2012 - 05/25/2012) - 25
    Total number of Absence days for June - 0
    Total number of Absence days for July - 0
    Total number of Absence days for Aug- 0
    .....

    Total number of absences - 16+28+31+30+25

  6. #6
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    The correct result for January is 16 days?
    Or 17 days (days 15 and 31 inclusive)?

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    NZDF solutions rock!
    Code:
    DECLARE @d1 DATETIME, @d2 DATETIME
    
    SELECT @d1 = '01/15/2010', @d2 = '05/25/2010'
    
    SELECT Left(DateName(mm, dateAdd(month, token, 0)), 3), COUNT(*)
       FROM (SELECT DateDiff(month, 0, DATEADD(day, a.number, @d1)) AS token
    ,        DATEADD(day, a.number, @d1) AS target
          FROM master.dbo.spt_values AS a
          WHERE  'P' = a.type
             AND 0 < number) AS b
       WHERE target <= @d2
       GROUP BY token
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Mar 2012
    Posts
    10
    Hi, It works!!!! Thank you so much!!!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •