Jim, Yeah that is what I original wrote, but the problem is that it is not a sum of amount, it is number of days by the amount that it falls in, and also needs to take into consideration if there are free days hence amount equals null.
But the complicated bit is if the days selected falls in the free day range, it needs to go one step back as described in my first post. Mind you I never thought of the case statement to do what you did I used a where clause instead. 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 1:17 AM To: SQL Subject: Re: Not sure how to do this Andrew Scott wrote: > > Any ideas how to return just the $amount rather than all the rows based on > the number of days? > If I understand the logic of what you are trying to do, then try something like... SELECT (SUM(ToDay - FromDay + 1) * min(CASE Amount WHEN 0 THEN NULL ELSE Amount END)) FROM SomeTable WHERE ToDay < 12 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:2650 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
