All geek questions in one place

Count the number of days that each employee goes on vacation for a month. SQL Server

I have this table:

Vacationtbl

    ID   Start      End
    -------------------------
    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
    03   06/14/17   06/22/17

Employeetbl:

ID   Fname   Lname
------------------
01   John    AAA
02   Jeny    BBB
03   Jeby    CCC

I like to count the number of days that each employee goes on vacation in April.

My request:

SELECT 
    SUM(DATEDIFF(DAY, Start, End) + 1) AS Days 
FROM 
    Vacationtbl
GROUP BY 
    ID
  • 01returns 9(invalid)
  • 02returns 7(correct)

How to fix a request so that it is taken into account before the end of the month and ends at the end of the month. For example, April has 30 days. In the second line, Employee 01should count 4/27/17to 4/30/17. A 05/02/17- May.

thank

+4
source share
5 answers

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

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

- 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
+3

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

3

  • - , - . .
  • , - , .
  • - , . .

: OP, ,

/*This recursive cte generates the month start and end dates with in a given time frame
For Eg: all the month start and end dates for 2017
Change the start and end period as needed*/
with dates (month_start_date,month_end_date) as 
(select cast('2017-01-01' as date),cast(eomonth('2017-01-01') as date)
 union all
 select dateadd(month,1,month_start_date),eomonth(dateadd(month,1,month_start_date))  from dates
 where month_start_date < '2017-12-01'
)
--End recursive cte
--Query logic is the same as above
select v.Id
,year(d.month_start_date) as yr,month(d.month_start_date) as mth
,sum(case when v.[end]>d.month_end_date and v.[start]<d.month_start_date then datediff(day,d.month_start_date,d.month_end_date)+1
          when v.[end]>d.month_end_date then datediff(day,v.[start],d.month_end_date)+1
          when v.[start]<d.month_start_date then datediff(day,d.month_start_date,v.[end])+1
     else datediff(day,v.[start],v.[end])+1
     end) as VacationDays
from dates d
join Vacationtbl v on v.[start] <= d.month_end_date and v.[end] >= d.month_start_date
group by v.id,year(d.month_start_date),month(d.month_start_date)
+1

.

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

rextester demo (cte): http://rextester.com/BCY62752

:

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

:

0

, , , . . .

, , SQL Server least() greatest(), 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;

, , .

0

,

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
0

Source: https://habr.com/ru/post/1676858/

More articles:


All Articles