August 1, 2008 at 7:42 am
Hi All,
I need to code a query to return a delivery date based on two factors: the days that a delivery company delivers on and the lead time needed for the delivery.
I've included the DDL below to create the scenario. If I ordered an item today with delivery company 3, what is the earliest date they could delivery it?
Thanks!
CREATE TABLE DeliveryCompany
(
CompanyIDINT
,CompanyNameVARCHAR(30)
,DaystoDeliverINT
)
CREATE TABLE DeliveryDays
(
DDIDINT
,CompanyINT
,MondayBIT
,TuesdayBIT
,WednesdayBIT
,ThursdayBIT
,FridayBIT
,SaturdayBIT
,SundayBIT
)
INSERT INTO DeliveryCompany VALUES(1,'A1 Delivery',4)
INSERT INTO DeliveryCompany VALUES(2,'Home Delivery services',3)
INSERT INTO DeliveryCompany VALUES(3,'Dump it delivery',6)
INSERT INTO DeliveryDays VALUES(1,1,1,1,1,1,1,0,0)
INSERT INTO DeliveryDays VALUES(2,2,1,1,1,1,1,1,0)
INSERT INTO DeliveryDays VALUES(3,3,1,1,0,1,1,0,0)
August 1, 2008 at 8:16 am
SET NOCOUNT ON
DROP TABLE #DeliveryCompany
DROP TABLE #DeliveryDays
CREATE TABLE #DeliveryCompany
(
CompanyID INT
,CompanyName VARCHAR(30)
,DaystoDeliver INT
)
CREATE TABLE #DeliveryDays
(
DDID INT
,Company INT
,Monday BIT
,Tuesday BIT
,Wednesday BIT
,Thursday BIT
,Friday BIT
,Saturday BIT
,Sunday BIT
)
INSERT INTO #DeliveryCompany VALUES(1,'A1 Delivery',4)
INSERT INTO #DeliveryCompany VALUES(2,'Home Delivery services',3)
INSERT INTO #DeliveryCompany VALUES(3,'Dump it delivery',6)
INSERT INTO #DeliveryDays VALUES(1,1,1,1,1,1,1,0,0)
INSERT INTO #DeliveryDays VALUES(2,2,1,1,1,1,1,1,0)
INSERT INTO #DeliveryDays VALUES(3,3,1,1,0,1,0,1,0)
SET DATEFIRST 1 -- monday, default is sunday
SELECT c.CompanyID, c.CompanyName, CAST(CHARINDEX('1', CAST(Monday AS CHAR(1))+
CAST(Tuesday AS CHAR(1))+
CAST(Wednesday AS CHAR(1))+
CAST(Thursday AS CHAR(1))+
CAST(Friday AS CHAR(1))+
CAST(Saturday AS CHAR(1))+
CAST(Sunday AS CHAR(1)), DATEPART(dw, GETDATE()) ) AS INT) - DATEPART(dw, GETDATE()) + c.DaystoDeliver AS [DaysToDelivery]
FROM #DeliveryCompany c
INNER JOIN #DeliveryDays d ON d.Company = c.CompanyID
Doing your homework will cost you a pint 😛
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2008 at 8:25 am
I'll gladly buy you a pint sir, if you can tell me what date the item will be delivered! I'm not catching your drift on the solution ... Fridays eh :w00t:
August 1, 2008 at 8:31 am
Add [DaysToDelivery] to the current date:
SELECT c.CompanyID, c.CompanyName,
DATEADD(DD,
CAST(CHARINDEX('1', CAST(Monday AS CHAR(1))+
CAST(Tuesday AS CHAR(1))+
CAST(Wednesday AS CHAR(1))+
CAST(Thursday AS CHAR(1))+
CAST(Friday AS CHAR(1))+
CAST(Saturday AS CHAR(1))+
CAST(Sunday AS CHAR(1)), DATEPART(dw, GETDATE()) ) AS INT) - DATEPART(dw, GETDATE()) + c.DaystoDeliver
, GETDATE()) AS [DeliveryDate]
FROM #DeliveryCompany c
INNER JOIN #DeliveryDays d ON d.Company = c.CompanyID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2008 at 8:39 am
Here is a solution that includes a Numbers[/url] table. The caveat is that my solution needs the numbers table to include 0. You can obviously adapt the solution for a Numbers table that starts with 1. Also, normally you would have a real numbers/tally/sequence table in a "tools" database. This solution assumes the order ships on order date:
[font="Courier New"]CREATE TABLE #DeliveryCompany
(
CompanyID INT
,CompanyName VARCHAR(30)
,DaystoDeliver INT
)
CREATE TABLE #DeliveryDays
(
DDID INT
,Company INT
,Monday BIT
,Tuesday BIT
,Wednesday BIT
,Thursday BIT
,Friday BIT
,Saturday BIT
,Sunday BIT
)
SELECT TOP 8
IDENTITY(INT,0,1) AS N
INTO #numbers
FROM MASTER.dbo.SysColumns sc1,
MASTER.dbo.SysColumns sc2
INSERT INTO #DeliveryCompany VALUES(1,'A1 Delivery',4)
INSERT INTO #DeliveryCompany VALUES(2,'Home Delivery services',3)
INSERT INTO #DeliveryCompany VALUES(3,'Dump it delivery',6)
INSERT INTO #DeliveryDays VALUES(1,1,1,1,1,1,1,0,0)
INSERT INTO #DeliveryDays VALUES(2,2,1,1,1,1,1,1,0)
INSERT INTO #DeliveryDays VALUES(3,3,1,1,0,1,1,0,0)
DECLARE @order_date smalldatetime
DECLARE @Company INT
SET @Company = 3
SET @order_date = GETDATE()-1
SELECT
C.CompanyName,
C.DaysToDeliver,
@order_date AS orderdate,
-- how many days did I need to add to get it delivered
MIN(N.N) AS daysadded,
-- what is the "first" date that it can be delivered
MIN(DATEADD(DAY, C.DaysToDeliver,@order_date + N)) AS deliverydate
FROM
#DeliveryCompany C JOIN
#DeliveryDays D ON
C.CompanyID = D.Company JOIN
#numbers N ON
CASE -- this finds the days that are valid delivery days for the comany
WHEN DATEPART(dw, DATEADD(DAY, C.DaysToDeliver,@order_date + N)) = 7 THEN D.Saturday
WHEN DATEPART(dw, DATEADD(DAY, C.DaysToDeliver,@order_date + N)) = 6 THEN D.Friday
WHEN DATEPART(dw, DATEADD(DAY, C.DaysToDeliver,@order_date + N)) = 5 THEN D.Thursday
WHEN DATEPART(dw, DATEADD(DAY, C.DaysToDeliver,@order_date + N)) = 4 THEN D.Wednesday
WHEN DATEPART(dw, DATEADD(DAY, C.DaysToDeliver,@order_date + N)) = 3 THEN D.Tuesday
WHEN DATEPART(dw, DATEADD(DAY, C.DaysToDeliver,@order_date + N)) = 2 THEN D.Monday
ELSE D.Sunday
END = 1
WHERE
company = @Company
GROUP BY
C.CompanyName,
C.DaysToDeliver
DROP TABLE #DeliveryCompany
DROP TABLE #DeliveryDays
DROP TABLE #numbers[/font]
I would actually redesign the database to accomplish this. My DeliveryDates table would be (CompanyId Int, DeliverOnDayOfWeek Int) where DeliverOnDayOfWeek is the numeric representation of the day of the week they deliver on (Returned by DatePart(dw, getdate()). Note that this varies based on the Set DateFirst setting where Sunday is 1. Which you can see I use in my code above. Here is how I would solve the problem with this design:
[font="Courier New"]CREATE TABLE #DeliveryCompany
(
CompanyID INT
,CompanyName VARCHAR(30)
,DaystoDeliver INT
)
CREATE TABLE #DeliveryDays
(
CompanyID INT
,DeliverOnDayOfWeek INT
)
SELECT TOP 8
IDENTITY(INT,0,1) AS N
INTO #numbers
FROM MASTER.dbo.SysColumns sc1,
MASTER.dbo.SysColumns sc2
INSERT INTO #DeliveryCompany VALUES(1,'A1 Delivery',4)
INSERT INTO #DeliveryCompany VALUES(2,'Home Delivery services',3)
INSERT INTO #DeliveryCompany VALUES(3,'Dump it delivery',6)
INSERT INTO #DeliveryDays SELECT COmpanyId, N FROM #DeliveryCompany CROSS JOIN #numbers WHERE N NOT IN (0,1,7) AND companyid = 1
INSERT INTO #DeliveryDays SELECT COmpanyId, N FROM #DeliveryCompany CROSS JOIN #numbers WHERE N NOT IN (0,1) AND companyid = 2
INSERT INTO #DeliveryDays SELECT COmpanyId, N FROM #DeliveryCompany CROSS JOIN #numbers WHERE N NOT IN (0,1, 4) AND companyid = 3
DECLARE @order_date smalldatetime
DECLARE @Company INT
SET @Company = 3
SET @order_date = GETDATE()-1
SELECT
C.CompanyName,
C.DaysToDeliver,
@order_date AS orderdate,
-- how many days did I need to add to get it delivered
MIN(N.N) AS daysadded,
-- what is the "first" date that it can be delivered
MIN(DATEADD(DAY, C.DaysToDeliver,@order_date + N)) AS deliverydate
FROM
#DeliveryCompany C JOIN
#DeliveryDays D ON
C.CompanyID = D.CompanyID JOIN
#numbers N ON
DATEPART(dw, DATEADD(DAY, C.DaysToDeliver,@order_date + N)) =DeliverOnDayOfWeek
WHERE
C.companyid = @Company
GROUP BY
C.CompanyName,
C.DaysToDeliver
DROP TABLE #DeliveryCompany
DROP TABLE #DeliveryDays
DROP TABLE #numbers[/font]
This second design and solution is easier to code and read.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 1, 2008 at 8:41 am
Ah, I'm liking the soltion, but comany 3 should take the longest...
It makes more sense if I explain the [DaystoDeliver]. It is the number of days that a delivery company needs to process and deliver a product. But they can only process on days where there is a 1 flag in the [DeliveryDays] table.
So based on the data I'd expect that if I ordered a product for delivery with company 3 today, they'd deliver the item on the 11th Aug. This is beacuse company C only has four processing days, so the days that count are:
Day1- Friday-1st
Day2- Monday-4th
Day3- Tuesday-5th
Day4- Thursday-7th
Day5- Friday-8th
Day6- Monday-11th
Thanks
August 1, 2008 at 8:44 am
Hi Adam
I deliberately changed the data for company 3 to make the test valid.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2008 at 8:53 am
Okay, I misunderstood your problem as well, I think. The solution I offered assumed that the DeliveryDates were dates the company would deliver on, so if I order on today they will deliver 6 days from today, unless they do not deliver on that day then keep adding a day until I hit a day they deliver on. But what you are saying is that if I order on a day that is not included, Say Wednesday for company that I have to add 1 day because nothing will be processed that day and then I have to add 2 more days because they don't work on Saturday and Sunday also. So really the fastest I can get a delivery from Company3 is 9 days because I am always going to hit the weekend and that will cause me to hit Wednesday every time as well.
The solution I have provided can be tweaked to work in that situation and the alternate design I proposed I think will handle this even better. I'd have to noodle a little more to give you the exact code. I may work on that later today.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 1, 2008 at 9:02 am
Adam, I've changed my code to subtract the current day number from the first available delivery day number, but if you run it now it will still be incorrect because I've included weekends in the daystodeliver. Company 2 delivers on a saturday though...
Jack, I can see where you're coming from with this, how often do we see denormalised data? This particular exercise would be a helluva lot easier.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2008 at 9:31 am
That's spot on Jack.
Thanks for the responses guys. I'm still trying to solve this myself too.
First one to a solution get's to go home early?
August 1, 2008 at 11:33 am
I keep thinking I have a solution, but As I test it to make sure it will work in each Company/delivery day situation I find cases where the solution does not work.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 1, 2008 at 11:40 am
Adam Warne (8/1/2008)
That's spot on Jack.Thanks for the responses guys. I'm still trying to solve this myself too.
First one to a solution get's to go home early?
Already happened mate, it's friday 😀
Question: is there any difference between days on which a company can deliver, and days on which they work i.e. sort? Say, vans could be loaded on friday for delivery on saturday?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2008 at 11:51 am
For this I'd make the assumption that a 0 for a day means that nothing happens.
P.S, I finished ealy too 😛
August 2, 2008 at 10:10 am
Hi Adam
"Running totals" is one way to tag this problem, and here's a proof-of-concept solution. It's a bit raw so you can check values for yourself to see what's happening. I'd replace weekno & dayno with a single identity column and put the code into a UDF for use, called like this:
EarliestDelivery = uftGetEarliestDelivery(monday, ...sunday, DaystoDeliver)
Let me know if you want this done for you. Here's the code:
DECLARE @monday int, @tuesday int, @wednesday int, @thursday int, @friday int, @saturday int, @sunday int,
@LeadTime int
SET @monday = 1
SET @tuesday = 1
SET @wednesday = 0
SET @thursday = 1
SET @friday = 1
SET @saturday = 0
SET @sunday = 0
SET @LeadTime = 4
SET NOCOUNT ON
SET DATEFIRST 1 -- monday, default is sunday
DECLARE @dayno int, @Today DATETIME
SET @Today = dateadd(d, 0, datediff(d, 0, getdate()))
SET @dayno = DATEPART(dw, @Today)
SELECT a.weekno, b.dayno, b.Busy, CAST(NULL AS INT) AS Working, DATEADD(DD, ((weekno-1)*7)+dayno-@dayno, @Today) AS thedate
INTO #Updateable
FROM (SELECT 1 AS weekno UNION SELECT 2 UNION SELECT 3) a,
(SELECT 1 AS dayno, @monday AS Busy UNION
SELECT 2, @tuesday UNION
SELECT 3, @wednesday UNION
SELECT 4, @thursday UNION
SELECT 5, @friday UNION
SELECT 6, @saturday UNION
SELECT 7, @sunday) b
DECLARE @Working int
SET @Working = 0
UPDATE #Updateable SET @Working = Working = CASE WHEN thedate > = @Today AND Busy = 1 THEN @Working + 1 ELSE @Working END
SELECT MIN(thedate) FROM #Updateable WHERE Working = @LeadTime
Here's the table contents after the running totals update:
weekno dayno Busy Working thedate
----------- ----------- ----------- ----------- -----------------------
1 1 1 0 2008-07-28 00:00:00.000
1 2 1 0 2008-07-29 00:00:00.000
1 3 0 0 2008-07-30 00:00:00.000
1 4 1 0 2008-07-31 00:00:00.000
1 5 1 0 2008-08-01 00:00:00.000
1 6 0 0 2008-08-02 00:00:00.000
1 7 0 0 2008-08-03 00:00:00.000
2 1 1 1 2008-08-04 00:00:00.000
2 2 1 2 2008-08-05 00:00:00.000
2 3 0 2 2008-08-06 00:00:00.000
2 4 1 3 2008-08-07 00:00:00.000
2 5 1 4 2008-08-08 00:00:00.000
2 6 0 4 2008-08-09 00:00:00.000
2 7 0 4 2008-08-10 00:00:00.000
3 1 1 5 2008-08-11 00:00:00.000
3 2 1 6 2008-08-12 00:00:00.000
3 3 0 6 2008-08-13 00:00:00.000
3 4 1 7 2008-08-14 00:00:00.000
3 5 1 8 2008-08-15 00:00:00.000
3 6 0 8 2008-08-16 00:00:00.000
3 7 0 8 2008-08-17 00:00:00.000
This is a 2k solution, expect someone to come up with a fancy schmancy single-statement 2k5 solution real soon now:)
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply