Sorry Jim, I might not have explained it to well either.
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? I suppose I could do a group on this and do more calculations that way? Now I have to look at the following to go along with that 1-3 $100 total for 3 days 4-6 $75 total for 3 days 7+ $50 day for every day there after So 7 days would be (3 * 100) + (3 * 75) + n * 50, with the same concept of free days as well. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:2654 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
