DSXchange

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

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.

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.

There will be one row for each month

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

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.