lundi 25 janvier 2016

using Case in select statement

I have a situation where I am summing up several columns from a table and inserting the results into another table. This is being grouped by county and district. One of the columns is also taking the smallest total sales from a retailer in that district. The problem I have is that there may be some that have less than zero total sales. I only want to write the smallest value that is greater than zero to that column.

declare @WeekEnd datetime
set @WeekEnd = (select top(1) date from sales order by date desc)
select date
,county
,district
,sum(prod1) 
,sum(prod2) 
,sum(prod3) 
,sum(prod4) 
,sum(prod1+prod2+prod3+prod4) --Total Sales
,Case when min(prod1+prod2+prod3+prod4) > 0 then min(prod1+prod2+prod3+prod4)
--this works well except for when a total is less than zero, then it is null. I want to avoid the null and have it write the smallest value greater than zero. 
end
from sales 
where date = @WeekEnd
group by date,county,district
order by county, district

Aucun commentaire:

Enregistrer un commentaire