MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Sorting with condition

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
SK2007
Beginner


Joined: 25 Jan 2007
Posts: 40
Topics: 17
PostPosted: Mon Jul 11, 2011 12:38 pm    Post subject: Sorting with condition Reply with quote
Hi

I have dataset with 4 columns:
1) Mbr-id - 9 bytes
2) RT# - 8 bytes
3) BT# - 9 bytes
4) year and month - 6 bytes (ex; 201101)

The sample data is
Quote:

----+----1----+----2----+----3----+
***************************** Top o
123456789 11111111 222134567 201005
123456789 11111111 222134567 201006
123456789 11111134 222134457 201007
123456789 11111134 222134457 201008
123456789 11111134 222134457 201009
123456789 11113434 345134457 201010
123456789 11113434 345134457 201011
123456790 32432434 546564457 201006
123456790 32432434 546564457 201007
123456790 32432434 546564457 201008
123456790 34535634 436767457 201009
123456790 84595634 954687457 201010


I want to find, howmany times RT and BT is changed between 201006 to 201105? And dont count if RT and BT is changed before 201006.
For first customer, the BT and RT is same for 201005 and 201006. In this case I dont want to count this, as the RT and BT is ahcnged in 201005.
When there is a change in RT, BT also should be changed.

My expected output is


Quote:

123456789 - 2
123456790 - 3


I am using ICETOOL. Thanks for your help as always.
SK2007
Back to top
View user's profile Send private message
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Mon Jul 11, 2011 1:28 pm    Post subject: Reply with quote
SK2007,
Correct me If I am wrong but I don't see 123456790 changing 3 times as shown in your expected output.
Code:

123456789 11111111 222134567 201005     ****NOT COUNTED****
123456789 11111111 222134567 201006     INITIAL VALUE
123456789 11111134 222134457 201007     FIRST CHANGE-1
123456789 11111134 222134457 201008     SAME VALUE-1
123456789 11111134 222134457 201009     SAME VALUE-1
123456789 11113434 345134457 201010     SECOND CHANGE-2
123456789 11113434 345134457 201011     SAME VALUE-2

123456790 32432434 546564457 201006     INITIAL VALUE
123456790 32432434 546564457 201007     SAME VALUE-0
123456790 32432434 546564457 201008     SAME VALUE-0
123456790 34535634 436767457 201009     FIRST CHANGE-1
123456790 84595634 954687457 201010     SECOND CHANGE-2


Based on this shouldn't your expected output be ...
Code:
123456789 - 2
123456790 - 2

Thanks,
Back to top
View user's profile Send private message
SK2007
Beginner


Joined: 25 Jan 2007
Posts: 40
Topics: 17
PostPosted: Mon Jul 11, 2011 1:56 pm    Post subject: Reply with quote
Sqlcode,

Thanks for your response.
For mbr-id 123456790 i should get 3. The BT#s are
32432434
34535634
84595634
Also the first BT was on 201006, and no record on 201005.

Thanks
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
PostPosted: Mon Jul 11, 2011 2:13 pm    Post subject: Reply with quote
SK2007,

I am not sure why you did not pick the following BT # for the mbr id # 123456789

11111111
11111134
11113434

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
SK2007
Beginner


Joined: 25 Jan 2007
Posts: 40
Topics: 17
PostPosted: Mon Jul 11, 2011 2:33 pm    Post subject: Reply with quote
Kolusu,

For this mbr-id 123456789, the BT is entered in 201005, and continue same in 201006. I dont want to count anything entred in 201005 and continue same in 201006..
If BT entered in 201005 and if it is different in 201006, then I want to count.
If no BT in 201005, and it is in 201006, then I want to count.

Thanks for your help.
Back to top
View user's profile Send private message
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Mon Jul 11, 2011 2:34 pm    Post subject: Reply with quote
SK2007 wrote:
Sqlcode,

Thanks for your response.
For mbr-id 123456790 i should get 3. The BT#s are
32432434
34535634
84595634
Also the first BT was on 201006, and no record on 201005.

Thanks

So going with this theory, 123456789 should also have 3 because it has 3 different RT#/BT#
1) 11111111 222134567
2) 11111134 222134457
3) 11113434 345134457

Now the question is ,would you include 201006 as a valid change? I was assuming this as a valid record because you mentioned "between 201006 to 201105".

Thanks,
Back to top
View user's profile Send private message
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Mon Jul 11, 2011 2:35 pm    Post subject: Reply with quote
Never mind, I think you answered my question as well in reponse to Kolusu's question.

Thanks,
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
PostPosted: Mon Jul 11, 2011 2:47 pm    Post subject: Reply with quote
SK2007,

Use the following DFSORT JCL which will give you the desired results

Code:

//STEP0100 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD *                                                       
----+----1----+----2----+----3----+----4----+----5----+----6----+----7
123456789 11111111 222134567 201005                                   
123456789 11111111 222134567 201006                                   
123456789 11111134 222134457 201007                                   
123456789 11111134 222134457 201008                                   
123456789 11111134 222134457 201009                                   
123456789 11113434 345134457 201010                                   
123456789 11113434 345134457 201011                                   
123456790 32432434 546564457 201006                                   
123456790 32432434 546564457 201007                                   
123456790 32432434 546564457 201008                                   
123456790 34535634 436767457 201009                                   
123456790 84595634 954687457 201010                                   
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                       
  OMIT COND=(30,6,CH,LT,C'201005',OR,                                 
             30,6,CH,GT,C'201105')                                   
  INREC OVERLAY=(81:C'01')                                           
  SORT FIELDS=(1,28,CH,A),EQUALS                                             
  SUM FIELDS=(81,2,ZD)       
  OUTFIL REMOVECC,NODETAIL,BUILD=(80X),OMIT=(30,6,CH,EQ,C'201005'),     
  SECTIONS=(1,9,TRAILER3=(1,9,X,COUNT=(M10,LENGTH=8)))                 
//*


The output is
Code:

123456789        2
123456790        3

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
SK2007
Beginner


Joined: 25 Jan 2007
Posts: 40
Topics: 17
PostPosted: Mon Jul 11, 2011 10:18 pm    Post subject: Reply with quote
Thanks much Kolusu. It is working as expected.
One more small request, how to get the total count for all mbr-ids. That means for above request the Total count is 5.

Thanks again for your help.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
PostPosted: Tue Jul 12, 2011 10:22 am    Post subject: Reply with quote
SK2007,

Quite simple . Add a comma at the end of sections statement and use trailer1 like shown below

Code:

  SECTIONS=(1,9,TRAILER3=(1,9,X,COUNT=(M10,LENGTH=8))),     
  TRAILER1=(9'=',/,'TOTAL :',3X,COUNT=(M10,LENGTH=8))       
//*


The output will be
Code:

123456789        2
123456790        3
=========         
TOTAL :          5


On the other hand I just realized that you don't need to pad a constant at the end and perform a sum on it instead you can use SUM Fields=NONE to eliminate the dups. I had another solution in mind before I saw you clarifications and went ahead with it. Use the following control cards

Code:

//SYSIN    DD *                                                     
  OMIT COND=(30,6,CH,LT,C'201005',OR,                               
             30,6,CH,GT,C'201105')                                 
  SORT FIELDS=(1,28,CH,A),EQUALS                                           
  SUM FIELDS=NONE                                                   
  OUTFIL REMOVECC,NODETAIL,OMIT=(30,6,CH,EQ,C'201005'),
  SECTIONS=(1,9,TRAILER3=(1,9,X,COUNT=(M10,LENGTH=8))),             
  TRAILER1=(9'=',/,'TOTAL :',3X,COUNT=(M10,LENGTH=8))               
//*

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
SK2007
Beginner


Joined: 25 Jan 2007
Posts: 40
Topics: 17
PostPosted: Wed Jul 13, 2011 2:40 pm    Post subject: Reply with quote
Thank you so much, Kolusu.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group