asked on

DIfference between 2 dates excluding weekends and holidays sql query

Hi all.

I'm working on a query that will display the difference between 2 dates but it excludes weekends and holidays. I have a Calendar table with the CalendarDate, WorkingDay flag (if it's a working day then it's set to 1 otherwise it's a 0) and a Holiday flag (if it's a holiday then it's set to 1 otherwise it's a 0). The 2 date fields I want to calculate the difference is TableA.PromiseDate and TableB.InvoiceDate. The majority of the time the PromiseDate will be before or the same day as the InvoiceDate. But there are times when we invoice the order before it's promised so the InvoiceDate is before the PromiseDate (i.e. InvoiceDate = Jan 4, 2017, the PromiseDate = Jan 5, 2017 so the difference would be -1).

If the PromiseDate = InvoiceDate then the difference is 0.

Any idea how I can get this to work?

Thank you in advance.

Scott Pletcher

SELECT COUNT(*) * CASE WHEN TableA.PromiseDate <= TableB.InvoiceDate THEN 1 ELSE -1 END AS day_count
FROM dbo.calendar_table ct
WHERE (ct.CalendarDate BETWEEN TableA.PromiseDate and TableB.InvoiceDate OR
ct.CalendarDate BETWEEN TableA.PromiseDate and TableB.InvoiceDate) AND
ct.WorkingDay = 1

Jim Horn

Show us the schema of your SQL Server Calendar Table.

>WorkingDay flag (if it's a working day then it's set to 1 otherwise it's a 0) and a Holiday flag (if it's a holiday then it's set to 1 otherwise it's a 0).
Based on the above statement it should go something like this <total air code>...

SELECT SUM(CASE WHEN WorkingDay=1 AND Holiday=0 THEN 1 ELSE 0 END) as count_working_days
FROM YourCalendarTable c 
   JOIN TableA on whatever
   JOIN TableB on whatever
WHERE c.YTourDateColumn >= TableA.PromiseDate and c.YourDateColumn  <= TableB.InvoiceDate

Open in new window

printmedia

ASKER

Thank you for the reply. How would I join the Calendar table to the other 2 tables?

TableA: SalesOrder, ItemNumber, PromiseDate
TableB: SalesOrder, InvoiceNumber, ItemNumber, InvoiceDate
Calendar table: CalendarDate, WorkingDay, Holiday

Both your answers do not include instances when the InvoiceDate is before the PromiseDate and I'm thinking it has to do with the joining of the Calendar table to TableA and TableB.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

printmedia

ASKER

Ok Scott. I switched the second part of the OR in the CROSS APPLY section and I added to the CASE statement for when the PromiseDate = InvoiceDate then the difference between both dates is 0. But the query is adding a day so if the PromiseDate = Jan 17 and InvoiceDate = Jan 18 then it should be 1 day and not 2 as the query is currently doing. I guess I want it to work like the DateDiff function.

SELECT OrderFulfillment_Detail.SalesOrderNumber,OrderFulfillment_InvoiceHeader.InvoiceNumber, OrderFulfillment_Detail.ItemNumber,OrderFulfillment_Detail.PromiseDate, CONVERT(DATE,OrderFulfillment_InvoiceHeader.InvoiceDate) AS InvoiceDate, ca1.day_count
FROM         OrderFulfillment_InvoiceDetail INNER JOIN
                      OrderFulfillment_InvoiceHeader ON OrderFulfillment_InvoiceDetail.InvoiceNumber = OrderFulfillment_InvoiceHeader.InvoiceNumber INNER JOIN
                      OrderFulfillment_Detail ON OrderFulfillment_InvoiceHeader.SalesOrderNumber = OrderFulfillment_Detail.SalesOrderNumber AND 
                      OrderFulfillment_InvoiceDetail.ItemNumber = OrderFulfillment_Detail.ItemNumber
CROSS APPLY (
    SELECT COUNT(*) * CASE WHEN OrderFulfillment_Detail.PromiseDate = CONVERT(DATE,OrderFulfillment_InvoiceHeader.InvoiceDate) THEN 0 WHEN OrderFulfillment_Detail.PromiseDate < CONVERT(DATE,OrderFulfillment_InvoiceHeader.InvoiceDate) THEN 1 ELSE -1 END AS day_count
    FROM SDI.dbo.Calendar ct
    WHERE (ct.CalendarDate BETWEEN OrderFulfillment_Detail.PromiseDate and OrderFulfillment_InvoiceHeader.InvoiceDate OR
          ct.CalendarDate BETWEEN OrderFulfillment_InvoiceHeader.InvoiceDate and OrderFulfillment_Detail.PromiseDate) AND
          ct.WorkingDay = 1 AND ct.Holiday = 0
) AS ca1

Open in new window

Scott Pletcher

But the query is adding a day so if the PromiseDate = Jan 17 and InvoiceDate = Jan 18 then it should be 1 day and not 2 as the query is currently doing. I guess I want it to work like the DateDiff function.

Good point, I should have subtracted 1 from the final result:

...
SELECT (COUNT(*) - 1)
...