Next business day query

Transact-SQL
1

Basically I need help in writing a query for next business day delivery and holidays also needs to be considered. I also have a calendar table that have all holiday until 2030. A classic example that I have,
let say today is 7/1/2011 which is a Friday. Obviously next business day is a Monday but it falls on 7/4/2011 which is a holiday.
So its next business day should be 7/5/2011.
So when the report for today pulls up, all deliveries scheduled for 7/2 Sat, 7/3 Sun and 7/4 Mon should all be on a new delivery date of 7/5/2011.

I need help in creating a CASE statement to figure out that if it's a Friday, how will it automatically check the Saturday and Sunday.
Or if next day is a holiday, how will it also add the day of the holiday to be delivered on the next business day?

These are a few scenarios that came to mind that I also need help:

Scenario 1
Is @DeliveryDate = Fri
If yes, + 3 days --Mon
Also get deliveries for Sat and Sun to be Mon deliveries

Scenario 2
Is --Is @DeliveryDate = Fri
--If yes, + 3 days --Mon
--Also get deliveries for Sat and Sun to be Mon deliveries
falls on a Holiday, + 1 day --Tue
Also get deliveries for the Holiday to be delivered the next day

Scenario 3
Is @DeliveryDate between Mon - Thur
If yes, + 1 day

Scenario 4
Is --Is @DeliveryDate between Mon - Thur
--If yes, + 1 day
falls on a Holiday, + 1 day
If that falls on a Fri, follow Fri step above
Also get deliveries for the Holiday to be delivered the next day

I apologize if it's hard to understand. Just let me know so I can explain further.

Again any input will be valuable to me to learn more about this technology.

Thank you in advance,

Louie

2

Is this what you want?

DECLARE @DeliveryDate DATE = '2017-07-07';

SELECT TOP 1
	NextWorkingDay = c.dt
FROM 
	dbo.Calendar c
WHERE 
	c.dt >= DATEADD(dd, 1, @DeliveryDate) 
	AND c.isWeekday = 1
	AND c.isHoliday = 0
ORDER BY 
	c.dt;
3

Thanks for the response Jason. That is a part that I can add to my query. Not sure where, in a CASE or a temp table.

Basically the query will be a sql job that will run once daily on weekdays.

So for a Monday report, you'll see its next business day as Tuesday.
Tuesday -> Wednesday
Wednesday -> Thursday
Thursday -> Friday
Friday -> Monday. For this report, you'll include next business day deliveries for Saturday and Sunday deliveries.

If your supposedly next business day is a Holiday add 1 day.

Monday -> Tuesday Holiday -> Wednesday. In this report, like the Friday above, you'll also include next day deliveries for Tuesay Holiday
Tuesday -> Wednesday Holiday -> Thursday
Wednesday -> Thursday Holiday -> Friday

Thursday -> Friday Holiday -> Monday. Need to include next business deliveries for Friday, Saturday and Sunday
Friday -> Monday Holiday -> Tuesday. Need to include next business deliveries for Saturday Sunday and Monday

I need help on how will sql know that they need to do what was mentioned above.

Thank you in advance

4

Just create a "NextWorkingDay" inline table valued function.
The code I provided will accept a date as an input parameter and the code will return the next business day (assuming that your calendar table is correct).

5

My calendar table only have the holidaydate. Do you have the code to create a similar calendar table related to the one you have above?

6

Then it's not a Calendar table... It's a Holiday table. Big difference...

Here are a couple of options... The execution plan on the 2nd is way better on #2 but the logical reads are much lower on #1. Test them both and see which works better in your environment...

IF OBJECT_ID('tempdb..#Holiday', 'U') IS NOT NULL 
DROP TABLE #Holiday;

CREATE TABLE #Holiday (
	HolidayDate DATE NOT NULL PRIMARY KEY
	);
INSERT #Holiday (HolidayDate) 
SELECT 
	c.dt
FROM 
	dbo.Calendar c
WHERE 
	c.isHoliday = 1
	AND c.isWeekday = 1;

--====================================
--====================================

-- option 1...
DECLARE @SomeDate DATE = '2017-07-07';

SELECT 
	NextBusinessDay = CASE 
						WHEN DATEPART(dw, DATEADD(dd, 1, @SomeDate)) BETWEEN 2 AND 6 AND NOT EXISTS (SELECT 1 FROM #Holiday h WHERE h.HolidayDate = DATEADD(dd, 1, @SomeDate))
						THEN DATEADD(dd, 1, @SomeDate)
						WHEN DATEPART(dw, DATEADD(dd, 2, @SomeDate)) BETWEEN 2 AND 6 AND NOT EXISTS (SELECT 1 FROM #Holiday h WHERE h.HolidayDate = DATEADD(dd, 2, @SomeDate))
						THEN DATEADD(dd, 2, @SomeDate)
						WHEN DATEPART(dw, DATEADD(dd, 3, @SomeDate)) BETWEEN 2 AND 6 AND NOT EXISTS (SELECT 1 FROM #Holiday h WHERE h.HolidayDate = DATEADD(dd, 3, @SomeDate))
						THEN DATEADD(dd, 3, @SomeDate)
						WHEN DATEPART(dw, DATEADD(dd, 4, @SomeDate)) BETWEEN 2 AND 6 AND NOT EXISTS (SELECT 1 FROM #Holiday h WHERE h.HolidayDate = DATEADD(dd, 4, @SomeDate))
						THEN DATEADD(dd, 4, @SomeDate)
						WHEN DATEPART(dw, DATEADD(dd, 5, @SomeDate)) BETWEEN 2 AND 6 AND NOT EXISTS (SELECT 1 FROM #Holiday h WHERE h.HolidayDate = DATEADD(dd, 5, @SomeDate))
						THEN DATEADD(dd, 5, @SomeDate)
						WHEN DATEPART(dw, DATEADD(dd, 6, @SomeDate)) BETWEEN 2 AND 6 AND NOT EXISTS (SELECT 1 FROM #Holiday h WHERE h.HolidayDate = DATEADD(dd, 6, @SomeDate))
						THEN DATEADD(dd, 6, @SomeDate)
						WHEN DATEPART(dw, DATEADD(dd, 7, @SomeDate)) BETWEEN 2 AND 6 AND NOT EXISTS (SELECT 1 FROM #Holiday h WHERE h.HolidayDate = DATEADD(dd, 7, @SomeDate))
						THEN DATEADD(dd, 7, @SomeDate)
					END;

--====================================

-- option 2...
SELECT TOP 1
	NextBusinessDay = d.FollowDate
FROM 
	( VALUES 
			(DATEADD(dd, 1, @SomeDate), DATEPART(dw, DATEADD(dd, 1, @SomeDate))),
			(DATEADD(dd, 2, @SomeDate), DATEPART(dw, DATEADD(dd, 2, @SomeDate))),
			(DATEADD(dd, 3, @SomeDate), DATEPART(dw, DATEADD(dd, 3, @SomeDate))),
			(DATEADD(dd, 4, @SomeDate), DATEPART(dw, DATEADD(dd, 4, @SomeDate))),
			(DATEADD(dd, 5, @SomeDate), DATEPART(dw, DATEADD(dd, 5, @SomeDate))),
			(DATEADD(dd, 6, @SomeDate), DATEPART(dw, DATEADD(dd, 6, @SomeDate))),
			(DATEADD(dd, 7, @SomeDate), DATEPART(dw, DATEADD(dd, 7, @SomeDate)))
		) d (FollowDate, DOW)
	LEFT JOIN #Holiday h
		ON d.FollowDate = h.HolidayDate
WHERE 
	h.HolidayDate IS NULL
	AND d.DOW BETWEEN 2 AND 6
ORDER BY 
	d.FollowDate;
7

Thank you so much for the code. I will try it out and let you know after. Thanks again.

8

No problem. Glad to help. :slight_smile:

9

Need help here again.

Doing Option 2 above for some reason doesn't show the next business day.

Example 7/24, NBD should be 7/25 but it give 7/24. Basically it won't give me the next business day when I provide a date that falls within M to Th.

Can somebody help me tweak the code?

Thanks

10

Delete or comment out the following line...

(@SomeDate, DATEPART(dw, @SomeDate)),

11

Thanks again Jason. Getting close but this time the Thursdays were off by giving Monday as its next business day.

But I think I got it, changed this from
AND d.DOW BETWEEN 2 AND 5
to
AND d.DOW BETWEEN 2 AND 6

so far so good

12

Change

AND d.DOW BETWEEN 2 AND 5

to this...

AND d.DOW BETWEEN 2 AND 6

13

Using the query above brings me closer to what I really want to accomplish. Thanks to all the help Jason.

One more thing, I'm on a dead end again. I'm working on getting the same total deliveries for Friday, Saturday and Sunday which all fall on Monday.
Also for a 'next day is a holiday' delivery.

As what the query above does is to show next business day.

Let say you check the report on a Friday and you see that there are 722 total deliveries on Monday. If you break it down, it is 246 for Saturday, 97 for Sunday and 379 on Monday.

But if you access the report on a Saturday, total deliveries for Monday goes down and it is from the sum of 97 and 379. Same thing goes on a Sunday, 379.

Basically, I want to see 722 regardless if you access the report on Friday, Saturday or Sunday. Because all of them are suppose to be delivered on a Monday.

Not sure how to handle this. Looking forward to your assistance.

Thank you in advance.

14

The use code that will alter the "now date" back to the "latest working date", if the current date isn't a working date.

15

An alternative to the solution from @Jason_A_Long could look like this:

/*****
 * Simulate the date of runtime
 */
declare @thedate date=cast('2017-01-01' as date);

/*****
 * Simulate holiday table
 */
create table #holiday(dt date);
insert into #holiday
   select *
     from (values(cast('2016-01-01' as date))
                ,(cast('2016-01-02' as date))
                ,(cast('2016-01-18' as date))
                ,(cast('2016-02-15' as date))
                ,(cast('2016-05-30' as date))
                ,(cast('2016-07-04' as date))
                ,(cast('2016-09-05' as date))
                ,(cast('2016-10-10' as date))
                ,(cast('2016-11-11' as date))
                ,(cast('2016-11-24' as date))
                ,(cast('2016-12-25' as date))

                ,(cast('2017-01-01' as date))
                ,(cast('2017-01-02' as date))
                ,(cast('2017-01-16' as date))
                ,(cast('2017-02-20' as date))
                ,(cast('2017-05-29' as date))
                ,(cast('2017-07-04' as date))
                ,(cast('2017-09-04' as date))
                ,(cast('2017-10-09' as date))
                ,(cast('2017-11-10' as date))
                ,(cast('2017-11-11' as date))
                ,(cast('2017-11-23' as date))
                ,(cast('2017-12-25' as date))

                ,(cast('2018-01-01' as date))
                ,(cast('2018-01-02' as date))
                ,(cast('2018-01-15' as date))
                ,(cast('2018-02-19' as date))
                ,(cast('2018-05-28' as date))
                ,(cast('2018-07-04' as date))
                ,(cast('2018-09-03' as date))
                ,(cast('2018-10-08' as date))
                ,(cast('2018-11-11' as date))
                ,(cast('2018-11-12' as date))
                ,(cast('2018-11-22' as date))
                ,(cast('2018-12-25' as date))
          ) as holiday(dt)
;

/*****
 * Find valid work dates one week back and forward from @thedate
 */
with cte1(dt,rn)
  as (select d.dt
            ,row_number() over(order by (select null)) as rn
        from (select dateadd(day,row_number() over(order by (select null))-7,@thedate) as dt
                from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as v(n)
             ) as d
       where not exists(select 1
                          from #holiday as h
                         where h.dt=d.dt
                       )
         and datepart(weekday,d.dt) not in (7,1)
     )
/*****
 * Find matching period
 */
    ,cte2(dt_start,dt_end)
  as (select a.dt
            ,b.dt
        from cte1 as a
             inner join cte1 as b
                     on b.rn=a.rn+1
       where a.dt<=@thedate
         and b.dt>@thedate
     )
/*****
 * Finally join and show data
 */
select b.*     /* this ofcause should be replaced with the fields you want to see */
  from cte2 as a
       inner join yourtable as b
               on b.dt>=a.dt_start
              and b.dt<a.dt_end
;

/*****
 * Clean up
 */
drop table #holiday;