logic to calculate the missing record

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
nandika.kodituwakku
Premium Member
Premium Member
Posts: 5
Joined: Tue Sep 13, 2016 5:19 pm
Location: Australia

logic to calculate the missing record

Post by nandika.kodituwakku »

Hi All,

Can you please help me with the logic for the following requirement

INPUT:

ID | MM Number | From | To
12345|4444444|2015-01-03|2015-02-02
12346|4444444|2015-02-03|2015-03-02

<< missing one record ( ex same MM Number no record for period from 2015-03-03 to 2015-04-02

12347|4444444|2015-04-03|2015-05-01
12348|4444444|2015-05-02|2015-06-01
12349|4444444|2015-06-02|2015-07-01
12310|4444444|2015-07-02|2015-07-31

<< missing one record (ex: same MM Number no record for period from 2015-08-01 to 2015-08-31

12310|4444444|2015-09-01|2015-09-30
12310|4444444|2015-10-01|2015-10-31

Out put:
MM No | Missing Start Date | Missing To Date | Missing Days
4444444 |2015-03-03 |2015-04-02|30
4444444 |2015-08-01 |2015-08-31|30
npk
leandrohmvieira
Participant
Posts: 44
Joined: Wed Sep 02, 2015 7:19 am
Location: Brasilia, Brazil

Post by leandrohmvieira »

There will be 1 row for each month? Or 1 row for each 3 first days of each month? If it does not fit in one of these scenarios i dont know how to guess the day number from a missing record.
Leandro Vieira

Data Expert - Brasilia, Brazil
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I don't have time for the searching this morning but answers on the subject of how to find gaps in dates / date ranges have been posted here before. Hopefully someone can dig them up, or I can try later.

Edited to add: An exact search for "between two dates" returns 64 results, something in there should be useful.
-craig

"You can never have too many knives" -- Logan Nine Fingers
nandika.kodituwakku
Premium Member
Premium Member
Posts: 5
Joined: Tue Sep 13, 2016 5:19 pm
Location: Australia

Post by nandika.kodituwakku »

There will be one row for each month
npk
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

With 'month' being a relative term, it would seem.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Use stage variables to remember the start and end dates from the month in the previous row (with sorted data). If there will never be more than one contiguous missing row, you're done.

If there might be more than one, then you need to compare your dates against a complete calendar.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply