Historically, I probably would have written a script (php or something) to loop through all spreadsheet rows and perform some calculations.
I'm looking for a pure SQL solution on the presumption that SQL has better options than loops.
How should I approach this problem while using a pure SQL approach?
We have a list (currently in a spreadsheet) of items for which we want to check trending.
Let's pretend that that spreadsheet contains these three items; 111111, 222222, and 888888.
In the DB, we have a table of vendors' inventory snapshots. Here's a simplified example:
(see the fiddle here).
CREATE TABLE inventory_snapshots ([id] int, [vendor_id] int, [item] int, [brand] varchar(1), [inventory_version] int, [quantity] int); INSERT INTO inventory_snapshots ([id], [vendor_id], [item], [brand], [inventory_version], [quantity]) VALUES (1, 111, 111111, 'A', 5, 45), (2, 111, 111111, 'B', 5, 43), (3, 111, 222222, 'X', 5, 12), (4, 222, 111111, 'B', 4, 5), (5, 222, 888888, 'C', 4, 50), (6, 222, 111111, 'D', 5, 10), (7, 222, 888888, 'C', 5, 100), (8, 111, 111111, 'A', 6, 40), (9, 111, 222222, 'X', 6, 10);
Notice that a vendor can have multiple rows of an item in a given inventory version because of brand differences.
Our goal is to see the trending over all vendors.
That means comparing a list (currently in spreadsheet form) of items against the DB to retrieve:
- item number
- starting qty (sum of qty's from the MIN version number for each
- vendor, items grouped by vendor_id,inventory_version) total increases (sum of each vendors version-over-version increases in qty) total
- increases (sum of each vendors version-over-version increases in qty)
- ending qty (sum of qty's from the MAX version number for each vendor, items grouped by vendor_id,inventory_version)
For another example, see the comment in the fiddle.
It sounds like SQL Server can do some looping, but that it's impractical.
What's a better way to approach this?