CODEWITHSUNDEEP
sqlsql-serversql-server-2008
oshirowanen
oshirowanen

Reputation: 15965

Counting between date ranges

I have lots of employee holiday records which show the holiday start and end days. I need to work out how many holidays each employee has taken between 2 dates, i.e. 2015-01-01 00:00:00 to 2015-02-01 00:00:00.

How do I deal with holidays where the holiday starts before that range or ends after that range?

Upvotes: 0

Views: 843

Answers (4)

Dan
Dan

Reputation: 10700

Say your employee holiday table looks like this:

CREATE TABLE EmployeeHoliday (
    EmployeeId INT,
    HolidayStart DATETIME,
    HolidayEnd DATETIME
)

The following query returns the number of days per employee, between the two specified dates (variables):

DECLARE @CountFromDate AS DATETIME = '2015-01-01'
DECLARE @CountToDate AS DATETIME = '2015-02-01'

SELECT EmployeeId,
    SUM(DATEDIFF(DAY,
        CASE WHEN @CountFromDate < HolidayStart THEN HolidayStart ELSE @CountFromDate END,
        CASE WHEN @CountToDate > HolidayEnd THEN HolidayEnd ELSE @CountToDate END
        )
    ) AS HolidaysHeld
FROM EmployeeHoliday
GROUP BY EmployeeId

If you don't want this query to output employees that only had holidays outside the specified interval of time, include the following WHERE-clause just before the GROUP BY part of the query:

WHERE HolidayStart <= @CountToDate AND HolidayEnd >= @CountFromDate

Upvotes: 1

Jayesh
Jayesh

Reputation: 46

you try SELECT date1-date2 from tablename;

Upvotes: 0

Elohim Meth
Elohim Meth

Reputation: 1827

declare @datebeg datetime = '20150101', @dateend datetime = '20150201'

select e.employeeid, sum(datediff(day, case when @datebeg < h.datebeg then h.datebeg else @datebeg end,
                                       case when @dateend > h.dateend then h.dateend else @dateend end))
from holiday h 
where @datebeg <= h.dateend and @dateend >= h.datebeg
group by h.employeeid

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You use date logic. Other databases have least() and greatest() functions to help with this. In SQL Server, you can use somewhat more complicated case statements. The result is something like this:

select h.employee_id,
       sum(datediff(day, 
                    (case when h.start < @StartDate then @StartDate else h.start end),
                    (case when h.end > @EndDate then @EndDate else h.end end)
                   )
          ) as DaysOnHoliday
from holidays h
where h.start <= @EndDate and h.end >= @StartDate
group by h.employee_id

Upvotes: 1

Related Questions