如何在一定范围内得到特定月份的天数总和

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

考虑日期格式为:m/dd/yyy

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

我怎样才能得到每个员工在特定月份假期的总和。

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

假设我想在2015年8月8日休假

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

7 = 8-2-2015和8-5-2015之间的所有天数，以及8-29-2015和8-31-2015之间的所有天数

5 个解决方案

#1

This will work for sqlserver 2012+

这将适用于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

#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

在有效评论之后更新:这将在以下日期失败:2015-07-01,2015-09-30 -@t-clausen。dk，我被认为只有一个月的需求，他会通过

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

#3

Using a Tally Table:

使用数据表:

SQL Fiddle

SQL小提琴

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.

首先，您希望使用正确的数据类型来简化计算。在我的解决方案中，我使用CTE格式化数据类型。然后建立一个计数表，从1到所有假期的最大持续时间。使用这个计数器表，在假期表上做一个交叉连接，从它的start_date到end_date生成所有的假期日期。

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

然后，添加WHERE子句来过滤属于传递的月-年参数的日期。

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:

这里，@month和@year被声明为INT，你想要的是将所有日期从一个月的第一天到最后一天。每月第一天的公式是:

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))

Some common date routines.
一些常见的例程。
More explanation on tally table.
在理货台有更多的解释。

#4

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

#5

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

#1