Подсчитайте количество дней, в течение которых каждый сотрудник отправляется в отпуск в течение месяца. SQL Server

Стол Tally / Calendar - это путь. Тем не менее, вы можете использовать таблицу таблиц ad-hoc .

пример

Select Year  = Year(D)
      ,Month = Month(D)
      ,ID
      ,Days  = count(*)
 From  Vacationtbl A
 Cross Apply (
                Select Top (DateDiff(DAY,[Start],[End])+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),[Start]) 
                 From  master..spt_values 
             ) B
 -- YOUR OPTIONAL WHERE STATEMENT HERE --
 Group By ID,Year(D),Month(D)
 Order By 1,2,3

Возвращает

Year    Month   ID  Days
2017    4       01  7
2017    4       02  7
2017    5       01  2

EDIT - Показать все ID, даже если Zero Days

Select ID
      ,Year  = Year(D)
      ,Month = Month(D)
      ,Days  = sum(case when D between [Start] and [End] then 1 else 0 end)
 From (
       Select Top (DateDiff(DAY,'05/01/2017','05/31/2017')+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),'05/01/2017')  
        From  master..spt_values 
      ) D
 Cross Join Vacationtbl  B
 Group By ID,Year(D),Month(D)
 Order By 1,2,3

Возвращает

ID  Year    Month   Days
1   2017    5       2
2   2017    5       0

dbFiddle, если это помогает

EDIT - 2 Исправляет перекрытия (пробелы и острова)

--Create Some Sample Data
----------------------------------------------------------------------
Declare @Vacationtbl Table ([ID] varchar(50),[Start] date,[End] date)
Insert Into @Vacationtbl Values
 (01,'04/10/17','04/12/17')
,(01,'04/27/17','05/02/17')
,(02,'04/13/17','04/15/17')
,(02,'04/17/17','04/20/17')
,(02,'04/16/17','04/17/17')  -- << Overlap
,(03,'05/16/17','05/17/17')

-- The Actual Query
----------------------------------------------------------------------
Select ID
      ,Year  = Year(D)
      ,Month = Month(D)
      ,Days  = sum(case when D between [Start] and [End] then 1 else 0 end)
 From (Select Top (DateDiff(DAY,'04/01/2017','04/30/2017')+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),'04/01/2017')  From  master..spt_values ) D
 Cross Join (
                Select ID,[Start] = min(D),[End] = max(D)
                 From (
                        Select E.*,Grp = Dense_Rank() over (Order By D) - Row_Number() over (Partition By ID Order By D)
                         From (
                                Select Distinct A.ID,D
                                  From  @Vacationtbl A
                                  Cross Apply (Select Top (DateDiff(DAY,A.[Start],A.[End])+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),A.[Start]) From  master..spt_values ) B
                               ) E
                      ) G
                 Group By ID,Grp    
            )  B
 Group By ID,Year(D),Month(D)
 Order By 1,2,3

Возвращает

ID  Year    Month   Days
1   2017    4       7
2   2017    4       8
3   2017    4       0

Без таблицы дат вы можете использовать

select Id
     ,sum(case when [end]>'20170430' and [start]<'20170401' then datediff(day,'20170401','20170430')+1
               when [end]>'20170430' then datediff(day,[start],'20170430')+1
               when [start]<'20170401' then datediff(day,'20170401',[end])+1
          else datediff(day,[start],[end])+1
        end) as VacationDays
from Vacationtbl
where [start] <= '20170430' and [end] >= '20170401'
group by Id

Здесь три условия

• Начало - до этого месяца, а конец - после этого месяца. В этом / * Этот рекурсивный cte генерирует даты начала и окончания месяца в течение определенного периода времени. Например: все даты начала и окончания месяца для 2017 года. Изменить начальный и конечный период по мере необходимости * / с датами (month_start_date, month_end_date) как (select cast ('2017-01-01' в качестве даты), cast (eomonth ('2017-01-01') в качестве даты) union all select dateadd (месяц, 1, month_start_date), eomonth (dateadd (месяц, 1, month_start_date)) из дат, где month_start_date <'2017-12-01') - И рекурсивная логика cte --Query такая же, как выше, выберите v.Id, year (d.month_start_date) как yr, month (d.month_start_date) как mth, sum (случай, когда v. [end]> d.month_end_date и v. [start] <d.month_start_date then dateiff (day, d.month_start_date, d.month_end_date) +1, когда v. [end]> d. month_end_date, затем dateiff (день, v. [start], d.month_end_date) +1, когда v. [start] <d.month_start_date then dateiff (day, d.month_start_date, v. [end]) + 1 else dateiff (день, v. [start], v. [end]) + 1 end) as VacationDays from date d join Vacationtbl v v v. [start] <= d.month_end_dat e и v. [end]> = d.month_start_date group по v.id, year (d.month_start_date), month (d.month_start_date) вы вычтите даты окончания и начала месяца.
• Конец заканчивается после конца месяца, а начало - в месяце, в этом случае вычитают дату окончания месяца с начала.
• Начало - до этого месяца, но конец месяца. В этом случае вычесть дату начала месяца и дату окончания.

Изменить: основываясь на комментариях OP о том, что будущие даты должны быть включены,

least()

Предполагая, что вы хотите всего один месяц, и вы хотите считать все дни, вы можете сделать это с помощью арифметики. Отдельная таблица календаря не требуется. Преимуществом является производительность.

Я думаю, что было бы проще, если бы SQL Server поддерживал greatest()и case, но caseбудет делать:

select id,
       sum(1 + datediff(day, news, newe)) as vacation_days_april
from vactiontbl v cross apply
     (values (case when [start] < '2017-04-01' then cast('2017-04-01' as date) else [start] end),
             (case when [end] >= '2017-05-01' then cast('2017-04-30' as date) else [end] end)
     ) v(news, newe)
where news <= newe
group by id;

Вы можете легко продлить это до любого месяца:

with m as (
      select cast('2017-04-01' as date) as month_start, 
             cast('2017-04-30' as date) as month_end
     )

select id,
       sum(1 + datediff(day, news, newe)) as vacation_days_aprile
from m cross join
     vactiontbl v cross apply
     (values (case when [start] < m.month_start then m.month_start else [start] end),
             (case when [end] >= m.month_end then m.month_end else [end] end)
     ) v(news, newe)
where news <= newe
group by id;

Вы даже можете использовать аналогичную идею для расширения до нескольких месяцев, с другой строкой для каждого пользователя и каждого месяца.

Вы можете использовать календарь или таблицу дат для такого рода вещей.

Для всего 152 КБ в памяти вы можете иметь 30-летние даты в таблице:

/* dates table */
declare @fromdate date = '20000101';
declare @years    int  = 30;
/* 30 years, 19 used data pages ~152kb in memory, ~264kb on disk */
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
select top (datediff(day, @fromdate,dateadd(year,@years,@fromdate)))
    [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
into dbo.Dates
from n as deka cross join n as hecto cross join n as kilo
               cross join n as tenK cross join n as hundredK
order by [Date];
create unique clustered index ix_dbo_Dates_date
  on dbo.Dates([Date]);

Не принимая фактический шаг создания таблицы, вы можете использовать ее внутри общего выражения таблицы только с этим:

declare @fromdate date = '20170401';
declare @thrudate date = '20170430';
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate, @thrudate)+1) 
      [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
  from n as deka cross join n as hecto cross join n as kilo
                cross join n as tenK cross join n as hundredK
   order by [Date]
)
select [Date]
from dates;

---

Используйте так:

select 
    v.Id
  , count(*) as VacationDays
from Vacationtbl v
  inner join Dates d
    on d.Date >= v.[Start]
   and d.Date <= v.[End]
where d.Date >= '20170401'
  and d.Date <= '20170430'
group by v.Id

демо-версия реестров (таблица): http://rextester.com/PLW73242

демонстрационный реестр (cte): http://rextester.com/BCY62752

возвращает:

+----+--------------+
| Id | VacationDays |
+----+--------------+
| 01 |            7 |
| 02 |            7 |
+----+--------------+

---

Номер и таблица календаря:

• Создать набор или последовательность без циклов - 2 - Аарон Бертран
• Таблица «Числа» или «Талли»: что это такое и как он заменяет цикл - Джефф Моден
• Создание таблицы дат / измерения в sql Server 2008 - Дэвид Штайн
• Календарные таблицы - зачем вам нужно - Дэвид Штайн
• Создание размера даты или таблицы календаря в sql Server - Аарон Бертран

Попробуй это,

declare @Vacationtbl table(ID int,Startdate date,Enddate date)
 insert into @Vacationtbl VALUES   
(1   ,'04/10/17','04/12/17')
,(1   ,'04/27/17','05/02/17')
,(2   ,'04/13/17','04/15/17')
,(2   ,'04/17/17','04/20/17')
-- somehow convert your input into first day of month
Declare @firstDayofGivenMonth date='2017-04-01'
Declare @LasttDayofGivenMonth date=dateadd(day,-1,dateadd(month,datediff(month,0,@firstDayofGivenMonth)+1,0))

;with CTE as
(
select * 
,case when Startdate<@firstDayofGivenMonth then @firstDayofGivenMonth else Startdate end NewStDT
,case when Enddate>@LasttDayofGivenMonth then @LasttDayofGivenMonth else Enddate end NewEDT
from @Vacationtbl
)

SELECT 
SUM(DATEDIFF(DAY, NewStDT, NewEDT) + 1) AS Days 
FROM 
CTE
GROUP BY 
ID