0

Hello and thanks in advance for any help, I work in the Oracle SQL environment.

My problem is specified in this picture:

image

Input:

I have date intervals belonging for a customer which never go beyond the scope of one month. I need to connect those intervals where the end of first interval is the end of a month and the next interval starts the first day of the next month (that is the only way they connect).

In the column "days between" it is already obvious, that when there is a start of a new interval the number is higher than 1 and when the current row belongs to an interval there is 1.

Some specifics:

  • For 1 user there can be more than two intervals, in the example there is one user with three intervals
  • The intervals can be several months long = occupying several rows.
  • The spacing between intervals can be also several months long.

Output:

Specific example of the desirable output is in the attached picture. Simply - I want for each row to be specified when does the whole interval, in which the row interval is included, start and end.

Data:
ID  Start date  End date    Last start date Last end date   Days between
12  21.03.2016  31.03.2016                                  1
12  01.04.2016  04.04.2016  21.03.2016      31.03.2016      1
12  28.04.2016  30.04.2016  01.04.2016      04.04.2016      24
12  01.05.2016  20.05.2016  28.04.2016      30.04.2016      1
12  06.06.2016  30.06.2016  01.05.2016      20.05.2016      17
12  01.07.2016  13.07.2016  06.06.2016      30.06.2016      1
Emil Holub
  • 168
  • 10
  • 1
    You should at least include some sample data in your actual question. This sounds like a gaps and islands problem, which would require substantial effort to solve. Have you tried anything yet? – Tim Biegeleisen Sep 08 '16 at 09:03
  • I am sorry I am new to posting to this site - where can I upload some excel or something? This is the last part of a big problem I am solving. I will try to work something out in the meantime, I was curious if someone solved something similar. – Emil Holub Sep 08 '16 at 09:06
  • 2
    Include _text_ in your actual question. Add four spaces to the start of each line. – Tim Biegeleisen Sep 08 '16 at 09:06
  • I am sorry, the four spaces did not indent the text, could you help me? Thank you – Emil Holub Sep 08 '16 at 09:16
  • 1
    " This sounds like a gaps and islands problem, which would require substantial effort to solve." Thank you very much for the name of the problem. I think I should be able to program this from this example: http://stackoverflow.com/questions/38979745/complex-gaps-and-islands-issue/38986476#38986476 if I am succesful I will mark this as comepleted. Thank you. – Emil Holub Sep 08 '16 at 09:22

1 Answers1

0

Thank you Tim-biegeleisen for the problem name again, from there it was so easy! I will definitely remember this solution in the future.

I solved the problem in two easy steps.

First use a dummy variable for those intervals, where there is a gap:

    case when days_between=1 then 0 else 1 end as gap

Then we distribute the intervals according the gaps

    sum(gap) over (partition by ID order by start_date)

From that we just group by ID and the new variable and we are done.

Emil Holub
  • 168
  • 10