Need help forming a statement

Other SQL Server Topics
1

Hi Folks.

New user/just joined. Not a boat load of exp.

Not sure if it's possible but I'm trying to form a statement that excludes results equaling zero. Right now I have

SELECT cu_name, hd_sku, 
sum(case when hd_type in ('SRR','SRC','ARR','ARC','URR','URC','LRR','LRC','DRR','DRC','LNO','LND') and 
0 = 1 then 0 else hd_trn_qty + hd_adj_qty end) AS qty, (sg_weight/16)*hd_trn_qty as wght

It's the last bit that's got me... I want to say WHERE wght <> 0, but wght is not recognized as a column. I'm thinking this can be accomplished with a CASE statement but, again, I don't know how to form it, I've never dived this deep into SQL before.

Now I could be an idiot and a CASE statement is not the way to go, which is what brings me here to the people who are more well versed than I. Any help is greatly appreciated.

2

You cannot use aliases in the WHERE clausule, you have to use the fields itself:

WHERE (sg_weight/16)*hd_trn_qty <> 0 should work.

1 Like
3

I think you also need to SUM that column:

SUM(sg_weight/16)*hd_trn_qty) AS wght

And if so - then it needs to go in the HAVING clause:

GROUP BY ...
HAVING SUM(sg_weight/16)*hd_trn_qty) <> 0

Unless you are grouping by the weight - but I don't see why you would be doing that.

1 Like
4

Yup, that's the ticket. I didn't even think to try the formula in the WHERE clause. Thank you very much!

5

You are correct Sir. I did Sum that column (after the fact) and because there is always more than one way to skin a cat, both options work here... Adding it to the WHERE or adding a HAVING.

6

I prefer using a CROSS APPLY to assign a name to the calc'd value(s). This avoids having to repeat calcs multiple times, which can be a pain if the calc changes later.

You could get different results with WHERE vs HAVING. I would think WHERE here would be more accurate.


SELECT cu_name, hd_sku, 
    sum(case when hd_type in ('SRR','SRC','ARR','ARC','URR','URC','LRR','LRC','DRR','DRC','LNO','LND') and 
        0 = 1 then 0 else hd_trn_qty + hd_adj_qty end) AS qty,
    sum(wght)
FROM dbo.your_table
CROSS APPLY (
    SELECT sg_weight/16*hd_trn_qty as wght
) AS ca1
WHERE wght <> 0