Hey Guys,
Recently we got a requirement to find out the difference between two dates excluding the weekends i.e. Saturday and Sunday. I'm giving the report level formula that can be used to achieve this. Here the Presentation table I'm using is "Time" and the respective date columns are "Start Date" and "End Date". You need to replace these column names according to your Presentation Layer.
-TIMESTAMPDIFF(SQL_TSI_DAY,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFWEEK("Time"."End Date")-1),"Time"."End Date"),
TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFWEEK(TIMESTAMPADD(SQL_TSI_DAY,6,"Time"."Start Date"))-1),TIMESTAMPADD(SQL_TSI_DAY,6,"Time"."Start Date")))/7*5+
MOD(7-DAYOFWEEK("Time"."Start Date"),6)+CASE WHEN DAYOFWEEK("Time"."End Date")-2>5 THEN 5 ELSE DAYOFWEEK("Time"."End Date")-2 END
Hope this helps!!!
Cheers,
Siva
Recently we got a requirement to find out the difference between two dates excluding the weekends i.e. Saturday and Sunday. I'm giving the report level formula that can be used to achieve this. Here the Presentation table I'm using is "Time" and the respective date columns are "Start Date" and "End Date". You need to replace these column names according to your Presentation Layer.
-TIMESTAMPDIFF(SQL_TSI_DAY,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFWEEK("Time"."End Date")-1),"Time"."End Date"),
TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFWEEK(TIMESTAMPADD(SQL_TSI_DAY,6,"Time"."Start Date"))-1),TIMESTAMPADD(SQL_TSI_DAY,6,"Time"."Start Date")))/7*5+
MOD(7-DAYOFWEEK("Time"."Start Date"),6)+CASE WHEN DAYOFWEEK("Time"."End Date")-2>5 THEN 5 ELSE DAYOFWEEK("Time"."End Date")-2 END
Hope this helps!!!
Cheers,
Siva
