Calculating time

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!!


Ordernum OrderCreate OrderShipDate TimeZ
67890 5/5/2005 11:57 5/6/2005 15:33 EST
35789 5/5/2005 13:57 5/5/2005 14:19 MST
44465 5/5/2005 13:58 5/5/2005 21:58 CST
87654 5/5/2005 18:00 5/7/2005 21:58 PST

You can try case with datepart function.

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]

---

That works great!!! Awesome Job! Thanks Nosepicker!!

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.


Thanks for your help!

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.

Timez ordercreate ordershipdate
CST 2/1/2005 17:28 2/1/2005 17:28 0

Thanks alot for your help Nosepicker, you are awesome with this stuff. I am truly grateful.

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!!