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