Forgot to add that there is another field that says whether it is a day price or not?
Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -----Original Message----- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: Tuesday, 5 December 2006 2:59 PM To: SQL Subject: Re: Not sure how to do this Andrew Scott wrote: > > Ok, the query returns the right data in almost the right circumstances. > Except if the free days are as follows > > FromDay = 7 > ToDay = 9 > > 7 - SUM((today - fromday + 1) * CASE amount WHEN 0 THEN 1 ELSE 0 END) As > Days , > MIN(CASE amount WHEN 0 THEN NULL ELSE amount END) AS Amount > > FROM CarHireRates > WHERE CarHirePricesId = 2 AND FromDay <= 7 > > Actually returns 5 days instead of 6 at $55.00, other than that I am going > to have too look more closely at this case statement its brilliant? > ok, but now we are getting ugly... SELECT 8 - SUM((CASE WHEN (8 >= fromday AND 8 < today) THEN 8 ELSE today END - fromday + 1) * CASE amount WHEN 0 THEN 1 ELSE 0 END) AS Amount, min(CASE amount WHEN 0 THEN NULL ELSE amount END) AS Amount It seems like you might think about setting up a view to make this data easier to work with...something like... create a table of days.... DayID 1 2 3 etc.... Then create a view joining that table with your CarHireRates table.... SELECT a.DayID,b.amount FROM Days a INNER JOIN CarHireRates b ON a.DayID >= b.fromday AND a.DayID <= b.today ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2659 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
