Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2011
    Posts
    5

    Red face Unanswered: Need help for optimizing a spezial sql

    Within a table I do have a lot of records which do have the same content for one Id. The only diffence is that a date-field and an amount field for every record could differ, the amount not realy but the date. Perhaps I have 61 records for one ID, two different amounts but several dates, it's a payment schedule. Problem is that I want to compress this table in only 1 to 6 records per ID.
    One record is not a problem, 2 records also is no Problem but the problem became strange when the first payment date will be a 29th of march and the customer has to pay 60 insatllments. in Someyears there will be no 29 th of februrary so the compressed schedule has to hold a leapyear schedule as single payments. How could that be realized having in one table 61 records with all payments at every payment date, and want to have an other table holding only x records where every startdate of schedule is defined by the date following the leapyear date, with the original startday as day.
    example:
    1st payment on 20110729 with xy following payments
    one payment on 20130228
    next round starting on 20130329 with an other xx payments
    then again one payment on 20140228
    and next paymentround starting 20140329.
    all this dates could be found in the original table, but to save space it is necessary to compress to a simple schedule.
    Is there anybody out here who could give me a solution.
    Loops are realy time consuming, because a customer could have several Products within the same payment schedule.
    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Need help for optimizing a spezial sql

    but you do NOT provide tables, data, or existing SQL,
    so why do you expect any assistance?
    Let me paraphrase your post below

    My car won't go
    Tell me how to make my car go.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2011
    Posts
    5

    Quit simple answer

    I'm not able to provide a table nor a SQl, it's a quit theoretic question Where I want to have a solution for.
    So I want to describe the Problem again withe an example.
    Customer X: ID: XYZ
    Customer Z: ID: ZYX
    both habe a Credit with a different Schedule, a different payment amount, different paymnent dates and number of payment. So far so good.
    The table now looks like this
    ID; paydate; payamount,max payments; mumber of payment;
    XYZ;20110815;200,00;36;1,
    XYZ;20110915;200,00;36,2,
    XYZ;20111015;200,00;36;3,
    XYZ;..............................,
    XYZ;20140815;200,00;36;36,
    second Customer
    ZYX;20110130;350,50;60;1,
    ZYX;20110228;350,50;60;2, Changhe in date
    ZYX;20110330;350,50;60;3,
    ZYX;20110430;350,50;60;4,
    ZYXX;.............................,
    ZYX;20120130;350,50;60;12,
    ZYX;20120229;350,50;60;13,Change in date leap year
    ZYX;20120330;350,50;60;14,
    ZYX................................,
    ZYX;20160130;350,50;60;60,

    So far understandable.

    As you can imagine, the table will be quite juge if there are a lot of customers with different values. My intend is. to compress this table to only 2 to 13 records per customer, which should lokk like this.
    For customer one quit simple:
    XYZ;20110815;200,00;36;36, this means customer has 36 paymenst with 200,00 bucks and a start of payment date on the 15.08.2011

    For Customer two it looks a bit different because there are a couple of februrars with 28 days and with 29 days (leap year), because there is no 30.02.2012

    so it schould look like this:
    ZYX;20110130;350,50;60;1, starting payment on 30.01.2011
    ZYX;20110228;350,50;60;1, Change in date
    ZYX;20110130;350,50;60;11, 11 payments with a starting date on 30.03.2011
    ZYX;20120229;350,50;60;1, Change in date
    ZYX;20110130;350,50;60;11, 11 payments with a starting date on 30.03.2012
    ZYX;20130228;350,50;60;1, Change in date
    and so on till all payments are distributed.

    This is a so called compresssed payment schedule,
    and this will reduce the size of a table quite a lot.

    I hope this will become clearer know.

    It's just as your example with a car.

    Someone brought me his car to check why it was not runing,
    I tried all to solve it but did not find a solution, so I went to a specialist told him my problem and was hoping to get a solution

  4. #4
    Join Date
    Oct 2011
    Posts
    5

    Question Is ther nobody outside

    Hi all is there realy nobody outside who could help me

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> select add_months(to_date('20110130','YYYYMMDD'),1) from dual;
    
    ADD_MONTHS(TO_DATE(
    -------------------
    2011-02-28 00:00:00
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Oct 2011
    Posts
    5

    This is not what I'm expectig

    The date ist not where ther is a need to change, This value is known as I show it in the post 3.

    Again.

    There is a table having a lot of records for a lot of different customers. This table looks like the first part in post 3.

    This table I want to compress to the contents in the second part of post 3. This means.
    a payment schedule starts on 15th of January, and there will be 60 payments.
    In the source table there will be 60 records for each payment with just the same value in every record except the date. Record 1 will show the starting date record 2 will show the next payment date this will be the one with one month ahead. And so on.
    for this customer it is no problem to create a single record entry in a new table. This record will than schow the starting date of the first record the count of payments, payment amount and saome other values.
    So this is not the problem.

    So the problem occurs if a customer is paying on the 30 of a month, not the 31 of a month. For some month it is the last day of month for others it's the day before the last day of month. The customer signed the contract for the payment to start on the 30 of a month. Now there are also 28 th and 29 th of February which are n both caases the last day of month.

    In this case I need a sql which will present the result of the second part of Post 3.
    How could that be solved.

    It did not have bearing on the addition of a month to a date.

    Regards

  7. #7
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    Quote Originally Posted by bowie1304 View Post
    So the problem occurs if a customer is paying on the 30 of a month, not the 31 of a month. For some month it is the last day of month for others it's the day before the last day of month. The customer signed the contract for the payment to start on the 30 of a month. Now there are also 28 th and 29 th of February which are n both caases the last day of month.

    In this case I need a sql which will present the result of the second part of Post 3.
    the add_months function does the 28th/29th/last day of month calculations for you

    Don't keep changing the date and storing the new date; store the first payment date and use date arithmetic to calculate the due dates/last pymt date as needed

    In real situation you'd not design it this way, there would be master and detail data, transaction details, etc.

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by bowie1304 View Post
    The date ist not where ther is a need to change, This value is known as I show it in the post 3.
    Is this not contradicting with this part:
    Quote Originally Posted by bowie1304 View Post
    So the problem occurs if a customer is paying on the 30 of a month, not the 31 of a month. For some month it is the last day of month for others it's the day before the last day of month. The customer signed the contract for the payment to start on the 30 of a month. Now there are also 28 th and 29 th of February which are n both caases the last day of month.
    Looks to me, that calculation of that date is problem. Moving it to the data generation removed it from calculation phase, however the problem magically did not disappear - it is now only moved to data generation.

    As Indy_tomcat suggested you, this design is not a good idea at all. You should store each payment schedule only once - this will be the best "compression". If you would like to store details about each payment (e.g. status, type, counter-account, overdue, ...), you would probably place it into different table with N:1 relationship to the schedule table.
    Quote Originally Posted by bowie1304 View Post
    In this case I need a sql which will present the result of the second part of Post 3.
    How could that be solved.

    It did not have bearing on the addition of a month to a date.
    I do not understand your last sentence. Anyway, you may use simple date arithmetic plus your brain for determining the payment date calculation rules.
    E.g. when day of the payment (e.g. 30th) is greater than the add_months result, use that day, otherwise use add_months result.
    E.g. directly use least day from day of the payment and last day of payment month.
    Probably there are another possible ways too.
    Code:
    with month_no as ( select level shift from dual connect by level <= 15 ),
         sample_data as ( select to_date('20101130','YYYYMMDD') start_date,
                                 extract ( day from to_date('20101130','YYYYMMDD') ) start_day
                          from dual )
    select add_months(start_date, shift) add_months,
           case when extract ( day from add_months(start_date, shift) ) > start_day
                then trunc( add_months(start_date, shift), 'MM' ) + start_day -1
                else add_months(start_date, shift)
           end approach1,
           trunc( add_months(start_date, shift), 'MM' ) +
             least( start_day, extract ( day from add_months(start_date, shift) ) ) -1 approach2
    from sample_data, month_no;

  9. #9
    Join Date
    Oct 2011
    Posts
    5

    Perhaps you are right

    I thought about your Idea and it seems as if a new calculation of the schedule instead of using the data from the send file should be the best solution.
    as you can imagine, by now we have more than 9 million records in one file for thousands of customers. To compress them leads to a table with about 295000 records, which shows how much space there could be saved if we have a compressed schedule file.

    But our routine needs about 3 hours to do the job and that's much to long, there must be a way to speed this up and the idea to rebuild the schedule could be the fastest way.

    The idea with rebuilding the schedule looks for a first step fine, but there was an other probleme which is solved, because the payment is alo a prameter which will lead to a new Schedule sequence with new startingdate. But this is solved so far.
    The provider of the datafiles is not able to send a compressed schedule so it is our job to do so.

    Thanks for the help
    Last edited by bowie1304; 11-07-11 at 04:03. Reason: addition, correction

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >so it is our job to do so.
    or not to do.
    Storing computed values is generally a BAD idea
    & in this case it has been taken to an extreme by storing many multiple & unneeded rows.
    You can calculate any due date at any time based upon original date.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •