Andrew Scott wrote: > 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.
the case statement should take care of that...see below > > Mind you I never thought of the case statement to do what you did I used a > where clause instead. maybe something along these lines.... 12 - SUM((today - fromday + 1) * CASE amount WHEN 0 THEN 1 ELSE 0 END) AS numberofdays, min(CASE amount WHEN 0 THEN NULL ELSE amount END) AS minamount ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:2651 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
