> 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 -- http://mail.python.org/mailman/listinfo/python-list
