Removing holiday intervals from time periods

Question

The following query calculates working hours that have passed between a request was received and responded. Working hours are 9 to 5, Monday to Friday.

select tmp.Request_Id, 
sum (tmp.hhmmss) as WorkHoursElapsed
from

    (select Request_Id,
    period(cast(c.calendar_date as timestamp(6)) + INTERVAL '0000 09:00:00.00000' DAY TO SECOND,cast(calendar_date as timestamp(6)) + INTERVAL '0000 17:00:00.000000' DAY TO SECOND) as p, 
    p P_INTERSECT b.ref as i, 
    (end(i) - begin(i)) Hour(4) to MINUTE as hhmmss 
    from calendar c 
    Cross join

        (select Request_Id, period(LocalTime_Received_RTA ,LocalTime_Responded_RTA) as ref 
        from Responded_Requests 
         WHERE LocalTime_Received_RTA<LocalTime_Responded_RTA 
        group by 1,2
        ) as b 

    where p overlaps b.ref
    and c.day_of_week between 2 and 6  

    ) as tmp

group by 1

I have another table for holidays, with the start and end intervals

Start: 1/1/2014 12:00:00 AM End: 1/1/2014 11:59:00 PM

How do I join the holiday table to exclude the holiday intervals?

Answer 1

You will need to convert it Teradata SQL systax, In T-SQL you would just add another join and exclude values like this

SELECT m.*
    FROM mytable AS m
    JOIN othertable AS o
        ON m.date NOT BETWEEN o.StartDate AND o.EndDate

or

SELECT m.*
    FROM mytable AS m
    JOIN othertable AS o
        ON m.date < o.StartDate
           AND m.date > o.EndDate