I have data like this:
+----+-------------------------+----------+----------+
| ID | DateReceived | Quantity | VendorID |
+----+-------------------------+----------+----------+
| 1 | 2010-08-09 06:53:44.783 | 2 | 1 |
| 2 | 2010-08-01 13:31:26.893 | 1 | 1 |
| 3 | 2010-07-26 07:52:29.403 | 2 | 1 |
| 4 | 2011-03-22 13:31:11.000 | 1 | 2 |
| 5 | 2011-03-22 13:31:11.000 | 1 | 2 |
| 6 | 2011-03-22 11:27:01.000 | 1 | 2 |
| 7 | 2011-03-18 09:04:58.000 | 1 | 1 |
| 8 | 2011-12-17 08:21:29.000 | 1 | 3 |
| 9 | 2012-08-10 10:55:20.000 | 9 | 3 |
| 10 | 2012-08-02 20:18:10.000 | 5 | 1 |
| 11 | 2012-07-12 20:44:36.000 | 3 | 1 |
| 12 | 2012-07-05 20:45:29.000 | 1 | 1 |
| 13 | 2013-03-22 13:31:11.000 | 1 | 2 |
| 14 | 2013-03-22 13:31:11.000 | 1 | 2 |
+----+-------------------------+----------+----------+
I want to sort the data by the DateReceived and sum the Quantity. BUT, I want to sum the Quantity grouped by the VendorID as long as they are adjacent like the example output below.
+----------+----------+
| VendorID | Quantity |
+----------+----------+
| 1 | 5 |
| 2 | 3 |
| 1 | 1 |
| 3 | 10 |
| 1 | 9 |
| 2 | 2 |
+----------+----------+
I currently am doing this by loading all rows and going through them in my application code. This is currently a bottleneck in my software that I would like to elimiate.
What is a MS Sql Server query to generate the desired output?
PS. Any suggestions for a better title?
Try this solution:
SELECT z.VendorID, z.GroupID,
MIN(z.DateReceived) AS DateReceivedStart,
MAX(z.DateReceived) AS DateReceivedStop,
SUM(z.Quantity) AS SumOfQuantity
FROM
(
SELECT y.VendorID,
y.RowNum1 - y.RowNum2 AS GroupID,
y.DateReceived,
y.Quantity
FROM
(
SELECT *, ROW_NUMBER() OVER(ORDER BY x.DateReceived) AS RowNum1,
ROW_NUMBER() OVER(ORDER BY x.VendorID, x.DateReceived) AS RowNum2
FROM @MyTable x
) y
) z
GROUP BY z.VendorID, z.GroupID
ORDER BY DateReceivedStart