My SQL group statement results in the below table. From this table, I need to subtract Qty with Code ='S' from Qty with code ='B' when Price and Date are matching.
For example, in the below table I need the value to be stored in a work variable.
1) 100-50 = 50 for the first 2 rows
2) 60 -30 = 30 for the 3rd and 4 th row
3) The last row since it does not have code 'S' it should return just 20
Table
Price Date Code Sum(Qty)
9.0 201512 B 100
9.0 201512 S 50
8.0 201506 B 60
6.0 201506 S 30
5.0 201508 B 20
SQL query used to get the above table
select Price, Date, Code,sum(Qty) from Table
where Type = 'A' and Acct = 'CLOSED'
group by Price,Date,Code
order by Price,Date
Can I modify my existing SQL statement typed above using CASE statements to get my required output. I tried it but Cursor returns one by one row and CASE does not seem to work
exec sql
declare c1 cursor for
select Price, Date, Code,
Case when Code ='B' then ifnull(sum(Qty),0)
when Code ='S' then (-1 * ifnull(sum(Qty),0)) end
from Table
where Type = 'A' and Acct = 'CLOSED'
group by Price,Date,Code
order by Price,Date
exec sql
open c1
exec sql
fetch c1 into :var_price, :var_date, :var_code, :var_Bqty, :VarSqty
Using SQLRPGLE on iseries system.
You can use conditional aggregation for this:
select Price, Date,
sum(case when code = 'B' then Qty when code = 'S' then -QTY end) as diff
from Table
where Type = 'A' and Acct = 'CLOSED'
group by Price, Date
order by Price, Date;