Business Day calculation

Database
Enthusiast

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            ?

4 REPLIES
Enthusiast

Re: Business Day calculation

Please help

Enthusiast

Re: Business Day calculation

Try this:

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

Enthusiast

Re: Business Day calculation

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

Junior Contributor

Re: Business Day calculation

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.