Date Calculation

[Zurich98]

Hello All,

I'm trying to accomplish the following: I have shipdate and DaysTakeToDeliver. I'm trying to figure out the ExpectedDeliveryDate without holiday and weekend. Below is the sample data.

Shipdate DaysTake ExpectedDeliveryDate
8/29/08 3 9/4/08 (exclude holiday/weekend)
8/29/08 4 9/5/08 (exclude holiday/weekend)
8/29/08 5 9/8/08 (exclude holiday/weekend)
9/2/08 3 9/5/08 (exclude holiday/weekend)
9/2/08 4 9/8/08 (exclude holiday/weekend)
9/2/08 5 9/9/08 (exclude holiday/weekend)

i have a table that stored all the holidays. How can I create the ExpectedDeliveryDate.
Your input/suggestion is greatly appreciated.
Thanks

 

[gmmastros]

i have a table that stored all the holidays.

Can you show some sample data from that table?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Zurich98]

I've three tables. Please see below.

tbl_ItemShipped
TrackingID
Invoice_Nmbr
Item_Nmbr
Item_Desc
Shipping_Cost
DeliveryDate
Customer_Name
Customer_Addr1
Customer_Addr2
Customer_City
Customer_State
Customer_ZipCode


tbl_ServiceInfo
State
ZipCode
Zone
DayTake
IsActive
DateCreated
DateAdded
DateModified

tlbHoliday
HolidayId
Holiday_Date
Holiday_Name


select t1.*,t2.isnull(DayTake,3) as DayTake
from tbl_ItemShipped t1
left join tbl_ServiceInfo t2
on t1.ZipCode = t2.ZipCode

I'm on SQL 2005. Thanks for your respond.

 

[gmmastros]

Will the expected delivery date be different if the holiday falls on a weekend?

Also, Can you show the data in the tblHoliday table?

[tt][blue]
Select *
From tblHoliday
Where Holiday_Date >= '20080801'
And Holiday_Date < '20081001'
[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Zurich98]

Hi George,

Below is the data in the holiday table. the Expected Delivery Date can NEVER be on holiday or weekend. For example, if we shipped an order on 8/29/08 and UPS/FED EX estimates that it would take 4 days to deliver, the ExpectedDeliveryDate = 9/5/08. (8/30 and 8/31 is weekend and 9/1 is Labor Day). Thank you for taking the time to help.

HolidayID Holiday_Date Holiday_Name
6 2008-01-01 00:00:00 New Years
7 2008-05-26 00:00:00 Memorial Day
8 2008-07-04 00:00:00 US Independence Day
9 2008-09-01 00:00:00 Labor Day
10 2008-11-27 00:00:00 Thanksgiving Day
11 2008-12-25 00:00:00 Christmas Day

 

[gmmastros]

This was a lot more difficult than I thought it would be. I suppose you could argue that this is why I continue to answer questions on this forum (for the chance to really stretch the mind).

Of course, there are going to be many ways to accomplish this. Here's what I suggest.

First, it will make things a lot easier if you have a calendar table in your database. This calendar table should have one row for each day that you could ever possibly care about. There should also be a column to indicate days that are excluded from your calculations. Something like this...

First, to build the calendar table...

Create Table Calendar(CalendarDate DateTime Primary Key Clustered, ExcludeFromShipping Bit)

Declare @Start DateTime
Set @Start = '19900101'
While @Start < '21000101'
  Begin
    Insert Into Calendar(CalendarDate) Values(@Start)
    Set @Start = @Start + 1
  End

Update Calendar
Set    ExcludeFromShipping = 1
Where  DatePart(Weekday, CalendarDate) = 7
       Or DatePart(Weekday, CalendarDate) = 1

Update Calendar
Set    ExcludeFromShipping = 1
From   Calendar
       Inner Join tblHoliday T
         On Calendar.CalendarDate = T.Holiday_Date

Once you have this calendar table, you can calculate your expected delivery date like this...

** Note, I put your sample data in to a table variable so I could test this.

Declare @Temp Table(ShipDate DateTime, DaysTake Int)
insert Into @Temp Values('8/29/08',3)--            9/4/08 (exclude holiday/weekend)
insert Into @Temp Values('8/29/08',4)--            9/5/08 (exclude holiday/weekend)
insert Into @Temp Values('8/29/08',5)--            9/8/08 (exclude holiday/weekend)
insert Into @Temp Values('9/2/08' ,3)--            9/5/08 (exclude holiday/weekend)
insert Into @Temp Values('9/2/08' ,4)--            9/8/08 (exclude holiday/weekend)
insert Into @Temp Values('9/2/08' ,5)--            9/9/08 (exclude holiday/weekend)

Select  *,
        (Select Top 1 CalendarDate
        From   (
               Select Top (T.DaysTake) *
               From   Calendar
               Where  CalendarDate > T.ShipDate
                      And ExcludeFromShipping Is NULL
               Order By CalendarDate
               ) As A
        Order By CalendarDate Desc
        )
From	@Temp T

Also note, this will not work with SQL2000 because of this:

[tt][blue]Select Top [!](T.DaysTake)[/!][/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom