MVSFORUMS.com

nag77 · Beginner Joined: 03 Jan 2006 Posts: 11 Topics: 8

Hi,

I have to get the sum of the amounts for an account number till the current year. The conditions are that all amounts with Indicator = 'C' should be added and all the amounts with Indicator = 'D' should be subtracted.

Can u suggest a query for these conditions.

thanks
Nag

kolusu · Posted: Fri Feb 24, 2006 6:18 am Post subject:

nag77,

Try this untested sql

shekar123 · Posted: Thu Mar 02, 2006 1:40 am Post subject:

Nag,

I guess you are looking for a scenario like below:

Code:

CREATE TABLE SHEKAR.AMOUNTS
(
INDICATOR CHAR(1) NOT NULL,
AMOUNT INTEGER NOT NULL
)
IN DBTEMP.TBTEMP;
INSERT INTO SHEKAR.AMOUNTS VALUES ('C',1000);
INSERT INTO SHEKAR.AMOUNTS VALUES ('D',500);
INSERT INTO SHEKAR.AMOUNTS VALUES ('C',1000);
INSERT INTO SHEKAR.AMOUNTS VALUES ('C',100);
INSERT INTO SHEKAR.AMOUNTS VALUES ('D',1000);
INSERT INTO SHEKAR.AMOUNTS VALUES ('D',500);
INSERT INTO SHEKAR.AMOUNTS VALUES ('C',500);
INSERT INTO SHEKAR.AMOUNTS VALUES ('D',1000);
INSERT INTO SHEKAR.AMOUNTS VALUES ('C',1000);
INSERT INTO SHEKAR.AMOUNTS VALUES ('C',1111);
SELECT SUM(CASE WHEN INDICATOR = 'C' THEN AMOUNT
WHEN INDICATOR = 'D' THEN AMOUNT * -1
ELSE 0
END)
FROM SHEKAR.AMOUNTS;

OUTPUT

Code:

-------------- C -------------------- ---------- D ----------
1711 [(1000 + 1000 + 100 + 500 + 1000 + 1111) - (500 + 1000 + 500 + 1000)]

_________________
Shekar
Grow Technically