Next Available Date

<< DB2 OS/390 Database COPY and RESTORE | Home | XML, SQL & DB2 V10 >>

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

Print | posted on Wednesday, June 30, 2010 2:43 PM | Filed Under [ SQL Server ]

Feedback

# 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

6/30/2010 3:06 PM | Milan

# 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.

6/30/2010 4:36 PM | Rob Volk

# 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

7/1/2010 3:35 AM | Peso

Comments have been closed on this topic.

x002548's Blog

My Links

Article Categories

Archives

Post Categories

Image Galleries

Misc

SQL Server