I want to get a list of those accounts that has zero balance as a total, but I used below query, it still showing accounts that has balance for other year/month, it only have one year has balance =0...the rest still has balance. How do we fix the query to make it shows only the one that has zero balance for all year/month. Thanks,
SELECT ACTNO, YEAR, MONTH, SUM(BAL) AS BALANCE FROM TABLE1 GROUP BY ACTNO, YEAR, MONTH HAVING SUM(CASE WHEN BAL IS NOT NULL THEN BAL ELSE 0 END)=0
SELECT
ACCTNO
FROM
TABLE1 t1
WHERE
NOT EXISTS
(
SELECT t2.ACTNO
FROM TABLE1 t2
WHERE t1.ACCTNO = t2.ACCTNO
GROUP BY
t2.ACTNO,
t2.YEAR,
t2.MONTH
HAVING SUM(CASE WHEN BAL IS NOT NULL THEN BAL ELSE 0 END) <> 0
)
SELECT
ACCTNO
FROM
TABLE1 t1
WHERE
NOT EXISTS
(
SELECT t2.ACTNO
FROM TABLE1 t2
WHERE t1.ACCTNO = t2.ACCTNO
GROUP BY
t2.ACTNO,
t2.YEAR,
t2.MONTH
HAVING SUM(CASE WHEN BAL IS NOT NULL THEN BAL ELSE 0 END) <> 0
)
sql ?
Posted - 09/07/2012 : 14:14:56
