SQL Server 2008 : select newest rows whose sum matches a value in the row

Question

I asked a similar question recently, but the actual implementation proved more difficult as it had to rely on a date field, not an ID field, so I'm going to start a new question as the method will no doubt prove somewhat different.

Looking at the table below, only those rows whose SUM(PurchQty) are necessary to arrive at a value greater than / equal to "CurrentStock" should be returned, and those must be newest rows first, based on date. This needs to be evaluated and row(s) returned per "ProductCode".

Therefore, given this table:

ID    ProductCode    Date        PurchQty    CurrentStock
1001  AB101          14/12/2016      9            14
1111  AB101          01/01/2017     18            14
1223  AB101          15/01/2017     20            14
1233  BB400          02/01/2017     50            40 
1321  AB101          31/01/2017      8            14
1400  BB400          12/12/2016     90            40
1456  CC200          13/03/2017    100            20

Our query should yield the result:

ProductCode    Date         PurchQty    CurrentStock
AB101          31/01/2017      8             14 
AB101          15/01/2017     20             14
BB400          02/01/2017     50             40
CC200          13/03/2017    100             20

(note these are UK dates - dd/mm/yyyy - but that is unimportant)

Answer 1

Try This, This will give the desired result, I have used recursive CTE

CREATE TABLE #tst (id int,ProductCode varchar(50),[Date] Datetime,PurchQty int,CurrentStock int)
INSERT INTO #tst
SELECT 1001,'AB101','2016/12/14',9,14 UNION
SELECT 1111,'AB101','2017/01/01',18,14 UNION
SELECT 1223,'AB101','2017/01/15',20,14 UNION
SELECT 1233,'BB400','2017/01/02',50,40 UNION
SELECT 1321,'AB101','2017/01/31',8,14 UNION
SELECT 1400,'BB400','2016/12/12',90,40 UNION
SELECT 1456,'CC200','2017/03/13',100,20 

;with CTE AS (
SELECT ROW_NUMBER() over(partition by ProductCode order by [date] desc) as RowId,* from #tst
),CTE2 AS
(
SELECT RowId,id ,ProductCode ,[Date] ,PurchQty ,CurrentStock,PurchQty as Cum_Quantity from CTE as a WHERE RowId=1
UNION ALL
SELECT a.RowId,a.id ,a.ProductCode ,a.[Date] ,a.PurchQty ,a.CurrentStock, a.PurchQty+b.Cum_Quantity
FROM CTE a 
JOIN CTE2 as b  ON a.ProductCode=b.ProductCode 
WHERE a.RowId=b.RowId+1  AND a.[Date]<b.[Date] AND b.Cum_Quantity<b.CurrentStock
)
SELECT * from CTE2 order by ProductCode

Answer 2

You need a cumulative sum. In SQL Server 2008, this can be done using apply or a correlated subquery:

select t.*, t2.cume_PurchQty
from t cross apply
     (select sum(PurchQty) as cume_PurchQty
      from t t2
      where t2.ProductCode = t.ProductCode and t2.Date <= t.Date
     ) t2
where t2.cume_PurchQty < t.CurrentStock;

In SQL Server 2012+, this is much more efficiently written using window functions:

select t.*
from (select t.*,
             sum(PurchQty) over (partition by ProductCode order by date) as cume_PurchQty
      from t
     ) t
where cume_PurchQty < CurrentStock

Answer 3

;With Cte_1(ID,ProductCode,[Date],PurchQty,CurrentStock)
As
(
SELECT 1001,'AB101','14/12/2016',  9,14 Union All
SELECT 1111,'AB101','01/01/2017', 18,14 Union All
SELECT 1223,'AB101','15/01/2017', 20,14 Union All
SELECT 1233,'BB400','02/01/2017', 50,40 Union All
SELECT 1321,'AB101','31/01/2017',  8,14 Union All
SELECT 1400,'BB400','12/12/2016', 90,40 Union All
SELECT 1456,'CC200','13/03/2017',100,20
),
cte2 AS (
        SELECT ProductCode
            ,[Date]
            ,CASE 
                WHEN Sum(PurchQty) OVER (
                        PARTITION BY ProductCode
                        ,[DATE] ORDER BY ProductCode
                        ) >= CurrentStock
                    THEN PurchQty
                END SumPurchQty
            ,CurrentStock
        FROM Cte_1
        )
    ,Cte3 AS (
        SELECT ROW_NUMBER() OVER (
                PARTITION BY ProductCode ORDER BY SumPurchQty DESC
                ) AS SeqOrder
            ,ProductCode,[DATE],SumPurchQty,CurrentStock
        FROM cte2
        )

SELECT ProductCode
    ,[Date]
    ,SumPurchQty
    ,CurrentStock
FROM Cte3
WHERE SeqOrder = 1

Answer 4

This can be achieved easily by using Rank function (row_number()). I have used your sample input to populate the temp table of similar structure.

create table #tmp
(
ID int, productcode varchar(20), dates date, purchqty int, currstock int
)
insert into #tmp values (1001,'AB101','14-Dec-2016',9,14)
insert into #tmp values (1111,'AB101','01-Jan-2017',18,14)
insert into #tmp values (1223,'AB101','15-Jan-2017',20,14)
insert into #tmp values (1233,'BB400','02-Jan-2017',50,40)
insert into #tmp values (1321,'AB101','31-Jan-2017',8,14)
insert into #tmp values (1400,'BB400','12-Dec-2016',90,40)
insert into #tmp values (1456,'CC200','13-Mar-2017',100,20)


;with CTE AS
(
select *,row_number() over (partition by Diff,productcode order by dates desc) RID 
from (
        select *, case when purchqty-currstock<=0 then 0 else 1 end Diff
        from #tmp  
    )D 
) 
select ID,productCode,dates,purchqty,currstock 
from CTE C1
where (RID=1 and diff=1) OR (Diff=0 and dates >= (select dates from CTE C2 where C2.productcode=C1.productcode and c2.Diff=1 and RID=1))

drop table #tmp