CODEWITHSUNDEEP
sqlsql-servert-sqlbetweendate-range
Muhammad Rehan Saeed
Muhammad Rehan Saeed

Reputation: 38437

Count Number of Intersections Between Two Date Ranges in T-SQL

I have the following two tables (some sample data shown):

Holiday

Start      | End
-----------|-----------
2000-01-01 | 2000-01-02
2000-02-20 | 2000-02-20

Event

Title      | Date
-----------|-----------
Foo        | 2000-01-03
Bar        | 2000-01-20

How can I return all events with the number of holiday days that have occurred during a week before Event.Date?

SELECT 
    e.Title,
    e.Date,
    DaysHolidayInPastWeek  <-- How to get this?
FROM Event e

Example Output

Title      | Date       | DaysHolidayInPastWeek
-----------|------------|----------------------
Foo        | 2000-01-03 | 2
Bar        | 2000-01-20 | 0

Upvotes: 1

Views: 761

Answers (3)

Ciro Corvino
Ciro Corvino

Reputation: 2128

this query also manages cases in which event date falls into a holiday period.

 SELECT Title, Date,
 (
  SELECT        
  sum(cntdays) from
  (select start, [end], 
    case 
      when E.Date between start and [End]   
            then DATEDIFF(DAY, Start, E.Date)

      when ( 
             DATEADD(DAY, -7,E.Date) between start and [End]    
             or 
             DATEADD(DAY, -7,E.Date) < start
           ) and E.Date > start

           then DATEDIFF(DAY, start, [end]) + 1

      else 0
    end as cntdays 
   from Holiday
  ) as H
 ) AS DaysHolidayInPastWeek
 FROM Event AS E

Upvotes: 0

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

Sample data

DECLARE @Holiday TABLE (HolidayStart date, HolidayEnd date);
INSERT INTO @Holiday (HolidayStart, HolidayEnd) VALUES
('2000-01-01', '2000-01-02'),
('2000-03-31', '2000-03-31'),
('2000-03-20', '2000-03-27'),
('2000-05-01', '2000-05-30');

DECLARE @Event TABLE (Title nvarchar(50), dt date);
INSERT INTO @Event (Title, dt) VALUES
('Foo', '2000-01-03'),
('Bar', '2000-01-20'),
('444', '2000-04-01'),
('555', '2000-05-10');

Query

Assumes that HolidayStart and HolidayEnd dates are both inclusive. CROSS APPLY E is just to create handy alias for the result of the DATEADD function, so that I could write short EventStart later instead of the long DATEADD expression.

OUTER APPLY gives a list of all rows in the Holiday that intersect with the week of the given Event. Intersection duration is from max of starts till min of ends.

Main SELECT groups and sums all intersections together.

SELECT
    Ev.Title
    ,Ev.dt
    ,ISNULL(SUM(DATEDIFF(day, 
        Intersections.IntersectionStart,
        Intersections.IntersectionEnd) + 1), 0) AS DaysHolidayInPastWeek
FROM
    @Event AS Ev
    CROSS APPLY
    (
        SELECT 
            DATEADD(day, -6, Ev.dt) AS EventStart
            ,Ev.dt AS EventEnd
    ) AS E
    OUTER APPLY
    (
        SELECT
            -- intersection duration is:
            -- max of starts
            -- min of ends
            CASE WHEN E.EventStart > H.HolidayStart 
                THEN E.EventStart ELSE H.HolidayStart END AS IntersectionStart
            ,CASE WHEN E.EventEnd < H.HolidayEnd
                THEN E.EventEnd ELSE H.HolidayEnd END AS IntersectionEnd
        FROM @Holiday AS H
        WHERE
            -- two intervals intersect
            H.HolidayEnd >= E.EventStart
            AND H.HolidayStart <= E.EventEnd
    ) AS Intersections
GROUP BY
    Ev.Title
    ,Ev.dt
;

Result

Title       dt  DaysHolidayInPastWeek
Foo 2000-01-03  2
Bar 2000-01-20  0
444 2000-04-01  3
555 2000-05-10  7

Upvotes: 3

neer
neer

Reputation: 4082

Try following query.

SELECT 
    e.Title,
    e.Date,
    (   
        SELECT
            SUM(DATEDIFF(DAY, h.start, h.end)) AS CountOfHoliday
        FROM
            Holiday h
        WHERE
            h.EventId = e.Id AND -- releation id
            h.Start >= DATEADD(DAY, -7, e.date) AND
            h.Start <= e.date -- Or delete this. Just h.Start >= DATEADD(DAY, -7, e.date)

    ) AS DaysHolidayInPastWeek 
FROM 
    Event e

Upvotes: 0

Related Questions