SQL Algorithms – Is There a Purely SQL Alternative to Looping?

algorithmsloopssql

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?

DATA OVERVIEW:
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.

GOAL:
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.

SUMMARY:

It sounds like SQL Server can do some looping, but that it's impractical.

What's a better way to approach this?

Best Answer

Depending on your version of SQL Server, windowing functions will do what you want. 2008 has limited support but 2012 adds nearly all of the standard. The over clause is used for things exactly like this, it is also available in the express versions as well.

http://technet.microsoft.com/en-us/library/ms189461.aspx

Windowing functions are used to perform high-level aggregation, ranking and statistical analysis. They are often used to split and show similarities in a data set.

The over clause can partition a column and provide a running sum like you describe in a T-SQL Statement.

SELECT [id], [vendor_id], [item], [brand], [inventory_version], SUM([quantity])
    OVER (PARTITION BY [vendor_id], [item]
        ORDER BY [vendor_id], [item], [inventory_version]) AS qty_Totals
FROM inventory_snapshots
ORDER BY [vendor_id], [item], [inventory_version]

Code is untested but should work. What it does is take the recordset, partitions it by the vendor_id and item. This effectively resets the sumation to 0 when that combination changes. We make sure to order the results in a logical manner to show the running totals easily. This will show the progression of item versions per manufacturer starting at the lowest and going straight through to the last.

Hopefully this clears things up a bit.

Related Topic