I have the following query that works and gets me the total that I need.
However, i need to change it so that it just displays the final total instead of every transaction leading up to the total. I have tried converting it by group by rollup, group by group sets, using max transaction number, but my total never matches and I don’t fully understand the SUM decode part of select statement. I believe i need to move part of that to the where statement to do what i need.
Here is the query:
SELECT SUM(DECODE(tbbdetc_type_ind,'C', (-1) * a.tbraccd_amount,'P',a.tbraccd_amount) ) OVER( PARTITION BY a.tbraccd_pidm ORDER BY a.tbraccd_tran_number ) "Running total", tbbdetc.tbbdetc_detail_code, tbbdetc.tbbdetc_type_ind, a.* FROM tbraccd a JOIN taismgr.tbbdetc ON a.tbraccd_detail_code = tbbdetc.tbbdetc_detail_code WHERE a.tbraccd_pidm = '101010101' order by tbraccd_tran_number desc;
Any help on how to convert this so i just get the grand total that matches the final running total would be appreciated. My end goal would be to get the grand total for a list of pidms(IDS) one total for each pidm in a row.
You would seem to want to remove the
order by. I’ll also do the favor of converting to standard SQL syntax:
sum(case when tbbdetc_type_ind = 'C' then - a.tbraccd_amount when tbbdetc_type_ind = 'P' then a.tbraccd_amount end) over (partition by a.tbraccd_pidm) as total_total