Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Group changes and numbering the groups with recurrence

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Auryn

New User


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

PostPosted: Thu Aug 16, 2018 1:32 pm    Post subject: Group changes and numbering the groups with recurrence
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

Senior Member


Joined: 21 Sep 2010
Posts: 2030
Location: NY,USA

PostPosted: Fri Aug 17, 2018 4:34 pm    Post subject:
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: 60
Location: Lower Saxony (DE)

PostPosted: Fri Aug 17, 2018 6:43 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Group changes and numbering the groups Auryn DB2 2 Tue Mar 20, 2018 7:12 pm
No new posts How to group and sum the decimal value sakrat DFSORT/ICETOOL 2 Tue Mar 06, 2018 9:13 pm
No new posts Copy Specific Groups V S Amarendra Reddy SYNCSORT 10 Thu Jan 11, 2018 1:16 am
No new posts RACF - Sub groups - how they work? vasanthz JCL & VSAM 1 Wed Jan 10, 2018 6:44 am
No new posts Split files upto certain limit based ... bubbu75 DFSORT/ICETOOL 5 Tue Nov 14, 2017 11:11 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us