-
I have an Orders table that has the following fields:
Item
Location
OrderQty
DateDue
What I want to do is take all orders within a time range starting at Date A and ending with Date B and put the Order Requirements into Buckets by week showing the total orderQty Like so:
Item Location 10/20/00 10/27/00 11/3/00 X
------ ---------- ---------- ----------- --------- -----
10 A 50 25 5 10
10 B 75 30 0 5
12 A 123 90 50 75
and so forth....
What I need to do is show 8-12 weeks (to be determined at a later date) of buckets and then an X bucket to show orders out in the future beyond the 8-12 week span. Does anybody have any suggestions on how to do this?
I will be using Access 97 as the front end for the program needing this information. I already have skeleton passthrough queries working to "simulate" the data. So ideally I'd like to accomplish this task with one passthrough SQL statement, or if need be use VBA to aid in the process.
Thank you...
I will be showing 8-12 buckets (weeks) of inidivuals
-
U can use a decode statement like
select item,
location,
sum(decode(greatest(d2,d1),d2,order_qty,0) "bet_d2_d1
sum(decode(greatest(d3,d2),d3,order_qty,0) "bet_d3_d2
---
from order;
For performance consideration sign function may be properly
used instead of greatest.
Hope it helps u.
