Technical Article

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating