23maverick23
2/26/2018 - 6:29 PM

NS: Date Difference Buisness Days

NS: Date Difference Buisness Days

# Calculate Business Days (does not include start date)
CASE WHEN (TO_CHAR({systemnotes.date},'J') - TO_CHAR({created},'J'))+1 - ((((TRUNC({systemnotes.date},'D'))-(TRUNC({created},'D')))/7)*2) - (CASE WHEN TO_CHAR({created},'DY','nls_date_language=english')='SUN' THEN 1 ELSE 0 END) - (CASE WHEN TO_CHAR({systemnotes.date},'DY','nls_date_language=english')='SAT' THEN 1 ELSE 0 END) = 1 THEN 'Same Day' WHEN (TO_CHAR({systemnotes.date},'J') - TO_CHAR({created},'J'))+1 - ((((TRUNC({systemnotes.date},'D'))-(TRUNC({created},'D')))/7)*2) - (CASE WHEN TO_CHAR({created},'DY','nls_date_language=english')='SUN' THEN 1 ELSE 0 END) - (CASE WHEN TO_CHAR({systemnotes.date},'DY','nls_date_language=english')='SAT' THEN 1 ELSE 0 END) = 2 THEN 'Next day' ELSE CONCAT((TO_CHAR({systemnotes.date},'J') - TO_CHAR({created},'J'))+1 - ((((TRUNC({systemnotes.date},'D'))-(TRUNC({created},'D')))/7)*2) - (CASE WHEN TO_CHAR({created},'DY','nls_date_language=english')='SUN' THEN 1 ELSE 0 END) - (CASE WHEN TO_CHAR({systemnotes.date},'DY','nls_date_language=english')='SAT' THEN 1 ELSE 0 END)-1, ' days') END

# Calculate Business Days (including start date; Wed to Thu = 2)
(TO_CHAR({systemnotes.date},'J') - TO_CHAR({created},'J'))+1 - ((((TRUNC({systemnotes.date},'D'))-(TRUNC({created},'D')))/7)*2) - (CASE WHEN TO_CHAR({created},'DY','nls_date_language=english')='SUN' THEN 1 ELSE 0 END) - (CASE WHEN TO_CHAR({systemnotes.date},'DY','nls_date_language=english')='SAT' THEN 1 ELSE 0 END)