tricky time interval billing problem

[pruebauno at latinmail.com]

> First of all, you need to use ordering to ensure that the database gives
> you the most convenient order for processing, as this will make your
> computations much easier. So I'd suggest sorting by clientNumber,
> ServiceNumber, Begindate and Enddate. That way you can consider each
> service separately.
>
> Then all you need to do is accumulate the entries where the clientNumber
> and ServiceNumber are the same and the start date of the second is less
> than or equal to the end date of the first, repeatedly until either
> there's no date overlap or a new service and/or client is started.
>
> This shouldn't need any intermediate storage of results: if the row
> you've just read can be used to extend the current range then extend it,
> otherwise emit the current range and replace it with the new row.
>
> Or is there something I've failed to understand about how you want to
> process the data?
>
> regards
>   Steve

Hi Steve,  (btw I am the one that sent you the quotes at Pycon)

Hm, that would be an algorithm for requirement number 2. I do not
understand how it would help with respect to requirement 1. Notice that
by coincidence, in my examples the input data is already sorted as you
specified.

The real data is of course more messy than my analogy and I will post
it here so that nobody can accuse me of "feature creep". I hope I don't
totally confuse you now. Feel free to ignore it. The real output is not
100% correct either (that is why I am rewriting the program). Some of
the database in all its gory, ..eh glory:

INPUT
43756352|D|01/01/1999|09/30/2003|DCUD2B00|DCUD2B00|Y|A|43756350|D|83516
  |00374    |9048327561|0001|
43756353|D|01/01/1999|09/30/2003|DCUD2B00|DCUD2B00|Y|A|43756350|D|83516
  |00374    |9048327561|0001|
43756351|D|01/01/1999|09/02/2002|DCUD2B00|DCUD2B00|Y|A|43756350|M|83516
  |00374    |9048327561|0001|
43756354|D|04/02/1999|09/30/2003|DCUD2B00|DCUD2B00|Y|A|43756350|W|83516
  |00374    |9048327561|0001|
43756351|M|01/01/1999|03/31/1999|MARTPPG2|MARTPPG2|Y|A|43756350|M|83516
  |00374    |9048327561|0001|
43756352|M|01/01/1999|03/31/1999|MARTPPG2|MARTPPG2|Y|A|43756350|D|83516
  |00374    |9048327561|0001|
43756353|M|01/01/1999|03/31/1999|MARTPPG2|MARTPPG2|Y|A|43756350|D|83516
  |00374    |9048327561|0001|
43756351|M|04/01/1999|07/31/2002|MBCPG002|MBCPG002|Y|A|43756350|M|83516
  |00374    |9048327561|0001|
43756352|M|04/01/1999|07/31/2002|MBCPG002|MBCPG002|Y|A|43756350|D|83516
  |00374    |9048327561|0001|
43756353|M|04/01/1999|07/31/2002|MBCPG002|MBCPG002|Y|A|43756350|D|83516
  |00374    |9048327561|0001|
43756354|M|04/02/1999|07/31/2002|MBCPG002|MBCPG002|Y|A|43756350|W|83516
  |00374    |9048327561|0001|
43756352|M|08/01/2002|09/30/2003|MBP07305|MBP07305|Y|A|43756350|D|83516
  |00374    |9048327561|0001|
43756353|M|08/01/2002|09/30/2003|MBP07305|MBP07305|Y|A|43756350|D|83516
  |00374    |9048327561|0001|
43756354|M|08/01/2002|09/30/2003|MBP07305|MBP07305|Y|A|43756350|W|83516
  |00374    |9048327561|0001|
43756351|M|08/01/2002|09/02/2002|MBP07305|MBP07305|Y|A|43756350|M|83516
  |00374    |9048327561|0001|

OUTPUT
43756350|9048327561|DCUD2B00|D|A|01/01/1999|04/01/1999|83516   |00374
 |0001|Y|A|DCUD2B00|
43756350|9048327561|DCUD2B00|D|E|04/02/1999|09/30/2003|83516   |00374
 |0001|Y|A|DCUD2B00|
43756350|9048327561|MARTPPG2|M|A|01/01/1999|03/31/1999|83516   |00374
 |0001|Y|A|MARTPPG2|
43756350|9048327561|MBCPG002|M|A|04/01/1999|07/31/2002|83516   |00374
 |0001|Y|A|MBCPG002|
43756350|9048327561|MBP07305|M|A|08/01/2002|09/02/2002|83516   |00374
 |0001|Y|A|MBP07305|
43756350|9048327561|MBP07305|M|E|09/03/2002|09/30/2003|83516   |00374
 |0001|Y|A|MBP07305|

CHEAT SHEET:
|   (M)  |  (H,W)  |  (D,O,S)  ||
+==============================
|   -    |    -    |     -     ||    O    |
+-----------------------------------------+
|   -    |    -    |     X     ||    G    |
+-----------------------------------------+
|   -    |    X    |     -     ||    F    |
+-----------------------------------------+
|   -    |    X    |     X     ||    E    |
+-----------------------------------------+
|   X    |    -    |     -     ||    C    |
+-----------------------------------------+
|   X    |    -    |     X     ||    D    |
+-----------------------------------------+
|   X    |    X    |     -     ||    B    |
+-----------------------------------------+
|   X    |    X    |     X     ||    A    |
+-----------------------------------------+

regards,
Nes