How to use Tally Table to allocate quantity?

• Bruce Dow

  Ten Centuries

  Points: 1019

  More actions

  June 19, 2019 at 6:11 pm

  #3655393

  I have read many great helpful articles on Tally Tables by Jeff Moden, Lynn Pettis, and many others; however I haven't seen one on how to allocate quantities using Tally Tables.

  Here is how I would do it using Loop (RBAR) logic.  Please give insight into how to turn it into set logic or point me to the article I missed.

  1. Loop through each item with qty on hand
  2. Loop through each item location with qty on hand
  3. Loop through po receipts in descending receipt date for each item
  4. Allocate the specific po#, receipt date and cost and qty to specific item locations until the entire item location qty has a po receipt qty and cost allocated.
  5. If po receipt has additional qty unallocated, assign to next item location.  If item location still has unallocated qty, get the next po receipt.

  Thanks for sharing your wisdom.

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  June 19, 2019 at 6:31 pm

  #3655405

  Nor have I been able to find a tally table solution to fixing my car's engine trouble. They're useful, but not universally so.

  Can you post some sample DDL, INSERT statements and desired results based on the sample data provided?

   

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  June 19, 2019 at 7:49 pm

  #3655450

  Bruce Dow wrote:

  I have read many great helpful articles on Tally Tables by Jeff Moden, Lynn Pettis, and many others; however I haven't seen one on how to allocate quantities using Tally Tables. Here is how I would do it using Loop (RBAR) logic.  Please give insight into how to turn it into set logic or point me to the article I missed.

  1. Loop through each item with qty on hand
  2. Loop through each item location with qty on hand
  3. Loop through po receipts in descending receipt date for each item
  4. Allocate the specific po#, receipt date and cost and qty to specific item locations until the entire item location qty has a po receipt qty and cost allocated.
  5. If po receipt has additional qty unallocated, assign to next item location.  If item location still has unallocated qty, get the next po receipt.

  Thanks for sharing your wisdom.

  That's because a tally table is probably not the best approach to use for this.  From your brief description, it sounds like the FIFO Queues approach I outlined will work better.  As already suggested, please post sample data and expected results.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Jeff Moden

  SSC Guru

  Points: 1004323

  More actions

  June 19, 2019 at 7:56 pm

  #3655453

  I'm pretty sure that a Tally table solution for this wouldn't be the preferred solution.  There is a set based solution.  See the winning entry at the bottom of the article in the following link.  The rest of the article is pretty good at explaining thing.

  https://www.red-gate.com/simple-talk/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)