1

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?

  • 2
    SQL is all about looping. Almost everything that happens in SQL is a loop over some set operation. It seems like there's a good question trying to break out somewhere in here. Can you make your problem more specific and concrete? – Robert Harvey Apr 8 '14 at 21:58
  • @RobertHarvey I hope you mean looping internally versus looping using cursors, etc., right? – D Stanley Apr 8 '14 at 22:04
  • @DStanley: I'm saying in my own little obtuse way that, if you can't do it using ordinary SQL and set theory, you probably should just write some code in your favorite programming language. – Robert Harvey Apr 8 '14 at 22:06
  • I think that, as I wrote, what started out as a "theory" question on how to approach getting results for some obscure list of items without looping, began to morph into an actual programming question (possibly more suited to stackoverflow ??). Maybe after thinking on it a little longer I'll find a way to refine it one direction or the other. – mOrloff Apr 8 '14 at 22:33
  • Ultimately, I'd really prefer to solve this in SQL because 1) remote access to the DB is locked down on this production server, 2) I can RDP into the server and run SQL directly, 3) it's a huge DB in a SQL flavor I don't currently have locally, so making a copy and getting it to my dev machine would be a real PITA, and 4) I'd be willing to bet there are some really cool SQL trick I can learn through this. – mOrloff Apr 8 '14 at 22:35
8

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.

  • This is more than a little vague. A code sample would really dress up your answer. – Robert Harvey Apr 8 '14 at 22:39
  • I'll add an example in a bit and expand. Not a fan of typing code on the phone. – Britchie Apr 8 '14 at 22:43
  • @Britchie Wow! Some of that is completely new to me. Thanks-a-bunch! It's always fun to learn new stuff. I'll have a chance to test it late tonight or tomorrow. In the mean time, I'll mark it as the answer. – mOrloff Apr 9 '14 at 13:45
  • A minor correction -- if you want a running value, you want lead or lag, not sum; sum would be for a group and you get the same values for all members of the group/partition. Lead and lag are the values from the previous/next row. – jmoreno Apr 9 '14 at 15:30
  • @jmoreno Nice! Thanks. If you find the time, it's always nice to see an example (for me and for future readers). – mOrloff Apr 9 '14 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.