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
logic to calculate the missing record
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 5
- Joined: Tue Sep 13, 2016 5:19 pm
- Location: Australia
-
- Participant
- Posts: 44
- Joined: Wed Sep 02, 2015 7:19 am
- Location: Brasilia, Brazil
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.
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
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 5
- Joined: Tue Sep 13, 2016 5:19 pm
- Location: Australia
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.