Return next highest value if not greater than zero

1

I have to write some sales data to a table for each district. each product is totaled and now I need to get the min sale for all products. The problem is it should only select if it is greater than zero. I have a couple minimum values that are less than or equal to zero so with the code below they are null. I want it to select the minimum value that is greater than zero.

 declare @WeekEnd datetime
 set @WeekEnd = (select top(1) week_ending_date from sales order by week_ending_date desc)

   insert into county
   select week_ending_date
   ,county_code
   ,district_code
   ,sum(product1) 
   ,sum(product2) 
   ,sum(product3) 
   ,sum(product4) 
  ,sum(product1 + product2+ product3+ product4)
  ,Case when min(product1 + product2+ product3+ product4) > 0 then min(product1 + product2+ product3+ product4)
  end --Here is where I am stuck. How do select the next highest min value if the lowest is not greater than zero?
from sales where week_ending_date = @WeekEnd
group by week_ending_date,county_code,district_code
2

instead of case:

min(isnull(product1, 0.0) + min(isnull(produect2,0) + ...

3

Won't this just change a null value to zero. The only reason I have any nulls in the code above is because the case does not have a then statement defined so it doesn't know what to do when the min value is not greater than zero.

The sum of all of the individual products has a value.

If I were to take the detail of a county/district it might show

**store**  **min value**
store1      -500
store2      500
store3      1000

I want to select the minimum value unless it is less than zero. In the case above the minimum value should be 500.

4

OK, so make it isnull(case(when product > 0) then product end, 0.0)

5

I am still unclear on how isnull() is going to help me. Also I have 14 different products (the four above are just a sample) that are summed up into a total for that store. The minimum column I am trying to populate is the smallest total for stores in that district.

I did try something similar to what you just posted and it doesn't seem to like the case inside the isnull().

6

this works for me

select isnull(case when a > 0 then 1 end, 0)
from (values (1),(-1)) v(a)
7

You're very close, you just need to move the case to inside the MIN:

insert into county
   select week_ending_date
   ,county_code
   ,district_code
   ,sum(product1) 
   ,sum(product2) 
   ,sum(product3) 
   ,sum(product4) 
   ,sum(product1 + product2+ product3+ product4)
   ,min(Case when product1 + product2+ product3+ product4 > 0 
             then product1 + product2+ product3+ product4 end) 
from sales where week_ending_date = @WeekEnd
group by week_ending_date,county_code,district_code
8

I have to write some sales data to a table for each district. each product is totaled and now I need to get the min sale for all products. <<

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.

Did you know that there is ISO week notation? Build a calendar table with this data and use it.

Do you know what a repeated group violation of 1NF is? What you posted is not normalized, so you will be kludging DML all the time.

I have to write some sales data to a table for each district. each product is totaled and now I need to get the min sale for all products. <<

Total of what? The dollars or the quantity? Since we have no DDL, no spec and no sample data, I will guess quantity.

CREATE TABLE Sales
(sales_week CHAR(10) NOT NULL-able
CHECK(sales_week LIKE '[12][0-9][0-9][0-9]W[0-5][0-9]'),
product_gtin CHAR(15) NOT NULL,
county_code CHAR(5) NOT NULL
CHECK (county_code LIKE '[A-Z][A-Z][0-9][0-9][0-9]'),
district_code CHAR(3) NOT NULL CHECK (district_code LIKE ??),
PRIMARY KEY (sales_week, product_gtin, county_code, district_code)
sale_qty INTEGER NOT NULL CHECK (sale_qty > 0)
));

The problem is it should only select if it is greater than zero. I have a couple minimum values that are less than or equal to zero so with the code below they are NULL. I want it to select the minimum value that is greater than zero. <<

How is that possible? What attribute of a product are we adding that can be negative? Not price, not quantity? This why we want DDL, sample data and specs in a normalized table.

WITH X1
AS
(SELECT sale_week, county_code, district_code,
SUM(sale_qty) AS sale_qty_tot
FROM Sales
GROUP BY sale_week, county_code, district_code),
X2
AS
(SELECT sale_week, county_code, district_code, sale_qty_tot,
DENSE_RANK()
OVER (PARTITION BY sale_week, county_code, district_code,
ORDER BY sale_qty_tot DESC) AS sale_qty_tot_rank
FROM X1)

SELECT *
FROM X2
WHERE sale_qty_tot_rank IN (1,2);

Untested due to lack of specs and sample data.