How to get the summation of days in specific month of year in range

Question

If i have Vacation table with the following structure :

emp_num start_date   end_date
 234     8-2-2015    8-5-2015
 234     6-28-2015   7-1-2015
 234     8-29-2015   9-2-2015
 115     6-7-2015    6-7-2015
 115     8-7-2015    8-10-2015

considering date format is: m/dd/yyyy

How could i get the summation of vacations for every employee during specific month .

Say i want to get the vacations in 8Aug-2015

I want the result like this

emp_num   sum
234        7
115        4

7 = all days between 8-2-2015 and 8-5-2015 plus all days between 8-29-2015 AND 8-31-2015 the end of the month

Are you using Informix or SQL Server? Please tag your question appropriately. — Gordon Linoff, Sep 16 '15 at 11:34
@GordonLinoff : the syntax is similar and i could convert , i use `informix` but i want sql server users to post their answers — Anyname Donotcare, Sep 16 '15 at 11:40
Check this answer from @FelixPamittan http://stackoverflow.com/a/32470363/2635532 . Good starting point. Similar concept you need to apply. Instead of quarter end dates you need to get month end dates. — Anup Agrawal, Sep 16 '15 at 11:44
@FelixPamittan :Could u clarify what do u mean by `How are you passing the month-year` — Anyname Donotcare, Sep 16 '15 at 11:46
@just_name, what's the format? is it two variables, one for month, one for year? — Felix Pamittan, Sep 16 '15 at 11:48
@just_name To make your question clear display the month also in the output. For emp 234 you need to say 7 holidays in august and 2 holidays in september. — Anup Agrawal, Sep 16 '15 at 11:49
the format of dates in the table `month-day-year` and i want the summations of vacations for each employee in a specific month in the year.and the result will be like the above — Anyname Donotcare, Sep 16 '15 at 11:50
@AnupAgrawal : no i pass the month and the year , in my example i want only the summation in aug-2015 — Anyname Donotcare, Sep 16 '15 at 11:51

wiretext · Answer 1 · 2015-09-16T13:08:33.003

2

i hope this will help you

declare @temp table
(emp_num int, startdate date, enddate date)


 insert into @temp values (234,'8-2-2015','8-5-2015')
 insert into @temp values (234,'6-28-2015','7-1-2015')
 insert into @temp values (234,'8-29-2015','9-2-2015')
 insert into @temp values (115,'6-7-2015','6-7-2015')
 insert into @temp values (115,'8-7-2015','8-10-2015')
-- i am passing 8 as month number in your case is August
 select emp_num,
 SUM(
 DATEDIFF (DAY , startdate,  
 case when MONTH(enddate) = 8
            then enddate
            else DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,startdate)+1,0))--end date of month
            end
 )+1) AS Vacation from @temp
 where (month(startdate) = 8 OR month(enddate) = 8) AND (Year(enddate)=2015 AND Year(enddate)=2015)
 group by emp_num

UPDATE after valid comment: This will fail with these dates: 2015-07-01, 2015-09-30 –@t-clausen.dk
i was assumed OP wants for month only which he will pass

declare @temp table
(emp_num int, startdate date, enddate date)


 insert into @temp values (234,'8-2-2015','8-5-2015')
 insert into @temp values (234,'6-28-2015','7-1-2015')
 insert into @temp values (234,'8-29-2015','9-2-2015')
 insert into @temp values (115,'6-7-2015','6-7-2015')
 insert into @temp values (115,'8-7-2015','8-10-2015')

 insert into @temp values (116,'07-01-2015','9-30-2015')


 select emp_num,
 SUM(
 DATEDIFF (DAY , startdate,  
 case when MONTH(enddate) = 8 
            then enddate
            else DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,startdate)+1,0))
            end
 )+1) AS Vacation from @temp
 where (Year(enddate)=2015 AND Year(enddate)=2015) 
 AND 8 between MONTH(startdate) AND MONTH(enddate)
 group by emp_num

edited Sep 16 '15 at 13:08

answered Sep 16 '15 at 11:58

wiretext

3,302
14
19

Wow. my solution is too complex compared to this, but I think this is correct. +1 – Felix Pamittan Sep 16 '15 at 12:00
Just add the year param though. – Felix Pamittan Sep 16 '15 at 12:00
1

@tinka : thanks but this generate wrong data because it doesn't take the year in consideration – Anyname Donotcare Sep 16 '15 at 12:25
@just_name thanks - add YEAR condition in where clause see my updated answer – wiretext Sep 16 '15 at 12:34
1

This will fail with these dates: 2015-07-01, 2015-09-30 – t-clausen.dk Sep 16 '15 at 12:38
1

@t-clausen.dk after edit my answer it is populating correct answer i am very thankful if you explain little more or edit the answer :) – wiretext Sep 16 '15 at 12:49
use the dates 2015-07-01, 2015-09-30 in your test data startdate and enddate. It will return 0 days although there is a whole month of vacation in august in that period – t-clausen.dk Sep 16 '15 at 12:51
@t-clausen.dk thanks i have updated my answer i was assumed OP want for month only – wiretext Sep 16 '15 at 13:12
1

When the dates of vacation are 2015-12-31 and 2016-01-01 and you are checking for month 12, this will also fail on both month check and year check – t-clausen.dk Sep 16 '15 at 13:15

score 1 · Answer 2 · edited May 23 '17 at 12:22

Using a Tally Table:

SQL Fiddle

DECLARE @month INT,
        @year  INT

SELECT @month = 8, @year = 2015

--SELECT
--  DATEADD(MONTH, @month - 1, DATEADD(YEAR, @year - 1900, 0)) AS start_day, 
--  DATEADD(MONTH, @month, DATEADD(YEAR, @year - 1900, 0)) AS end_d

;WITH CteVacation AS(
    SELECT 
        emp_num, 
        start_date = CONVERT(DATE, start_date, 101),
        end_date = CONVERT(DATE, end_date, 101) 
    FROM vacation
)
,E1(N) AS(
    SELECT * FROM(VALUES
        (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    )t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b),
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b),
Tally(N) AS(
    SELECT TOP(SELECT MAX(DATEDIFF(DAY, start_date, end_date)) FROM vacation)
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM E4
)
SELECT
    v.emp_num,
    COUNT(*)
FROM CteVacation v
CROSS JOIN Tally t
WHERE
    DATEADD(DAY, t.N - 1, v.start_date) <= v.end_date
    AND DATEADD(DAY, t.N - 1, v.start_date) >= DATEADD(MONTH, @month - 1, DATEADD(YEAR, @year - 1900, 0))
    AND DATEADD(DAY, t.N - 1, v.start_date) < DATEADD(MONTH, @month, DATEADD(YEAR, @year - 1900, 0))
GROUP BY v.emp_num

First, you want to use the correct data type to ease your calculation. In my solution, I used a CTE to format your data type. Then build a tally table from 1 up to the max duration of the all the vacations. Using that tally table, do a CROSS JOIN on the vacation table to generate all vacation dates from its start_date up to end_date.

After that, add a WHERE clause to filter dates that falls on the passed month-year parameter.

Here, @month and @year is declared as INT. What you want is to get all dates from the first day of the month-year up to its last day. The formula for first day of the month is:

DATEADD(MONTH, @month - 1, DATEADD(YEAR, @year - 1900, 0))

And for the last day of the month, add one month to the above and just use <:

DATEADD(MONTH, @month, DATEADD(YEAR, @year - 1900, 0))

thanks alot ,i will try it, Could u illustrate what's the meaning of `Then build a tally table from 1 up to the max duration of the all the vacations.` — Anyname Donotcare, Sep 16 '15 at 12:01
Thanks a lot +1 for your great explanation ,just it's slow than @t-clausen.dk answer — Anyname Donotcare, Sep 16 '15 at 12:35

t-clausen.dk · Accepted Answer · 2015-09-16T12:28:40.677

This will work for sqlserver 2012+

DECLARE @t table
(emp_num int, start_date date, end_date date)
INSERT @t values
( 234, '8-2-2015' , '8-5-2015'),
( 234, '6-28-2015', '7-1-2015'),
( 234, '8-29-2015', '9-2-2015'),
( 115, '6-7-2015' , '6-7-2015'),
( 115, '8-7-2015' , '8-10-2015')


DECLARE @date date = '2015-08-01'

SELECT
   emp_num,
   SUM(DATEDIFF(day, 
                CASE WHEN @date > start_date THEN @date ELSE start_date END,
                CASE WHEN EOMONTH(@date) < end_date 
                     THEN EOMONTH(@date)
                     ELSE end_date END)+1) [sum]
FROM @t
WHERE 
  start_date <= EOMONTH(@date)
  and end_date >= @date
GROUP BY emp_num

score 0 · Answer 4 · edited Sep 16 '15 at 12:32

0

Try this

  with cte(
  Select emp_num,DATEDIFF(day,start_date,end_date) AS sum_day from table_Name
  Group by emp_num,start_date,end_date
  )
  Select emp_num,sum(sum_day) as sum_day from cte group by emp_num

edited Sep 16 '15 at 12:32

MarmiK

5,639
6
40
49

answered Sep 16 '15 at 11:36

Raj Kamuni

388
2
12

score 0 · Answer 5 · answered Sep 16 '15 at 11:51

0

Select(emp_name,start_date,end_date) AS sum_day from table_Name Group by emp_num,start_date,end_date

answered Sep 16 '15 at 11:51

How to get the summation of days in specific month of year in range

5 Answers5