Logic required to get the records with overlapping dates

maddyinfy · New User Joined: 24 Nov 2007 Posts: 6 Location: AP

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

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)

A1 - (14,1,PD)

A2 - (15,3,CH)

A3 - (18,2,CH)

Facility - (20,4,CH)

Vendor - (24,9,CH)

Date-Eff - (33,10,CH)

Date-Off - (43,10,CH)

Input File Data :

CIC UPC A1 A2 A3 Fac Ven Date_Eff Date_Off

CIC1 UPC1 1 001 05 FAC1 V1 01/01/2009 12/31/2009
CIC1 UPC1 1 001 05 FAC1 V2 03/03/2009 12/31/2009

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

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

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

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

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

Records of CIC1 have overlapping dates and should come out

Records of CIC2 have overlapping 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

Records of CIC5 ,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.

Similarly,there can be 10 records for same CIC and FAC and the first 8 do NOT have a overlap but the 9th record has an overlap.It is like we need to verify each record with every other record of the same key and and the number of records per key varies.

Here,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 comparison 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.

Expected Output :

CIC UPC A1 A2 A3 Fac Ven Date_Eff Date_Off

CIC1 UPC1 1 001 05 FAC1 V1 01/01/2009 12/31/2009
CIC1 UPC1 1 001 05 FAC1 V2 03/03/2009 12/31/2009

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

CIC5 UPC6 1 001 05 FAC5 V6 11/16/2009 12/31/2019
CIC5 UPC7 1 001 05 FAC5 V7 12/06/2009 12/31/2019
CIC5 UPC8 1 001 05 FAC5 V8 12/01/2009 12/31/2019

I tried this using sort,but couldnt succeed as it requires looping concept.

I guess this can be done in cobol using arrays.Can anyone help me out with the brief logic?

expat · Posted: Wed Nov 25, 2009 3:12 pm

Maybe if you answered the questions that were asked of you in the DFSORT forum then perhaps a solution could be found there, rather than you posting the self same question in the COBOL forum.

maddyinfy · New User Joined: 24 Nov 2007 Posts: 6 Location: AP

Expat,
I have tried to narrow down my requirements and answered the questions in DFSORT forum too.But,with suggestions from the senior members that this logic can be done with cobol program I had to come here.As i have got less time to come up with the logic,I thought I would get the required pointers in this forum from senior folks like you to kick off my work asap.Please excuse me if you feel that I'm repeating the question in different forums.