Brett Kaiser (x002548) Blog

Not Just a Number - Brett Kaiser

Next Available Date

by Brett Kaiser (x002548) in  

Saw a post the other day on SQLTeam.

 

The OP Asked: "If I supply a Date, How can I get the next available date, that is not a holiday, and is not on the weekend."

 

I came up with this hack...please let me know if anyone has come with a different version

 

Thanks

 

Brett

 

CREATE TABLE Holidays (Holiday_Dt datetime, HoldayName varchar(255))
GO

INSERT INTO Holidays(Holiday_Dt, HoldayName)
SELECT '1/1/2010', 'New Years Day' UNION ALL
SELECT '1/18/2010', 'Martin Luther King' UNION ALL
SELECT '2/15/2010', 'President''s Day' UNION ALL
SELECT '5/31/2010', 'Memorial Day' UNION ALL
SELECT '7/5/2010', 'July 4th Holiday' UNION ALL
SELECT '9/6/2010', 'Labor Day' UNION ALL
SELECT '11/25/2010', 'Thanksgiving' UNION ALL
SELECT '11/26/2010', 'Black Friday' UNION ALL
SELECT '12/24/2010', 'Christmas Holiday' UNION ALL
SELECT '12/31/2010', 'New Years Eve'
GO

DECLARE @inp_Dt datetime; SET @inp_Dt = '11/25/2010'

    SELECT TOP 1 CASE WHEN Holiday_Dt IS NULL THEN inp_Dt ELSE DATEADD(d,n,inp_Dt) END AS Next_Available_Dt
      FROM (SELECT @inp_Dt AS inp_Dt) AS XXX
 LEFT JOIN Holidays h
  ON  h.Holiday_Dt = xxx.inp_Dt
 LEFT JOIN (SELECT 1 n UNION SELECT 2 n UNION SELECT 3 n UNION SELECT 4 n UNION
   SELECT 5 n UNION SELECT 6 n UNION SELECT 7 n) AS n
  ON  xxx.inp_Dt < DATEADD(d,n,inp_Dt)
     WHERE DATENAME(dw,DATEADD(d,n,inp_Dt)) NOT IN ('Saturday','Sunday')
    AND NOT EXISTS (SELECT * FROM Holidays WHERE Holiday_Dt = DATEADD(d,n,inp_Dt))
GO

DROP TABLE Holidays
GO

  

 

 

 

Legacy Comments
Milan
2010-06-30		 re: Next Available Date
Hi,

this is similar - just query plan seems to me better :)

SELECT TOP 1 z.*
FROM (
SELECT x = DATEADD(d, n.n, @inp_Dt)
FROM (SELECT 1 n UNION SELECT 2 n UNION SELECT 3 n UNION SELECT 4 n UNION
SELECT 5 n UNION SELECT 6 n UNION SELECT 7 n) AS n
WHERE DATENAME(dw, DATEADD(d, n.n, @inp_Dt)) NOT IN ('Saturday','Sunday')
) z
WHERE NOT EXISTS (SELECT * FROM Holidays WHERE Holiday_Dt = z.x)

Best regards,
Milan
Rob Volk
2010-06-30		 re: Next Available Date
Here's 2 variations on the last option using CTE's:

;with
a(n) as (select 1 union all select 0 union all select 0),
b(n) as (select 0 from a cross join a b),
c(Date) as (select DateAdd(day, ROW_NUMBER() over (order by n), @inp_dt) from b )
select top 1 * from c c
where DATENAME(dw,c.date) not in ('Saturday','Sunday')
and not exists(select * from Holidays where Holiday_Dt = c.Date)

;with
a(n) as (select 1 union all select n+1 from a where n<7),
d(Date) as (select DateAdd(day, n, @inp_dt) from a )
select top 1 * from d d
where DATENAME(dw,d.date) not in ('Saturday','Sunday')
and not exists(select * from Holidays where Holiday_Dt = d.Date)

Both have simpler plans but their execution times are the same or slightly worse than Milan's, at least on my server.

One nice suggestion is to use the CTE in a view definition for generic numbers, and possibly add generic dates as well, and then this query (and others like it) are simple SELECTs against that view.
Peso
2010-07-01		 re: Next Available Date
DATENAME is culture dependant so the suggesetions abov won't work in other language than english.

Anyhows, here is my suggestion

SELECT TOP(1) i.theDate AS Next_Available_Dt
FROM (
SELECT DATEADD(DAY, number, @inp_Dt)
FROM master.dbo.spt_values
WHERE Type = 'P'
AND DATEDIFF(DAY, 0, DATEADD(DAY, number, @inp_Dt)) % 7 < 5
) AS i(theDate)
LEFT JOIN dbo.Holidays AS h ON h.Holiday_Dt = i.theDate
WHERE h.Holiday_Dt IS NULL
ORDER BY i.theDate