1. Registered User
Join Date
Sep 2005
Posts
220

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. Registered User
Join Date
Jan 2005
Location
Green Bay
Posts
201

3. Registered User
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. Registered User
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. Registered User
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. Registered User
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. Registered User
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. Registered User
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. Registered User
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. :-)
Join Date
Jun 2003
Location
Posts
5,516
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. Registered User
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. Registered User
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
•