SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Slightly different date comparison question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Jason2112
Starting Member

17 Posts

Posted - 06/24/2009 :  19:00:50  Show Profile  Reply with Quote
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 ShippingDays

and 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

USA
11 Posts

Posted - 06/24/2009 :  19:51:53  Show Profile  Reply with Quote
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.
Go to Top of Page

Jason2112
Starting Member

17 Posts

Posted - 06/24/2009 :  20:26:04  Show Profile  Reply with Quote
Good question. Right now, no. I can work with the few exceptions until I have time to build a "business days" table.
Go to Top of Page

Jason2112
Starting Member

17 Posts

Posted - 06/24/2009 :  20:39:00  Show Profile  Reply with Quote
I just figured it out... just compared the ShippingDays calculation with the original TransitTime field and it works lol.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000