Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: simple query help

    I have a table as such:

    Code:
    item     loc      date       qty
    1         A      9/1/06      100
    1         A     10/1/06      200
    I need to return a qty that is equal to 1.5 months. In this case 100 + 200/2 = 200. I'm using the add_months function but I can't figure out how to add the 1/2 month.

  2. #2
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    What is your question

    Add 1/2 month to what?

  3. #3
    Join Date
    Sep 2005
    Posts
    220
    I need to get 1.5 months of the qty value.

    month1 qty = 100
    month2 qty = 200

    I want to return month1 qty + 1/2 of month2 qty, or in this example, 200 (100 + 200/2)

  4. #4
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122

    still unclear

    Please tell us, in detail, your problem statement. What are you trying to determine? What will this value be used for?

  5. #5
    Join Date
    Sep 2005
    Posts
    220
    I'm not sure how else to put this. I need the query to return a single row with the qty equal to the qty for date = 9/1/06 plus 1/2 of the qty from the next month (10/1/06). I am basically trying to get 45 days worth of quantity.

    Code:
    Item         Loc         Qty
      1           A          200

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Code:
    SQL> select * from t;
    
          ITEM L D                 QTY
    ---------- - ---------- ----------
             1 A 01/09/2006        100
             1 A 01/10/2006        200
    
    SQL>
    SQL> select t.*,
      2         qty + ( ( sum( qty ) over( order by d
      3                                    range between current row
      4                                              and add_months( d, 1.5 ) - d following ) - qty ) / 2 ) x
      5    from t
      6   order by d;
    
          ITEM L D                 QTY          X
    ---------- - ---------- ---------- ----------
             1 A 01/09/2006        100        200
             1 A 01/10/2006        200        200
    
    SQL> insert into t
      2  select 1, 'A',
      3         trunc( sysdate, 'YY' ) + trunc( dbms_random.value * 365 ),
      4         trunc( dbms_random.value( 100, 500 ) )
      5    from all_objects
      6   where rownum <= 10;
    
    10 rows created.
    
    SQL> select t.*,
      2         qty + ( ( sum( qty ) over( order by d
      3                                    range between current row
      4                                              and add_months( d, 1.5 ) - d following ) - qty ) / 2 ) x
      5    from t
      6   order by d;
    
          ITEM L D                 QTY          X
    ---------- - ---------- ---------- ----------
             1 A 09/01/2006        374        462
             1 A 11/01/2006        176        176
             1 A 02/04/2006        448        448
             1 A 04/05/2006        256      854.5
             1 A 13/05/2006        489        843
             1 A 15/05/2006        471      589.5
             1 A 24/05/2006        237        237
             1 A 10/08/2006        483        694
             1 A 22/08/2006        322        372
             1 A 01/09/2006        100        200
             1 A 01/10/2006        200      379.5
             1 A 19/10/2006        359        359
    
    12 rows selected.
    
    SQL>

  7. #7
    Join Date
    Sep 2005
    Posts
    220
    Now we are very close. I just need the qty value for the next month and the one after that. If today is 9/1/06, I need 10/1/06 + half of 11/1/06's quantity.

  8. #8
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    So, if today is 9/1/06 you want the sum of the set of values from next month's qtys plus half of the sum of the set of values of two months from today's qtys ?

    You have given incomplete data, making the question even harder to answer.

  9. #9
    Join Date
    Sep 2005
    Posts
    220
    Yes, that is correct.

    If today is 9/1/06, I need 10/1/06 + half of 11/1/06's quantity

    If today is 10/1/06, I need 11/1/06 + half of 12/1/06's quantity

    and so on...

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    ...
    sum(
      case 
        extract (month from date_column) - extract (month from sysdate)
        when 1 then qty
        when 2 then qty/2
        else 
      end 
    ) as qty
    ...

  11. #11
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    You are making the assumption that the quantity is a constant slope.

    Saying that the quantity of 9/1/06 = 100
    and the quatity on 10/1/06 = 200
    that the qauntity on 9/15 or 16 / 06 = 150

    if this is the need you need to determine the middle of the month

    something like this
    select sysdate, trunc(last_day('1-dec-2006')+ to_number(to_char(last_day('1-dec-2006'), 'DD')) /2) from dual

    then you need to determine what month this is for
    select date, quantity, quantity / 2 from table

    use these results to jon together and get the sum of the grouping of dates

  12. #12
    Join Date
    Sep 2005
    Posts
    220
    OK, maybe I'm not explaining myself. I don't think this is as hard as it appears. I have a table that has rows with say 12 monthly quantity buckets as such:

    Code:
    JAN  FEB  MAR  APR  MAY  JUN  JUL  AUG  SEP  OCT  NOV  DEC 
    100  300  500  100  600  200  500  100  500  100  800  200
    I need a way to return a value that is equal to 1.5 months worth of a quantity depending on the current date. For example. If today is 4-10-06, take the next month's (May) value of 600 + 1/2 of June's 200 for a total of $700 (600+100). If today is 7-4-06, take next month's 100 + 1/2 of Sept's 500 for a total of $350 (100+250). Is this clearer now? Is this really as difficult as everyone is making it sound?

Posting Permissions

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