SQL Query to calculate first n customers based on quantity conditions

Question

I have a question but I am not sure how to ask.

Here is my example.

Product Customer TxDate       Qty
Apple   Peter    2013/02/02   3
Apple   Edward   2013/02/03   5
Apple   Sally    2013/02/06   3
Apple   Emily    2013/02/08   6
Orange  Ray      2013/02/03   5
Orange  Simon    2013/02/04   4
Orange  Billy    2013/02/05   5
Orange  David    2013/02/06   2

I want to know the whose have purchased first 10 (variable in each product) items and I wish the query could return the following results:

Apple   Peter    2013/02/02   3
Apple   Edward   2013/02/03   5
Apple   Sally    2013/02/06   2
Orange  Ray      2013/02/03   5
Orange  Simon    2013/02/04   4
Orange  Billy    2013/02/05   1

I am not familiar with T-SQL therefore any help would be appreciated.

Thanks a lot.

This is essentially selection based on a running total - you can find several ways of calculating running totals in SQLServer in the answers to this question: http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver — , May 15 '13 at 07:57

Aleksandr Fedorenko · Answer 1 · 2013-05-15T10:50:03.893

Use option with APPLY operator and correlated-subquery

 ;WITH cte AS
 (    
  SELECT Product, Customer, TxDate, 
         ISNULL(NewQty, 10 - MAX(totalQty) OVER(PARTITION BY Product)) AS Qty,    
         MAX(total) OVER(PARTITION BY Product) AS IsMatch
  FROM dbo.test117 t
    CROSS APPLY(
                SELECT CASE WHEN SUM(t2.Qty) > 10 THEN NULL ELSE t.Qty END,
                       CASE WHEN SUM(t2.Qty) > 10 THEN NULL ELSE SUM(t2.Qty) END,
                       SUM(t2.Qty)
                FROM dbo.test117 t2 --WITH(FORCESEEK)                                                                 
                WHERE t.Product = t2.Product
                  AND t.TxDate >= t2.TxDate
                HAVING SUM(t2.Qty) - t.Qty < 10                                 
                ) o(NewQty, totalQty, total)
  )
  SELECT Product, Customer, TxDate, Qty
  FROM cte
  WHERE IsMatch >= 10

Demo on SQLFiddle

In order to improve performance use index:

CREATE INDEX x ON dbo.test117(Product) INCLUDE(Customer, TxDate, Qty)

score 0 · Answer 2 · answered May 15 '13 at 07:57

The first thing is to remove all customers that are not relevant, so I'm summing up the quantities per product, and remove all customers after the one that exceeded the quantity of 10.

The second problem is to reduce the quantity of the last relevant customer to get a total of 10. I find this customer with the CASE (SumQty > 10), and reduce the quantity if necessary.

This query should work (example on SQL Fiddle):

SELECT Product, Customer, TxDate,
       CASE WHEN SumQty > 10 THEN Qty-SumQty+10 ELSE Qty END AS Qty
FROM (
  SELECT Product, Customer, TxDate, Qty,
         SUM(Qty) OVER ( PARTITION BY Product ORDER BY TxDate ) AS SumQty
  FROM Table1
) t
WHERE SumQty - Qty < 10
;

Result:

| PRODUCT | CUSTOMER |                          TXDATE | QTY |
--------------------------------------------------------------
|   Apple |    Peter | February, 02 2013 00:00:00+0000 |   3 |
|   Apple |   Edward | February, 03 2013 00:00:00+0000 |   5 |
|   Apple |    Sally | February, 06 2013 00:00:00+0000 |   2 |
|  Orange |      Ray | February, 03 2013 00:00:00+0000 |   5 |
|  Orange |    Simon | February, 04 2013 00:00:00+0000 |   4 |
|  Orange |    Billy | February, 05 2013 00:00:00+0000 |   1 |

Doesn't work in SQLServer 2008, which is what the OP is using. — , May 15 '13 at 07:59
Well, sorry. This was not clear when I started to answer the question. — Peter Lang, May 15 '13 at 08:05

score 0 · Answer 3 · answered May 15 '13 at 08:54

-- emulation of demo date
DECLARE @MyTable TABLE (Product VARCHAR(10), Customer VARCHAR(10), TxDate SMALLDATETIME, Qty TINYINT)

INSERT INTO @MyTable(Product, Customer, TxDate, Qty )
VALUES
('Apple', 'Peter', '2013/02/02', 3),
('Apple', 'Edward', '2013/02/03', 5),
('Apple', 'Sally', '2013/02/06', 3),
('Apple', 'Emily', '2013/02/08', 6),
('Orange', 'Ray', '2013/02/03', 5),
('Orange', 'Simon', '2013/02/04', 4),
('Orange', 'Billy', '2013/02/05', 5),
('Orange', 'David', '2013/02/06', 2);

-- calculation

DECLARE @MyTable1 TABLE (Id TINYINT, Product VARCHAR(10), Customer VARCHAR(10), TxDate SMALLDATETIME, Qty TINYINT, CumSum INT);

WITH itemized
AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY Product ORDER BY TxDate) AS PurchaseNo
FROM @MyTable
)
INSERT INTO @MyTable1
SELECT PurchaseNo, Product, Customer, TxDate, Qty,
CumSum = 
(
    SELECT 
        SUM (i2.Qty)
    FROM itemized i2
    WHERE i2.Product = i1.Product AND i2.PurchaseNo <= i1.PurchaseNo  
)
FROM itemized i1
WHERE i1.PurchaseNo <=10;


SELECT 
    intermideate.Product, 
    intermideate.Customer,
    intermideate.TxDate,
    CASE
        WHEN intermideate.Id = final.MinIdAbove10 THEN intermideate.Qty - final.Correction 
        ELSE intermideate.Qty 
    END
FROM
(
SELECT t1.Product,
MinIdAbove10 =
(
    SELECT MIN(t2.Id)
    FROM @MyTable1 t2
    WHERE t2.CumSum >= 10 and t2.Product = t1.product
),
Correction =
(
    SELECT MIN(t2.CumSum - 10)
    FROM @MyTable1 t2
    WHERE t2.CumSum >= 10 and t2.Product = t1.product
)
FROM @MyTable1 t1
GROUP BY t1.Product 
) final
INNER JOIN @MyTable1 intermideate
ON intermideate.Product = final.Product AND intermideate.Id <= final.MinIdAbove10

SQL Query to calculate first n customers based on quantity conditions

3 Answers3