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

Sort Card to get the records with overlapping dates


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
maddyinfy

New User


Joined: 24 Nov 2007
Posts: 6
Location: AP

PostPosted: Mon Nov 16, 2009 3:02 pm
Reply with quote

Hi,
I have a requirement to get the records from an input file that has overlapping dates only
based on the key.

Here are the details of my input file :


RECFM=FB,LRECL=79

Start Position,Length,Type of fields :

CIC - (1,5,PD)

UPC - (6,8,PD)

Facility - (20,4,CH)

Vendor - (24,9,CH)

Date-Eff - (33,10,CH)

Date-Off - (43,10,CH)


Input File Data :



CIC UPC Facility Vendor Date_Eff Date_Off

CIC1 UPC1 FAC1 V1 01/01/2009 12/31/2009
CIC1 UPC1 FAC1 V2 03/03/2009 12/31/2009

CIC2 UPC3 FAC2 V3 01/01/2009 12/31/2009
CIC2 UPC4 FAC2 V4 03/03/2009 12/31/2009

CIC3 UPC5 FAC3 V5 01/01/2009 12/31/2010
CIC3 UPC5 FAC3 V6 03/03/2011 12/31/2019

CIC4 UPC6 FAC4 V7 01/01/2011 12/31/2019
CIC4 UPC7 FAC4 V8 03/03/2009 12/31/2010


In each case of the 4 cases ,the records have same CIC and FAC.

Records of CIC1 have overpalling dates and should come out

Records of CIC2 have overpalling dates and should come out

Records of CIC3 have mutually exclusive dates and should be eliminated

Records of CIC4 have mutually exclusive dates and should be eliminated



Expected Output :

CIC UPC Facility Vendor Date_Eff Date_Off

CIC1 UPC1 FAC1 V1 01/01/2009 12/31/2009
CIC1 UPC1 FAC1 V2 03/03/2009 12/31/2009


CIC2 UPC3 FAC2 V3 01/01/2009 12/31/2009
CIC2 UPC4 FAC2 V4 03/03/2009 12/31/2009


I'm trying to do this using the sort cards.

Can anyone help me out with the logic?
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Tue Nov 17, 2009 2:15 am
Reply with quote

In your example, it appears CIC4 has overlapping dates

01/01/2011 12/31/2019
03/03/2009 12/31/2010

so why isn't it included in the output? Did you mean to have 12/31/2009 instead of 12/31/2019?

Do you always have just two records for each CIC and FIC?

For each record, is the Date_Eff always less than the Date_Off as shown in your example?

Can the following test be used to determine if the pair of records does not have overlapping dates:

(Record2 Date_Eff > Record1 Date_Off) and
(Record2 Date_Off > Record1 Date_off)

If not, what test can be used?
Back to top
View user's profile Send private message
maddyinfy

New User


Joined: 24 Nov 2007
Posts: 6
Location: AP

PostPosted: Tue Nov 17, 2009 11:40 am
Reply with quote

Frank Yaeger wrote:
In your example, it appears CIC4 has overlapping dates

01/01/2011 12/31/2019
03/03/2009 12/31/2010

so why isn't it included in the output? Did you mean to have 12/31/2009 instead of 12/31/2019?

Do you always have just two records for each CIC and FIC?

For each record, is the Date_Eff always less than the Date_Off as shown in your example?

Can the following test be used to determine if the pair of records does not have overlapping dates:

(Record2 Date_Eff > Record1 Date_Off) and
(Record2 Date_Off > Record1 Date_off)

If not, what test can be used?


Frank,I still see that CIC4 has no overlapping dates.If we have closer look at it comparing to the previous records,I can say that the order of dates is reversed and it is possible in my case.

As per my information,there would be only 2 records for each CIC and FAC.(Can we think of any technique if we have more than 2 by any chance).

Yes,Date_Eff always less than the Date_Off .

Considering the order of dates too, for pair of records that does not have overlapping dates:


the condition

(Record2 Date_Off < Record1 Date_eff) and
(Record2 Date_eff < Record1 Date_eff)

or

(Record2 Date_Eff > Record1 Date_Off) and
(Record2 Date_Off > Record1 Date_off)

might hold good.Please correct if i overlooked anything?
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Nov 18, 2009 12:26 am
Reply with quote

maddyinfy,

The following DFSORT JCL will give you the desired results



Code:

//STEP0100 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD DSN=Your input Fb 79 byte file,DISP=SHR
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  INREC OVERLAY=(80:1,5,20,4,39,4,33,2,36,2,49,4,43,2,46,2)         
  SORT FIELDS=(80,25,BI,A),EQUALS                                   
                                                                     
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(105:SEQNUM,8,ZD,RESTART=(80,9))),
  IFTHEN=(WHEN=GROUP,BEGIN=(105,8,ZD,EQ,1),PUSH=(113:1,104))         
                                                                     
  OUTFIL IFOUTLEN=79,INCLUDE=(105,8,ZD,GT,1,AND,                     
  (89,8,CH,LT,209,8,CH,AND,97,8,CH,LE,209,8,CH)),                   
  IFTHEN=(WHEN=(105,8,ZD,EQ,2),BUILD=(113,79,/,1,79))               
//*
Back to top
View user's profile Send private message
maddyinfy

New User


Joined: 24 Nov 2007
Posts: 6
Location: AP

PostPosted: Wed Nov 18, 2009 10:04 am
Reply with quote

Thanks for the code kolusu.Could you please explain the logic used in the sort card ?
Back to top
View user's profile Send private message
maddyinfy

New User


Joined: 24 Nov 2007
Posts: 6
Location: AP

PostPosted: Thu Nov 19, 2009 7:51 pm
Reply with quote

maddyinfy wrote:
Thanks for the code kolusu.Could you please explain the logic used in the sort card ?


I tried to get the logic behind the code and found that the first record forms that base for comparison and also it processes more than 2 records for same CIC & FAC.However,I was just thinking whether below scenario also could be handled by small changes to the code or not -

CIC5 UPC5 FAC5 V5 02/21/2008 11/15/2009
CIC5 UPC6 FAC5 V6 11/16/2009 12/31/2019
CIC5 UPC7 FAC5 V7 12/06/2009 12/31/2019
CIC5 UPC8 FAC5 V8 12/01/2009 12/31/2019

In this above case,when you compare the first 2 records,they dont have any overlapping dates.
But,when the 2nd record is compared to other records,they have overlapping dates.So ideally,we need to get the other 3 records in the output leaving out the first record.

This might happen with only 3rd and 4th records too having the overlapping dates,leaving the first 2 records.All these cases arise when we have more than 2 records with same CIC and FAC.

Could you please throw some light on how to handle these scenarios also in the sort card or do we need to go for a cobol program for this handling?
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Thu Nov 19, 2009 10:07 pm
Reply with quote

maddyinfy,

What happens if you have 10 records and the first 8 do NOT have a overlap but the 9th record has an overlap. How do you handle such case? It is like you need to verify each record with every other record of the same key. If the number of records per key varies then you are better off coding a program.
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 -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts JCL sort card - get first day and las... JCL & VSAM 9
No new posts Sort First/last record of a subset th... DFSORT/ICETOOL 7
Search our Forums:

Back to Top