All Forums Database
Ashwin4tera 19 posts Joined 11/11
06 Mar 2012
Calculate business days hours between two Timestamp(6) fields

Hi All,

I have a business requirement for my client ono of the table have two Timestamp(6) fields

'Startdatets' and 'Enddatets' from this want to generate elapsed time report in HH:MM formate

which should not contain non business days hours like Saturdays, sundays and Holidays.

ex:

StartdateTs                                         EnddateTs                                    

'2012-03-02 09:00:00.000000'       '2012-03-05 09:00:00.000000'

(Friday)                                             (Monday) 

Answer:

If we do  (EnddateTs - StartdateTs) = 24:00 (Not 72:00) (Not include satuurday and Sunday hrs)

Please suggest the best way do to it please post if you have any ready query.

 

Thank you Verymuch.

ulrich 816 posts Joined 09/09
07 Mar 2012

This can become a very complicated thing.

You need a client specific calendar which contains the business day information.

Are you only dealing with a local - one city - data source? Otherwise you need to consider the location as well.

In Germany we have different states and some holidays are state specific. Even some cites have specific holidays.

Also some work days are considered as half holidays.

And are you certain that business days hours should be calculated? So should it be (EnddateTs - StartdateTs) = 24:00 or 8:00?

So again - prerequisite  is a calendar table flagging the holidays.

On which release are you?

Depending on the release there are different options to go ahead.

 

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Ashwin4tera 19 posts Joined 11/11
07 Mar 2012

Hi Ulrich,

Thankyou for your reply.

 

We are using Teradata 13.0 version.

Yes we have client specific calendor but it doesn't have Timestamp(6) field

It has date and Business day flag 'Y' or 'N'

we have some generic national holidays i dont want those hours.

It should be (EnddateTs - StartdateTs) = 24:00 Not 8:00, I want all business day hours (not business hours 8)

 

Please provide the above releated code if you have anay i will modify according.

 

Thanks,
 

ulrich 816 posts Joined 09/09
07 Mar 2012

this should give you some hints

Select sum(hhmmss)
from
(
select period(cast(c.calendar_date as timestamp(6)),cast(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 sys_calendar.calendar c 
     cross join
     (select period(cast(calendar_date - 10 as timestamp(6)) 
                      + INTERVAL '0000 01:30:00.000000' DAY TO SECOND
                    ,cast(calendar_date+5 as timestamp(6))
                      - INTERVAL '0000 11:33:00.000000' DAY TO SECOND
                    ) as ref 
      from sys_calendar.calendar 
      where date = calendar_Date
    ) as b
where p overlaps b.ref
      and c.day_of_week between 2 and 6
) as tmp
order by 1

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Ashwin4tera 19 posts Joined 11/11
07 Mar 2012

Hi ulrich,

Working on it will reply if any issue.

Appriciate your quick response.

 

Ashwin4tera 19 posts Joined 11/11
09 Mar 2012

Hi Ulrich,

In the bove query where to put my base table since both tables are sys_calendor and do i need to join this to my Client table to rid off the client holidays.

Can you Please send me the modified query.

 

Thank You Ulrich.

ulrich 816 posts Joined 09/09
10 Mar 2012

Funny request - "please send me the modified query". Please donate some money to Unicef!

 

Did you check the SQL at all?

The first sys_calendar.reference would represent your customers calendar table

from sys_calendar.calendar c <---- customers calendar table 

the derived table should be replaced with base table

where you need to transfrom your StartdateTs and EnddateTs into a period data type.

you need also add grouping columns as I expect more than one row in the base column.

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Ashwin4tera 19 posts Joined 11/11
12 Mar 2012

Hi Sir,

Never mind i'm under learning stage please do not wonder if i ask any simple things.

Sure Sir, I Will love to donate some bugs to Unicef.

Working on it will post my final query soon.

 

Thank you verymuch.

paddysmith 2 posts Joined 03/12
13 Mar 2012

Hi,

StartTime= A2
EndTime= J2
StartWorking Hours= $Y$1
EndWorking Hours= $Z$1
Y2 = "1" (Sunday)
Y3 = "2" (Monday)
Y4 = "3"
Y5 = "4"
Y6 = "5"
Y7 = "6"
Y8 = "7" (Saturday)

How do you calculate working hours (and minutes) between start time and end time. The times may span two or more days.

I've been using this formula for quite a while now and it has worked fine, but excluded weekends.

Code:

=IF(((J2-A2)<0),"OPEN",MIN(MOD(J2,1),$Z$1)-MAX(MOD(A2,1),$Y$1)+(SUM(IF(WEEKDAY(INT(A2)-1+ROW(INDIRECT("1:"&(INT(J2)-INT(A2))+1)))=TRANSPOSE($Y$3:$Y$7),1,0))-1)*($Z$1-$Y$1))

Now they want to INCLUDE weekends. No other change, just count all 7 days instead of just mon-fri.

Thanks!!

Ashwin4tera 19 posts Joined 11/11
15 Mar 2012

Hi ulrich,

Here is my coustom query i need time difference for each row not the entire rows i put group by in base table join and 'folder_id' is my base table key i just checked for two values but it is giving sum for entire values please see the query and out put below. Thank you.   

select sum (hhmmss)
 from
 (
select period(cast(c.calendar_date as timestamp(6)),cast(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 period(Policy_Enter_time_dt
                      + INTERVAL '0000 00:00:00.000000' DAY TO SECOND  ,Rec_created_ts  - INTERVAL '0000 00:00:00.000000' DAY TO SECOND) as ref
      from system_T.DS_OPS_A2K_WORKFLOW_POLICY
 WHERE folder_id in ( '2C02B62315H72272','2B09A85554J33864')
 group by 1
        ) as b
where p overlaps b.ref
      and c.day_of_week between 2 and 6
) as tmp;
 

Output:

Sum(hhmmss)
   28:50:29.999998
 

Thx

ulrich 816 posts Joined 09/09
16 Mar 2012

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
;

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Ashwin4tera 19 posts Joined 11/11
19 Mar 2012

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.

ulrich 816 posts Joined 09/09
19 Mar 2012

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

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Ashwin4tera 19 posts Joined 11/11
21 Mar 2012

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!!!

ulrich 816 posts Joined 09/09
22 Mar 2012

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.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.