Hello,
I am fairly new to this, but I need to write a query that calculates whether an order made a shipping cutoff based on creation time, and when it actually shipped, based on local customer time. All times in the table below are central time.
Heres where it gets complicated for me. If the order was created after 8:00am local customer time the same day, and shipped prior to 5pm local customer time, it would be considered a pass, or a 1. If an order was created after 8am local customer time, BUT didnt ship until AFTER 5pm local customer time, it would be considered a failure or a 0. And to complicate things even more, if the order is created after 5pm local customer time, the order create time would be treated as it was created at 8am local customer time the following day local customer time, and would need to be shipped prior to 5pm local customer time that day. Would CASE be appropriate for this? Or am I thinking wrong? Any help would really be appreciated!!
right on, u can use a case to check for both the cases
obain the datepart of dd and check for same date then convert order date into hh:mm:ss am\pm see convert in sql help this will give you 4 columns which are
orderdatedatepart(dd) 5 according to 1st case in the above case
orderdatetime assume 8:20am in morning
shipdatedatepart(dd) 5
shipdatetime 4:45 PM assume
this will give me output 1 which is my flag
now check if (ord datepart = shipdatepart)
based on ord no USING A CURSOR
if true
then
check ord date > 8 am and ship date < 5 pm then flag = 1
else
if ship datetime > 5 pm then shipdatepart = shipdatepart + 1
else
flag = 0
That looks something like the way I need to go, I am very new to this all, and I cant seem to get "case" to work properly, and I am not familiar with using cursors. This is what I have so far using datepart in the query. Somehow, I need to also factor in the time zone. All times given are central. Thanks for the responses and help!
Query:
SELECT order,log_date,shipdate
datepart(dd,log_date) AS 'CREATEHOUR',
datepart(dd,ship_date) AS 'SHIPHOUR'
FROM [dbo].[tblshipDetail]
I have uploaded the results in a txt file, as I wasnt able to make it very readable pasting it here.
Maybe there's an easier way to do this, but I just did it the straightforward way:
Code:
SELECT ordernum, ordercreate, ordershipdate, timez,
--If created after 8am and shipped before 5pm on the same day
CASE WHEN DATEDIFF (d,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate),
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
) = 0
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
) >= 8
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
) < 17 THEN 1
--If created after 8am and shipped after 5pm on the same day
WHEN DATEDIFF (d,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate),
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
) = 0
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
) >= 8
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
) >= 17 THEN 0
--If created after 5pm and shipped before 5pm on the next day
WHEN DATEDIFF (d,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate),
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
) = 1
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
) >= 17
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
) < 17 THEN 1
ELSE 0 END
FROM [dbo].[tblshipDetail]
I would like to go 1 step further with this, and calculate weekends differently, than normal business days. The rule that I have to change is if it is 5pm or later on a friday, it is counted as being set up on the next monday. Saturdays and Sunday ordercreate dates are treated as being set up on the following Monday as well.
I have been able to convert the ordercreate dates to be shown as the following Monday, BUT, it is counting some as a miss-ship, even if it may have shipped on the saturday, or sunday the order was set up in ordershipdate.
Even after converting, it works fine as long as the ordershipdate is before 5pm, regardless of if its a weekend day or not.
I have tried to use a datepart to compare both dates, so the ordercreatedate would be greater than the ordershipdate, and be bucketed as a 1, or pass. But that is also changing the result set of the normal 5 day business work week data as well. Any help would really be appreciated! I am to the point where I am dreaming about this query.
I need more info. If for example an order is created on Friday at 6PM and then shipped on Saturday at 9AM, is that a pass or failure? How about created on Friday 6PM and shipped on Monday at 7AM? Created on Friday 6PM and shipped on Monday at 4PM? Created on Friday 6PM and shipped on Monday at 6PM? Any other scenario I missed?
""If for example an order is created on Friday at 6PM and then shipped on Saturday at 9AM''
That would be a pass, anything created at or after 5pm local customer time on Friday only needs to ship prior to 5pm local customer time Monday.
"How about created on Friday 6PM and shipped on Monday at 7AM?"
This would be a pass as well, if created at or after 5pm local customer time would only fail if it ships after 5pm Monday.
"Created on Friday 6PM and shipped on Monday at 6PM?"
This would be a fail, but just by a minute. If the create time was 5pm local customer time or later that night, it would have to ship by 4:59pm Monday local customer time to pass.
But you have the right idea! Just prior to 1700
So basically anything created at or after 5pm customer time on friday, needs to ship prior to 5pm Monday local customer time.
Anything created Saturday or Sunday, needs to ship by 5pm Monday local customer time. The rules need to stay the same
for normal business days as before.
OK, give this a spin and see how it works (I think I got the signs wrong in my earlier SQL statement when trying to adjust for the time zones):
Code:
SELECT ordernum, ordercreate, ordershipdate, timez,
--If created after Friday, 5pm and shipped before 5pm on the following Monday
CASE WHEN DATEPART (dw,
DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordercreate)
) = 6
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordercreate)
) >= 17
AND DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordershipdate) >
DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordercreate)
AND DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordershipdate) <
DATEADD(hh, 17, DATEADD(wk, DATEDIFF(wk, 0, DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordercreate)) +
CASE WHEN DATEPART(dw, DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordercreate)) = 1 THEN 0 ELSE 1 END, 0))
THEN 1
--If created after 8am and shipped before 5pm on the same day
WHEN DATEDIFF (d,
DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordercreate),
DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordershipdate)
) = 0
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordercreate)
) >= 8
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordershipdate)
) < 17
THEN 1
--If created after 8am and shipped after 5pm on the same day
WHEN DATEDIFF (d,
DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordercreate),
DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordershipdate)
) = 0
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordercreate)
) >= 8
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordershipdate)
) >= 17
THEN 0
--If created after 5pm and shipped before 5pm on the next day
WHEN DATEDIFF (d,
DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordercreate),
DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordershipdate)
) = 1
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordercreate)
) >= 17
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN -1 WHEN 'CST' THEN 0 WHEN 'MST' THEN 1 WHEN 'PST' THEN 2 END, ordershipdate)
) < 17
THEN 1
ELSE 0 END
FROM [dbo].[tblshipDetail]
The time zones were functioning correctly previously. This even corrected an issue I was seeing when the month would change over, it was counting things for example, created on 1/31, and failing them if shipped on 2/1. But thats working correctly now
Is there a way to get this to treat anything created on Saturday or Sunday at any time of day to just have a cutoff time of 5pm Monday? Pretty much the same thing as you did with Friday, but with no constraints on what time it was created on saturday or sunday. It just needs to ship by 5pm monday.
Also, it is failing anything that is created after 5pm, and ships prior to Midnight the same night? For example, this is counting as a fail against the real data, but should not.
I reordered the CASE conditions and put the time zone adjustments back to the way I had them before (still doesn't seem right, but I'll trust your assessment):
Code:
SELECT ordernum, ordercreate, ordershipdate, timez,
--If created after Friday, 5pm and shipped before 5pm on the following Monday
CASE WHEN
(
(
DATEPART (dw,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
) = 6
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
) >= 17
)
OR DATEPART (dw,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
) IN (1, 7)
)
AND DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate) >
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
AND DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate) <
DATEADD(hh, 17, DATEADD(wk, DATEDIFF(wk, 0, DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)) +
CASE WHEN DATEPART(dw, DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)) = 1 THEN 0 ELSE 1 END, 0))
THEN 1
--If created after 5pm and shipped before 5pm on the next day
WHEN ordercreate <= ordershipdate
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
) >= 17
AND ordershipdate < DATEADD(hh, 17, DATEADD(d, DATEDIFF(d, 0, ordercreate) +1, 0))
THEN 1
--If created after 8am and shipped before 5pm on the same day
WHEN DATEDIFF (d,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate),
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
) = 0
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
) >= 8
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
) < 17
THEN 1
--If created after 8am and shipped after 5pm on the same day
WHEN DATEDIFF (d,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate),
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
) = 0
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
) >= 8
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
) >= 17
THEN 0
ELSE 0 END
FROM [dbo].[tblshipDetail]
Oops, forgot to put in the time zone adjustments everywhere:
Code:
SELECT ordernum, ordercreate, ordershipdate, timez,
--If created after Friday, 5pm and shipped before 5pm on the following Monday
CASE WHEN
(
(
DATEPART (dw,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
) = 6
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
) >= 17
)
OR DATEPART (dw,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
) IN (1, 7)
)
AND DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate) >
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
AND DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate) <
DATEADD(hh, 17, DATEADD(wk, DATEDIFF(wk, 0, DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)) +
CASE WHEN DATEPART(dw, DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)) = 1 THEN 0 ELSE 1 END, 0))
THEN 1
--If created after 5pm and shipped before 5pm on the next day
WHEN DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate) <=
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
) >= 17
AND DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate) <
DATEADD(hh, 17, DATEADD(d, DATEDIFF(d, 0, DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)) +1, 0))
THEN 1
--If created after 8am and shipped before 5pm on the same day
WHEN DATEDIFF (d,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate),
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
) = 0
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
) >= 8
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
) < 17
THEN 1
--If created after 8am and shipped after 5pm on the same day
WHEN DATEDIFF (d,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate),
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
) = 0
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordercreate)
) >= 8
AND DATEPART (hh,
DATEADD(hh, CASE timez WHEN 'EST' THEN 1 WHEN 'CST' THEN 0 WHEN 'MST' THEN -1 WHEN 'PST' THEN -2 END, ordershipdate)
) >= 17
THEN 0
ELSE 0 END
FROM [dbo].[tblshipDetail]
It works! I was scrubbing the data all evening, and I have found nothing wrong at all. I will look a bit more, but this looks right!!
As far as the time zones, I am in central time, as is the time in the data. so thats the reason it needs to add an hour to EST, and drop 1 hour for MST, 2 for PST etc. thanks for your help!!