IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Group changes and numbering the groups with recurrence


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Auryn

New User


Joined: 11 Jan 2006
Posts: 67
Location: Lower Saxony (DE)

PostPosted: Thu Aug 16, 2018 1:32 pm
Reply with quote

Hi there and good morning everybody,

some weeks ago I was asking for some support concering numbering groups of rows (see thread #66500 » Group changes and numbering the groups«). The hint was very helpful.

But – a very similar problem, only a bit more tricky: The groups depend on chronology and can recur. Some Sample data:
Code:
ID_COL  COL_1    YEAR_COL  COL_2   NUM_LVL_1  NUM_LVL_2  GRP_NUM
     3  Alpha    2001      One             1          1        1
     5  Alpha    2002      One             2          2        1
     7  Alpha    2003      One             3          3        1
    12  Alpha    2004      Two             4          1        2
    15  Alpha    2005      Two             5          2        2
    17  Alpha    2006      Three           6          1        3
    19  Alpha    2007      One             7          1        4
     1  Bravo    2001      Four            1          1        1
     4  Bravo    2002      Five            2          1        2
     8  Bravo    2003      Six             3          1        3
    11  Bravo    2004      Seven           4          1        4
    13  Bravo    2005      Eight           5          1        5
     2  Charlie  2001      Nine            1          1        1
     6  Charlie  2002      Ten             2          1        2
     9  Charlie  2003      Ten             3          2        2
    10  Charlie  2004      Eleven          4          1        3
    14  Charlie  2005      Nine            5          1        4
    16  Charlie  2006      Eleven          6          1        5
    18  Charlie  2007      Eleven          7          2        5

Note:
In COL_1 = "Alpha", COL_2 = "One" occurs three times in 2001 to 2003 and another time in 2007,
in COL_1 = "Chrarlie", COL_2 = "Nine" occures once in 2001 and once in 2005.
So in both cases groups "One" respectively "Nine" have to be counted twice.

The goal should be to receive the quantity of different groups within COL_1 (see max. of column GRP_NUM):
Code:
COL_1    Quantity
Alpha           4
Bravo           5
Charlie         5


So I’d say the most problematic thing is to calculate the GRP_NUM.
If this is done, I could use that query as a sub-select to get the second result.

Thanks for your hints in advance
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2567
Location: NYC,USA

PostPosted: Fri Aug 17, 2018 4:34 pm
Reply with quote

1.You put the second column name on second line under first column name which don’t makes sense , repost only the relevant columns, last two looks obsolete too.
2. How you come up with GRP_NUM values? Is it required any where in the calculation to achieve what you posted ?
3 say what is input and what’s output you want and then add separately the approach you are thinking of , right now it’s all mixed, please state only the requirements first.
Back to top
View user's profile Send private message
Auryn

New User


Joined: 11 Jan 2006
Posts: 67
Location: Lower Saxony (DE)

PostPosted: Fri Aug 17, 2018 6:43 pm
Reply with quote

Only cols 2 to 4 (COL_1, YEAR_COL, COL_2) are relevant.
First col ID_COL and cols 5 to 7 (NUM_LVL_1, NUM_LVL_2, GRP_NUM) are obsolete.
Input are cols 1 to 4 (ID_COL, COL_1, YEAR_COL, COL_2), cols 5 to 7 are 'hand-made' samples.
The last col GRP_NUM should be a interim result and helpful for the final result (quantity = MAX(grp_num), GROUP BY col_1)
Sorry for confusing
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Hexadecimal Group compare and write t... DFSORT/ICETOOL 5
No new posts DB2 group member usage DB2 0
No new posts Group comparison/update between two f... DFSORT/ICETOOL 10
No new posts Group selective VB records - Trailer ... DFSORT/ICETOOL 16
No new posts Include Group record based on detail ... DFSORT/ICETOOL 11
Search our Forums:

Back to Top