SQL Date Diff Business Days

Question

I need to count all orders that have a datediff <= 1. I use the COALESCE function, because sometimes the Date1 can be null.

SELECT COUNT(*)
FROM Orders
WHERE DATEDIFF(dd,COALESCE(Date1, Date2),Date0) <= 1

Is there a way I can count only the business days (Mon-Fri)?

Thanks.

Define business days. Do you mean Mon - Fri or do you need to exclude public holidays such as Christmas? If you need to exclude Christmas etc. then you need to define this in a calendar table. — Nick.Mc, Apr 14 '16 at 01:53
I guess you are using SQL Server (please tag accordingly). If so you can use the `DATENAME` function to work out whether a date is on a weekend or not, then filter on it. — Nick.Mc, Apr 14 '16 at 02:02
As Nick said, you can add a where clause that filters out only work days - Monday to Friday. The simplest way is to do this "and convert(int, COALESCE([OrderDate], [OrderDate2])) % 7 between 0 and 4" — cameront, Apr 14 '16 at 03:04
Here is an http://stackoverflow.com/questions/252519/count-work-days-between-two-dates on how to calculate for business days — cableload, Apr 14 '16 at 03:05

score 1 · Answer 1 · edited Nov 13 '18 at 23:14

You can use a Calendar table and store all business days in it.

Or, you can use the following simple formula to calculate number of business days between two dates. The formula assumes that Saturday and Sunday are not business days. It assumes that two given dates are business days.

All it does, is calculates the normal difference in days and then subtracts 2 (non-business) days from this result for each beginning of the week.

Example 1

DECLARE @Date0 date = '2016-04-07'; -- Thursday
DECLARE @Date1 date = '2016-04-08'; -- Friday

SELECT
    DATEDIFF(day, @Date0, @Date1) - 
    DATEDIFF(week, @Date0, @Date1) * 2 AS BusinessDays;

Result

BusinessDays
1

Example 2

DECLARE @Date0 date = '2016-04-08'; -- Friday
DECLARE @Date1 date = '2016-04-11'; -- Monday

SELECT
    DATEDIFF(day, @Date0, @Date1) - 
    DATEDIFF(week, @Date0, @Date1) * 2 AS BusinessDays;

Result

BusinessDays
1

Example 3

DECLARE @Date0 date = '2016-04-08'; -- Friday
DECLARE @Date1 date = '2016-04-18'; -- Monday

SELECT
    DATEDIFF(day, @Date0, @Date1) - 
    DATEDIFF(week, @Date0, @Date1) * 2 AS BusinessDays;

Result

BusinessDays
6

Your query would look like this:

SELECT COUNT(*)
FROM Orders
WHERE
    DATEDIFF(day,  COALESCE(Date1, Date2), Date0) -
    DATEDIFF(week, COALESCE(Date1, Date2), Date0) * 2 <= 1
;

score 1 · Accepted Answer · answered Apr 14 '16 at 10:45

You could change your count to a conditional sum, using DATEPART to ignore the Saturdays and Sundays (which should have a dw of 7 & 1 respectively).

SELECT SUM(CASE WHEN DATEPART(dw, COALESCE(Date1, Date2)) BETWEEN 2 AND 6 
           THEN 1 ELSE 0 END)
FROM Orders
WHERE DATEDIFF(dd,COALESCE(Date1, Date2),Date0) <= 1

SQL Date Diff Business Days

2 Answers2