DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
nandika.kodituwakku



Group memberships:
Premium Members

Joined: 13 Sep 2016
Posts: 4
Location: Australia
Points: 39

Post Posted: Wed Feb 15, 2017 11:16 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
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



Joined: 02 Sep 2015
Posts: 36
Location: Brasilia, Brazil
Points: 371

Post Posted: Thu Feb 16, 2017 4:39 am Reply with quote    Back to top    

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

Kyros Tecnologia- Uberlândia, Brazil
Rate this response:  
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 41848
Location: Denver, CO
Points: 214702

Post Posted: Thu Feb 16, 2017 7:52 am Reply with quote    Back to top    

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

The black queen chants the funeral march, the cracked brass bells will ring
To summon back the fire witch to the court of the crimson king
Rate this response:  
nandika.kodituwakku



Group memberships:
Premium Members

Joined: 13 Sep 2016
Posts: 4
Location: Australia
Points: 39

Post Posted: Thu Feb 16, 2017 4:57 pm Reply with quote    Back to top    

There will be one row for each month

_________________
npk
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 41848
Location: Denver, CO
Points: 214702

Post Posted: Thu Feb 16, 2017 5:53 pm Reply with quote    Back to top    

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

_________________
-craig

The black queen chants the funeral march, the cracked brass bells will ring
To summon back the fire witch to the court of the crimson king
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 53880
Location: Sydney, Australia
Points: 292392

Post Posted: Thu Feb 16, 2017 11:47 pm Reply with quote    Back to top    

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 ...

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
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



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours