Creating buckets from a table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Creating buckets from a table

  1. #1
    Join Date
    Oct 2000
    Posts
    9
    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

  2. #2
    Join Date
    Sep 2000
    Posts
    17
    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.

Posting Permissions

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


Click Here to Expand Forum to Full Width