1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Please solve that and help me SQL

Discussion in 'Installation - Windows' started by shanto150, Feb 9, 2015.

  1. shanto150

    shanto150 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    I have 2 table that table1 and Table2, I wants bellow's resutl how can I do this plz help me plz
    Table1
    From date To Date
    1/2/2015 15/2/2015

    Table2
    Slab Days
    1 4
    2 5
    3 5

    Result
    start dt end date
    1/2/2015 4/2/2015 (note-given 4days in slab1-table2)
    5/2/2015 9/2/2015 (note-given 5days in slab2-table2)
    10/2/2015 14/2/2015 (note-given 5days in slab3-table2)
    15/2/2015 15/2/2015 rest of the date will another slab
     

    Attached Files:

    • ss.jpg
      ss.jpg
      File size:
      42.5 KB
      Views:
      17
  2. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Hello

    Below query will give the expected output.

    SELECT c.from_date + (SELECT NVL (SUM (days), 0)
    FROM table2 b
    WHERE b.slab < a.slab) start_date
    , c.from_date + (SELECT NVL (SUM (days), 0)
    FROM table2 b
    WHERE b.slab <= a.slab) - 1 end_date
    FROM table1 c,
    table2 a
    UNION ALL
    SELECT d.from_date + (SELECT NVL (SUM (e.days), 0)
    FROM table2 e) start_date
    ,d.to_date end_date
    FROM table1 d
     
  3. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    As there is no join between those 2 tables, need to verify for multiple slabs or if the table1 consists more records .. I believe with small modification for this logic it will work for different scenarios...
     
  4. shanto150

    shanto150 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Thank you, your solution is okay but i've made a change in question
    Test Case:
    Code (SQL):
     CREATE TABLE detention_charge_slot
                (slot_no NUMBER(5),
                from_days NUMBER(10),
                to_days NUMBER(10),
                charge_amount NUMBER(10,2));
    Code (SQL):
     INSERT INTO detention_charge_slot
                VALUES (1,1,4,0);
               
                INSERT INTO detention_charge_slot
                VALUES (2,5,9,10);
               
                INSERT INTO detention_charge_slot
                VALUES (3,10,14,20);
               
                INSERT INTO detention_charge_slot
                VALUES (4,15,999,25);

        CREATE TABLE detention_invoice
                (invoice_no NUMBER(10),
                invoice_dt DATE,
                delivery_dt DATE);
        INSERT INTO detention_invoice
                VALUES(1,'10-JAN-2015','25-JAN-2015');
    Expected result for invoice_no=1