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 |
|
ITTrucker
Yak Posting Veteran
64 Posts |
 Posted - 2012-03-15 : 14:19:39
|
| Samples below.I'm trying to join a date with a range of dates and I'm having trouble getting the dates to work the way I want.We have a fuel table that is updated Tuesday of each week with an effective date (Tuesday) and an average fuel cost.We have an invoice table that has the date an order was completed. I'm trying to write a report that calculates a billed fuel surcharge based on when the order was completed and what the average price was at the time of completion. I thought I had it by converting the completion date to a Tuesday and joining to the fuel table, but I'm always converting to the current week so my Sunday and Monday's are off.I'd like to make it so a load delivered on Monday March 12th would look at the previous Tuesday the 6th in the fuel table and use that price, and a load delivering on Tuedsay March 13th would look at the 13th in the fuel table etc.--Where I am right now that isn't working for Sunday and Monday's SELECT 'Order #' = H.order, 'Del Date' = H.DELIVERYDATE,'FSC Rate' = CEILING((a.afp_price - 1.20)/.06)/100FROM invoice H INNER JOIN averagefuel a on DATEADD(ww, DATEDIFF(ww,0,H.DELIVERYDATE), 1) = a.afp_dateafp_date afp_price2012-03-13 00:00:00.000 4.2472012-03-06 00:00:00.000 4.2432012-02-28 00:00:00.000 4.208 |
|
|
ITTrucker
Yak Posting Veteran
64 Posts |
Posted - 2012-03-15 : 14:36:23
|
| It looks like I got it...dateadd(week, datediff(week, 0, dateadd(dd,-2,H.DELIVERYDATE)), 1)I added a dateadd -2 because needed to subtract 2 days from the date to account for it being a Tuesday start of week. Looks like it's working now. |
 |
|
|
|
|
|
|
|
Week starting with Tuesday