Sorry for not contributing in this forum that much, I appretiate all the work that you all do for the community and most of the time, even being at a Middleground on SQL, I can do most of my reports only reading answers from other topics. Also, english is not my first language, so sorry for any mispelling.
The fact is that something came up and suddenly I'm the sole person in the department that knows (barely) how to write a script, and seemingly no one here can help with more difficult questions. To top it off, they've been giving me increasingly difficult requests, and for this one I know I can't even start.
So the question is "How to get the amount of working hours (preferably in seconds) from two timestamps?" We have a Calendar table, and I already made a script to get the working days off it:
CREATE MULTISET VOLATILE TABLE DIAS_HABILES, NO LOG AS ( SELECT SITE.SIT_SITE_ID, TIM.TIM_DAY, CASE WHEN TIM.TIM_WEEK_DAY in (2,3,4,5,6) AND HOL.SHP_HOLIDAY_DATE_ID is null THEN 1 ELSE 0 END WORKING_DAY, SUM(WORKING_DAY) OVER (PARTITION BY SITE.SIT_SITE_ID ORDER BY TIM.TIM_DAY ROWS UNBOUNDED PRECEDING) AS ORDEN FROM MyDaysTable TIM CROSS JOIN MySitesTable SITE LEFT JOIN MyHolidaysTable HOL ON SITE.SIT_SITE_ID = HOL.SIT_SITE_ID AND HOL.SHP_HOLIDAY_DATE_ID = TIM.TIM_DAY WHERE TIM.TIM_DAY between '2018-01-01' and date )WITH DATA PRIMARY INDEX (SIT_SITE_ID, TIM_DAY) ON COMMIT PRESERVE ROWS;
But it is not enough. I know we have this topic: http://community.teradata.com/t5/Database/Calculate-business-days-hours-between-two-Timestamp-6-fiel... , but I could not even get started on it!
I'm really sorry to ask this for you guys, but I really need help on this one. Our working hours are 09-18 of the days mentioned above. Could any of you help me ellaborate this script?
Again, I really appretiate this community and love the product (especially combined with Alation).
If your working hours are fixed, you can start with a solution like this :
(ts_end - ts_start) day(2) to second(4,0) - (ts_end (date) - ts_start (date)) * interval '15' hour
The first part is the basic interval between two timestamp, the second one is to remove 15 hours (9 - 0 + 24 - 18) per day from the first interval.
Once you have the interval, you can go with standard multiplication
I've refined the formulas, it seems ok :
with cte_data (id, ts_start, ts_end) as ( select 1, timestamp '2018-09-05 09:00:00', timestamp '2018-09-05 18:00:00' from cte_dual union all select 2, timestamp '2018-09-04 09:00:00', timestamp '2018-09-05 18:00:00' from cte_dual union all select 3, timestamp '2018-09-04 16:00:00', timestamp '2018-09-05 10:00:00' from cte_dual union all select 4, timestamp '2018-09-03 18:00:00', timestamp '2018-09-05 09:00:00' from cte_dual ) , cte_dual (dummy) as (select 'X') select id, ts_start, ts_end , (ts_end - ts_start) day(2) to second(0) as base_interval , (ts_end (date)) - (ts_start (date)) as nb_days , nb_days * interval '15' hour as nb_days_adjusted , base_interval - nb_days_adjusted as working_interval , extract(day from working_interval) * 60 * 60 * 24 + extract(hour from working_interval) * 60 * 60 + extract(minute from working_interval) * 60 + extract(second from working_interval) as working_seconds from cte_data; id ts_start ts_end base_interval nb_days nb_days_adjusted working_interval working_seconds -- -------------------- ------------------- ------------- ------- ---------------- ---------------- --------------- 1 2018-09-05 09:00:00 2018-09-05 18:00:00 0 09:00:00 0 0 0 09:00:00 32400 2 2018-09-04 09:00:00 2018-09-05 18:00:00 1 09:00:00 1 15 0 18:00:00 64800 3 2018-09-04 16:00:00 2018-09-05 10:00:00 0 18:00:00 1 15 0 03:00:00 10800 4 2018-09-03 18:00:00 2018-09-05 09:00:00 1 15:00:00 2 30 0 09:00:00 32400
First off, thank you very much for taking the time to respond me, and your solution seems very reliable, I understood the logic behind the SQL and I think I could apply that to my tables with very little handwork.
The sad thing is that, as I mentioned in the first post, we have to account for holidays and weekends to not be considered in the working interval. How would I be able to do this? Is there a way to extract the dates from the interval, compare with my holidays/weekends volatile table and discount 9 hours per day that is (or is not) in the table?
If my line of thinking is confuse, please ask away so I can clarify the situation.
we have to account for holidays and weekends to not be considered in the working interval. How would I be able to do this? Is there a way to extract the dates from the interval, compare with my holidays/weekends volatile table and discount 9 hours per day that is (or is not) in the table?
Exactly like you said. Just do a join (or a scalar query, depending on the volumes) to count the number of holidays / weekend between both timestamp, multiply 9 hours to this number and substract it from the working interval :
select sum(1 - working_day) * interval '9' hour as not_worked_hours from dias_habiles where tim_day between ts_start (date) and ts_end (date)