Eliminate Weekends And Holidays From Days Elapsed
This script is designed to eliminate weekends and designated non business days
(such as holidays and other days a company is closed) from a computation to determine if a particular
order was shipped on time, early, or late.
--Begin setting up tables
CREATE TABLE NonBusinessDays (
[Iden] [int] IDENTITY (1, 1) NOT NULL ,
[NonBusinessDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE Shipping (
[OrderNumber] [int] IDENTITY (1, 1) NOT NULL ,
[Scheduled] [datetime] NULL ,
[shipped] [datetime] NULL
) ON [PRIMARY]
GO
INSERT INTO NonBusinessDays (NonBusinessDate)
SELECT '12/25/2002' UNION
SELECT '7/4/2002' UNION
SELECT '1/1/2002' UNION
SELECT '1/21/2002'
INSERT INTO Shipping (Scheduled, Shipped)
SELECT '4/1/2002','4/1/2002' UNION
SELECT '12/31/2001','1/22/2002' UNION
SELECT '7/5/2002','7/3/2002' UNION
SELECT '6/28/2002','7/5/2002' UNION
SELECT '4/29/2002','4/25/2002' UNION
SELECT '4/25/2002','4/29/2002' UNION
SELECT '4/1/2002','5/1/2002'
--End setting up tables
--Begin getting shipping days
SELECT OrderNumber,
SUM(CASE WHEN n.NonBusinessDate IS NULL THEN 0 ELSE 1 END) AS NonBusinessDays INTO #NonBusinessDays
FROM Shipping s
LEFT JOIN NonBusinessDays n ON (n.NonBusinessDate > s.shipped AND n.NonBusinessDate < s.Scheduled) OR (n.NonBusinessDate < s.shipped AND n.NonBusinessDate > s.Scheduled)
GROUP BY shipped,scheduled, OrderNumber
SELECT Shipped, Scheduled,
CASE WHEN DATEDIFF(dd,shipped,Scheduled) - (DATEDIFF
(wk,shipped,Scheduled) * 2) - NonBusinessDays > 1 THEN 'shipped ' + LTRIM(STR
(DATEDIFF(dd,shipped,Scheduled) - (DATEDIFF
(wk,shipped,Scheduled) * 2)) - NonBusinessDays) + ' days early.'
WHEN DATEDIFF(dd,shipped,Scheduled) - (DATEDIFF
(wk,shipped,Scheduled) * 2) - NonBusinessDays > 0 THEN 'shipped ' + LTRIM(STR
(DATEDIFF(dd,shipped,Scheduled) - (DATEDIFF
(wk,shipped,Scheduled) * 2)) - NonBusinessDays) + ' day early.'
WHEN DATEDIFF(dd,scheduled,shipped) - (DATEDIFF
(wk,scheduled,shipped) * 2) - NonBusinessDays > 1 THEN 'scheduled ' + LTRIM
(STR(DATEDIFF(dd,scheduled,shipped) - (DATEDIFF
(wk,scheduled,shipped) * 2)) - NonBusinessDays) + ' days late.'
WHEN DATEDIFF(dd,scheduled,shipped) - (DATEDIFF
(wk,scheduled,shipped) * 2) - NonBusinessDays > 0 THEN 'shipped ' + LTRIM
(STR(DATEDIFF(dd,scheduled,shipped) - (DATEDIFF
(wk,scheduled,shipped) * 2)) - NonBusinessDays) + ' day late.'
WHEN DATEDIFF(dd,scheduled,shipped) - (DATEDIFF
(wk,scheduled,shipped) * 2) = 0 THEN 'On time'
END AS [Diff between scheduled and shipped]
FROM Shipping s
LEFT JOIN #NonBusinessDays n ON n.OrderNumber = s.OrderNumber
--End getting shipping days
DROP TABLE #NonBusinessDays
DROP TABLE NonBusinessDays
DROP TABLE Shipping
