0

(I am a novice, so forgive me)

For every item in my inventory, there are multiple records indicating the quantity per the size of the container. For example:

RECEIPT NO.    ITEM #   DESCR.   PER UNIT QTY SEQ. NO.
 000001        10001    CRATE       10           3
 000001        10001    PACK        20           2
 000001        10001    EACH         1           1
 000002        10001    BAG          2           3
 000002        10001    PACK        20           2
 000002        10001    EACH         1           1

For Receipt No. 00001 specifically, the purchase of a single Crate of Item 10001 (could be multiple) would have (10x20x1) individual items. The complication is that items have a different maximum Sequence Number value --- some are just 1 level ("each"), and some go up to level 6 and therefore have 6 separate records in the database.

I want to find the total number of each item. In psuedocode, I want do to the following:

@seqcounter = (FIND MAX([SEQ.NO.]), GROUP BY [RECEIPT NO.], [ITEM #], [PER UNIT QTY], [DESCR.])
@totalquantity = 1
WHILE @seqcounter >=2:
UPDATE @totalquantity = [PER UNIT QTY] * @totalquantity AS [TotalQty]
UPDATE @seqcounter = @seqcounter - 1

SELECT [RECEIPT NO.], [ITEM #], [TotalQty] FROM database.

The above SQL code is entirely wrong, which is why I'm here. Another idea is that because every step of the sequence is in order ID-wise, I could maybe do a window operation. I don't even know where to start with that either.

Thanks in advance for your help.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
John Fonte
  • 1
  • 1
  • 1
    Please include your desired results for the sample data. – Stu May 25 '22 at 16:50
  • Could be wrong, but it doesn't seem like you even need window functions, just normal grouping and some clever arithmetic, as all you are doing is multiplying everything together. Eg https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=1f2e865a3c0eedb7e3d9832fb197c696 See also https://stackoverflow.com/questions/5416169/multiplication-aggregate-operator-in-sql So you can do `EXP(SUM(LOG(YourValue)))` – Charlieface May 26 '22 at 08:35

0 Answers0