Toad World® Forums

Possible loop needed


#1

Hi all! I am new to the forum and new to Toad Data Point. I have written a query that I am having trouble finishing. My query identifies overstocked item/locations and understocked item/locations and matches them up together in order to send merchandise from one location to another. The issue I am having is figuring out some type of loop process to determine which stores to send the items to. I need my query to first look at stores within the same district, then move onto stores within the same state, then move onto stores within the same region. For the stores in the same region I want it to order by who can take the most units. All while adding the quantity of the receiving stores as it goes until it fulfills all of the quantity from the sending store.

Example: Item 123 at Store A has 156 units it needs to transfer out. Store A is in district 307, in Virginia, in region 8796. Below are the results for the stores than need item 123.

I need the query to result that store ‘I’ can take the merchandise first at 24 units (same district). Then ‘G’ at 12 units, then ‘H’ at 12 units, then ‘J’ at 6 units (same state). Then ‘K’ at 40 units, then ‘B’ at 35 units, lastly at ‘C’ for 27 units (to sum to the total of 156 units).

STORE
DISTRICT
STATE
REGION
QTY
B
206
MD
8796
35
C
1338
NY
8796
30
D
206
MD
8796
18
E
927
FL
8796
6
F
1675
ME
8796
6
G
330
VA
8796
12
H
311
VA
8796
12
I
307
VA
8796
24
J
311
VA
8796
6
K
206
MD
8796
40
I have been wracking my brain and looking at examples and cannot figure out where to start? Multiple case statements? Separate queries that create an order?

Any help will be greatly appreciated.


#2

Couple of options. Your logic likely will involve several passes through the data, depending on how you implement. You don’t mention which data source(s) your data resides on, but it would be best to do this using the database’s own procedural language. That’s what I would recommend before any thing else.

That said, you may want to look at combining Toad DataPoint’s Automation Engine and Transform & Cleanse capabilities. Using T&C, you would be able to add on a “Group ID” to your result set, and then introduce multiple rules to assign a “group number or tag” to each record. That tag would indicate the order in which you process the units transfer.

For example, here’s a rough example of steps that you could build in a Toad Datapoint automation script:

  1. Create the records indicating Overstocked Items (call it the Overstock Master).
  2. For EACH Overstock record’s Item (Automation task library has a Loop Iterator that can be used)
    a. Create the results for all Stores that need that item# (NeedItem Master)
    b. Apply Transform & Cleanse rules to assign a group “order number” column to the NeedItem Master , possibly as follows:
    CASE
    When Overstock District same as NeedItem District, then
    order number= ‘1st’
    When Overstock State same as NeedItem State, then
    order number= ‘2nd’
    When Overstock Region same as NeedItem Region, then
    order number= ‘3rd’
    ELSE order number= ‘99th’
    c. Sort the NeedItem Master by the Order Number field and
    (1) process the 1st records
    (2) If Item still needed, process the 2nd records
    (3) If item still needed, process the 3rd records, etc.
    Until item fulfilled