Hi have a table A with the following data:
+------+-------+----+--------+ | YEAR | MONTH | PA | AMOUNT | +------+-------+----+--------+ | 2020 | 1 | N | 100 | +------+-------+----+--------+ | 2020 | 2 | N | 100 | +------+-------+----+--------+ | 2020 | 3 | O | 100 | +------+-------+----+--------+ | 2020 | 4 | N | 100 | +------+-------+----+--------+ | 2020 | 5 | N | 100 | +------+-------+----+--------+ | 2020 | 6 | O | 100 | +------+-------+----+--------+
I’d like to have the following result:
+---------+---------+--------+ | FROM | TO | AMOUNT | +---------+---------+--------+ | 2020-01 | 2020-02 | 200 | +---------+---------+--------+ | 2020-03 | 2020-03 | 100 | +---------+---------+--------+ | 2020-04 | 2020-05 | 200 | +---------+---------+--------+ | 2020-06 | 2020-06 | 100 | +---------+---------+--------+
My DB is DB2/400. I have tried with ROW_NUMBER partitioning, subqueries but I can’t figure out how to solve this.
Advertisement
Answer
I understand this as a gaps-and-island problem, where you want to group together adjacent rows that have the same PA.
Here is an approach using the difference between row numbers to build the groups:
select min(year_month) year_month_start, max(year_month) year_month_end, sum(amount) amount
from (
select a.*, year * 100 + month year_month
row_number() over(order by year, month) rn1,
row_number() over(partition by pa order by year, month) rn2
from a
) a
group by rn1 - rn2
order by year_month_start