1. Registered User
Join Date
Mar 2012
Posts
10

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 ?

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

3. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Weekends? Holidays?

4. Registered User
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. Registered User
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. Registered User
Join Date
Apr 2012
Posts
213
Hi,

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

7. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

8. Registered User
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
•