SQL query grouping by range

Question

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.

score 2 · Accepted Answer · answered Sep 24 '20 at 08:00

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

Thank you very much for your help, your answer solved my problem. — Elvis D., Sep 24 '20 at 08:58

score 0 · Answer 2 · answered Sep 24 '20 at 07:43

You can try the below -

select min(year)||'-'||min(month) as from_date,max(year)||'-'||max(month) as to_date,sum(amount) as amount from
(
select *,row_number() over(order by month)-
row_number() over(partition by pa order by month) as grprn
from t1
)A group by grprn,pa order by grprn

score 0 · Answer 3 · answered Sep 24 '20 at 07:46

This works in tsql, guess you can adaot it to db2-400?

SELECT MIN(Dte) [From]
     , MAX(Dte) [To]
       --   ,PA
     , SUM(Amount)
FROM (

    SELECT year * 100 + month Dte
         , Pa
         , Amount
         , ROW_NUMBER() OVER (PARTITION BY pa ORDER BY year * 100 + month) +
             10000- (YEar*100+Month) rn

    FROM tabA a
) b
GROUP BY Pa
       , rn
ORDER BY [From]
       , [To]

The trick is the row number function partitioned by PA and ordered by date, This'll count one up for each month for the, when added to the descnding count of month and month, you will get the same number for consecutive months with same PA. You the group by PA and the grouping yoou made, rn, to get the froups, and then Bob's your uncle.

SQL query grouping by range

3 Answers3