x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Wednesday, June 30, 2010

Next Available Date

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

  

 

 

 

posted @ Wednesday, June 30, 2010 2:43 PM | Feedback (3) | Filed Under [ SQL Server ]

Powered by:
Powered By Subtext Powered By ASP.NET