Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jason2112
Starting Member
17 Posts |
 Posted - 2009-06-24 : 19:00:50
|
| I have a view with shipment data and I need to measure if the packages will arrive to the customer on time. The fields I'm using are ShipDate, MustArriveDate, and TransitTime. ShipDate and MustArriveDate contain datetime data and the TransitTime is an nvarchar field that contains the #days we expect it to take a shipment to arrive. I need to factor in weekends, so that if the shipment occurs over the weekend, two days are added to the TransitTime (which I have working). I need to come up with the most efficient way to see if it was shipped on time.Currently I am calculating the diff between the MustArriveDate and the ShipDate as:DATEDIFF(dd, ShipDate, MustArriveDate) - DATEDIFF(ww, ShipDate, MustArriveDate) * 2 AS ShippingDaysand it works like it should. Now I need to compare it to the value in the TransitTime field, but how do I add in the weekend days to the TransitTime field? Would I have to compare the ShippingDays calculation above with a basic date subtraction (i.e. MustArriveDate - ShipDate) and then take the difference and add it to the TransitTime and then compare?The other option is to take the MustArriveDate - the TransitTime and compare it to the ShipDate, but I'm not sure how to subtract a variable (TransitTime) from a datetime field, factoring weekends, and then end up with a datetime field to compare with the original ShipDate?Any help is appreciated. |
|
|
eonmantra
Starting Member
11 Posts |
Posted - 2009-06-24 : 19:51:53
|
| Not a solution, but a question.Assuming you need the weekend component because most shippers do not deliver on weekends, would it be beneficial to be able to factor in holidays as well?If so you might want to setup a separate table with the dates for when the delivery service is not working and use the two together in a solution. |
 |
|
|
Jason2112
Starting Member
17 Posts |
Posted - 2009-06-24 : 20:26:04
|
| Good question. Right now, no. I can work with the few exceptions until I have time to build a "business days" table. |
|
|
|
Jason2112
Starting Member
17 Posts |
Posted - 2009-06-24 : 20:39:00
|
| I just figured it out... just compared the ShippingDays calculation with the original TransitTime field and it works lol. |
|
|
|
|
|
|
|
|
Slightly different date comparison question