Calculate business days hours between two Timestamp(6) fields

Database
Enthusiast

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.

14 REPLIES
Senior Supporter

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


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.

Enthusiast

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

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,

Senior Supporter

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

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

Enthusiast

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

Hi ulrich,

Working on it will reply if any issue.

Appriciate your quick response.

Enthusiast

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

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.

Senior Supporter

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

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.

Enthusiast

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

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.

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

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

Enthusiast

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

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