Calculate business days hours between two Timestamp(6) fields

Database
Senior Supporter

Re: Calculate business days hours between two Timestamp(6) fields

Hi, please don't get it personal but you need to spend a bit more time on the SQL basics before heading into a bit more advanced SQL calculations.

You need to propagate the folder_id up the chain.

Below query might be more what you are looking for. But it stll need the correct condition for your business day definition based on your calendar day.

select folder_id, 
sum (hhmmss)
from
(
select b.folder_id,
period(cast(c.calendar_date as timestamp(6)),cast(c.calendar_date+1 as timestamp(6)) - INTERVAL '0000 00:00:00.000001' DAY TO SECOND) as p,
p P_INTERSECT b.ref as i,
(end(i) - begin(i)) Hour(4) to Second as hhmmss
from dev_V.DS_VW1_CUST_CALENDAR c
Cross join
(select folder_id, period(Policy_Enter_time_dt ,Rec_created_ts) as ref
from system_T.DS_OPS_A2K_WORKFLOW_POLICY
WHERE folder_id in ( '2C02B62315H72272','2B09A85554J33864')
group by 1,2 -- is this needed???
) as b
where p overlaps b.ref
and c.BUSINESS_DAY_CONDITION!!! <- how to detect a business day in your calendar table?
) as tmp
group by 1
order by 1
;

Enthusiast

Re: Calculate business days hours between two Timestamp(6) fields

Hi ulrich,

Thank you for suggestion. After i remove the where condition and when i execute the query i got the below error after running 5 min

SELECT Failed. 7453: Interval field overflow (Please guide if i miss any)

I  checked there are no vlues greater than the begining value  here is my query

Select folder_id,

       sum (hhmmss)

 from

 (

select b.folder_id,

       period(cast(c.calendar_date as timestamp(6)),cast(c.calendar_date+1 as timestamp(6)) - INTERVAL '0000 00:00:00.000001' DAY TO SECOND) as p,

       p P_INTERSECT b.ref as i,

      (end(i) - begin(i)) Hour(4) to Second as hhmmss

from dev_V.DS_VW1_Aviva_CALENDAR c

      Cross join  

     (select folder_id, period(Policy_Enter_time_dt ,Rec_created_ts) as ref

      from system_T.DS_OPS_A2K_WORKFLOW_POLICY

              ) as b

where p overlaps b.ref

      and c.BUS_DY_INd = 'Y'

      ) as tmp

group by 1

order by 1

;

Thank you.

Senior Supporter

Re: Calculate business days hours between two Timestamp(6) fields

The meesage is indicating that at least for one row (end(i) - begin(i)) is bigger as 9999:xx:xx

Do you have any high/low values in your data?

Can you run

select min(Policy_Enter_time_dt), max(Policy_Enter_time_dt), min(Rec_created_ts),max(Rec_created_ts)

from system_T.DS_OPS_A2K_WORKFLOW_POLICY

Enthusiast

Re: Calculate business days hours between two Timestamp(6) fields

Hi ulrich,

Yes, Im getting one row below.

is there any other function do i add?

Minimum(Policy_Enter_Time_Dt)    Maximum(Policy_Enter_Time_Dt)    Minimum(Rec_Created_Ts)    Maximum(Rec_Created_Ts)

2/13/2004 13:08:53.021000    3/21/2012 00:00:13.000000    11/4/2011 15:43:21.440250    3/21/2012 03:12:07.000000

Thanks you very much!!!

Senior Supporter

Re: Calculate business days hours between two Timestamp(6) fields

What you can do is 

 Cast((sum (extract(hour from hhmmss)) +  sum (extract(minutes from hhmmss))  / 60. +   sum (extract(seconds from hhmmss)) / 3600.)  as descimal(18,3)) as business_hours

but this would represent the hours as decimal so 12.5 would mean 12:30:00

The other question is  if 2/13/2004 13:08:53.021000 and 2/13/2004 13:08:53.021000 are related to an initial load issue and if you realy want and need to represent this in your logic.