Exclude Row If Calculated Column Is Zero

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Exclude Row If Calculated Column Is Zero

Author

Topic

downoteof
Starting Member

1 Post

Posted - 2013-10-03 : 23:52:51

I'm fairly new to SQL (second day using it and was thrown into it without any help at all) and have been hitting wall after wall while trying to make my SQL statement work how I think it should work. I have finally hit a wall that has me completely stumped.

A little bit of background: I am attempting to create an SQL statement that will query a file and give me amount totals by company number/customer number. The totals have to be combined into 4 groups (1/2/3/4) for each amount total in company number/customer number combination. In effect it will look something like this:

COMPANY | CUSTOMER | SORT | AMOUNT
==================================
00001 | 11111 | 1 | $55
00001 | 11111 | 2 | $12
00001 | 11111 | 3 | $19
00001 | 11111 | 4 | $ 0
00001 | 22222 | 1 | $99
00001 | 22222 | 2 | $53
...and so on.

I HAVE THIS PART WORKING ALREADY. The problem is that I am trying to exclude the rows that have 0 (zero) in the amount column from showing up in the output. The amount is a calculated field of all the invoice for that company number/customer number combination for that sort (eg: Company 00001/Customer 11111/Sort 1 has $55 associated to it). I cannot use the calculated field in my where clause (my life would be infinitely easier if I could) so I'm kind of at a brick wall right now. Any help would be greatly appreciated!

I will include a simplified version of my select statement so you can see how I got as far as I have and perhaps you can give me an idea where to go so I pretty much say "WHERE SUM(SubTBL.Amount) <> 0".


----SELECT STATEMENT-----
SELECT 
MainTBL.Cust#, 
SUM(SubTBL.Amount) As TotAmt,

CASE
WHEN (days (currdate) - days (MainTBL.DateFLD)) <= 30 THEN '1'
WHEN (days (currdate) - days (MainTBL.DateFLD)) BETWEEN 31 AND 60 THEN '2'
WHEN (days (currdate) - days (MainTBL.DateFLD)) BETWEEN 61 AND 90 THEN '3'
WHEN (days (currdate) - days (MainTBL.DateFLD))> 90 THEN '4'
ELSE 'Unknown'
END As Sort


----FROM STATEMENT-----
FROM 
DB.MainTBL As MainTBL
INNER JOIN 
(SELECT CompNo
CASE 
WHEN RecType = 'C' AND AmtDue <> 0 THEN AmtDue * -1
ELSE AmtDue
END AS Amount
FROM
DB.MainTBL) As SubTBL
ON (MainTBL.CompNo = SubTBL.CompNo)


----WHERE STATEMENT-----
WHERE 
MainTBL.CompNo = 1


----GROUP BY STATEMENT-----
GROUP BY
MainTBL.CompNo, 
MainTBL.Cust#,
CASE
WHEN (days (currdate) - days (MainTBL.DateFLD)) <= 30 THEN 'Current'
WHEN (days (currdate) - days (MainTBL.DateFLD)) BETWEEN 31 AND 60 THEN 'Over 30'
WHEN (days (currdate) - days (MainTBL.DateFLD)) BETWEEN 61 AND 90 THEN 'Over 60'
WHEN (days (currdate) - days (MainTBL.DateFLD))> 90 THEN 'Over 90'
ELSE 'Unknown'
END


----ORDER BY STATEMENT-----
ORDER BY
MainTBL.CompNo, 
MainTBL.Cust#,
Sort

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-10-04 : 00:02:02

use the HAVING clause

see http://technet.microsoft.com/en-us/library/ms180199.aspx

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources