All Forums Database
Niesh20us 78 posts Joined 06/13
16 Oct 2014
Business Day calculation

Hi Experts,
I have got the situation where I have to get the 3 business day difference between two dates and if Yes then have to update the Flag accordingly. 

date1                   date2                flag          

10/16/2014      10/20/2014            ?            

10/13/2014      10/15/2014            ?                           

10/17/2014      10/20/2014            ?

10/6/2014        10/10/2014            ?


Niesh20us 78 posts Joined 06/13
16 Oct 2014

Please help

Raja_KT 1246 posts Joined 07/09
17 Oct 2014

Try this:
case when (date2-date1)>3 then 'Y' else 'N' end

Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

Niesh20us 78 posts Joined 06/13
17 Oct 2014

Thanks Raja :) but I was talking about Business Day calculation , this will give me total diffrence which will include weekends also.

dnoeth 4628 posts Joined 11/04
18 Oct 2014

The solution I prefer is a business_day number column in my calendar table, then you simply do two joins on start_date/end_date and calculate the difference.
As you have rules what's a business day (not including weekends, public holidays, ...) you probably already got a column with a flag indicating working days.
The new column is populated with a SUM(CASE WHEN workday_flag = 1 THEN 1 ELSE 0 END) OVER (ORDER BY calendar_date), so saturday and sunday will have the same number as friday and on monday it's increased.


You must sign in to leave a comment.