SQL Server:Select Rows Who's Sum Matches a Value

sql server : select rows who's sum matches a value

You can use recursive query in MSSQL to solve this.

SQLFiddle demo

The first recursive query build a tree of items with cumulative sum <= 150. Second recursive query takes leafs with cumulative sum = 150 and output all such paths to its roots. Also in the final results ordered by ItemsCount so you will get preferred groups (with minimal items count) first.

WITH CTE as
( SELECT id,num,
         id as Grp,
         0 as parent,
         num as CSum,
         1 as cnt,
         CAST(id as Varchar(MAX)) as path
     from T where num<=150
  UNION all
  SELECT t.id,t.num,
         CTE.Grp as Grp, 
         CTE.id as parent,
         T.num+CTE.CSum as CSum,
         CTE.cnt+1 as cnt,
         CTE.path+','+CAST(t.id as Varchar(MAX)) as path
    from T 
  JOIN CTE on T.num+CTE.CSum<=150 
             and CTE.id<T.id 
),
BACK_CTE as
(select CTE.id,CTE.num,CTE.grp, 
         CTE.path ,CTE.cnt as cnt,
         CTE.parent,CSum 
    from CTE where CTE.CSum=150
  union all
  select CTE.id,CTE.num,CTE.grp,
         BACK_CTE.path,BACK_CTE.cnt, 
         CTE.parent,CTE.CSum 
   from CTE
   JOIN BACK_CTE on CTE.id=BACK_CTE.parent 
              and CTE.Grp=BACK_CTE.Grp
              and BACK_CTE.CSum-BACK_CTE.num=CTE.CSum
) 
select id,NUM,path, cnt as ItemsCount   from BACK_CTE order by cnt,path,Id

SQL Server: select newest rows who's sum matches a value

Declare @YourTable table (ID int,QTY int,DATE varchar(25), CURRENT_STOCK int)
Insert Into @YourTable values
(1     ,1      ,'Jan'     ,30),
(2     ,1      ,'Feb'     ,30),
(3     ,2      ,'Mar'     ,30),
(4     ,6      ,'Apr'     ,30),
(5     ,8      ,'May'     ,30),
(6     ,21     ,'Jun'     ,30)

Select A.*
 From  @YourTable A
 Where ID>= (
                Select LastID=max(ID)
                 From  @YourTable A
                 Cross Apply (Select RT = sum(Qty) from @YourTable where ID>=A.ID) B
                 Where B.RT>=CURRENT_STOCK
             )

Returns

ID  QTY DATE    CURRENT_STOCK
4   6   Apr     30
5   8   May     30
6   21  Jun     30

SQL: How to select rows that sum up to certain value

You can use a correlated subquery to get the running total and retrieve those rows whose running total is < a specified number. (note that i changed the storage column to int. if it is a varchar the comparison would return the wrong result)

select id,user_id,storage
from uploads t
where storage+coalesce((select sum(storage) from uploads 
                        where storage<t.storage),0) < 410000
order by storage

SQL Fiddle

Edit: When there are duplicate values in the storage column, it has to be accounted for in the running sum by including a condition for the id column. (in this case < condition has been used, so the smallest id for a duplicate storage value gets picked up)

select id,user_id,storage
from uploads t
where storage+coalesce((select sum(storage) from uploads 
                        where storage<t.storage 
                        or (storage=t.storage and id < t.id)),0) < 410000
order by storage

Select rows until running sum reaches specific value

DECLARE @t TABLE (usr VARCHAR(100), dt DATE, amount INT);
INSERT INTO @t VALUES
('a', '2018-01-01', 100), -- 100
('a', '2018-02-01', 100), -- 200
('a', '2018-03-01', 100), -- 300
('a', '2018-04-01', 100), -- 400
('a', '2018-05-01', 100), -- 500
('b', '2018-01-01', 150), -- 150
('b', '2018-02-01', 150), -- 300
('b', '2018-03-01', 150), -- 450
('b', '2018-04-01', 150), -- 600
('b', '2018-05-01', 150); -- 750

DECLARE @Total INT = 301;

WITH cte AS
(
    SELECT *, SUM(amount) OVER (PARTITION BY usr ORDER BY dt) AS RunTotal
    FROM @t
)
SELECT *
FROM   cte
WHERE  cte.RunTotal - cte.amount < @Total -- running total for previous row is less
                                          -- than @Total then include current row

How to select rows which has cumulative sum of column value min to given value

We can use SUM here as an analytic function:

WITH cte AS (
    SELECT *, SUM(total_quantity) OVER (ORDER BY created_dttm DESC)
                  - total_quantity AS tq_sum
    FROM yourTable
)

SELECT id, type, total_quantity, created_dttm
FROM cte
WHERE tq_sum < 24;

Demo

The above trick (in the CTE) works by sparing the current row's total quantity from the running total. So the first row to exceed the threshhold of 24 would also be included, because its total quantity would be excluded from the running total.

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

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

Sql - SELECT rows until the sum of a row is a certain value

Assuming that the sum will match the rows in sequence. Following query will work.

DECLARE @Table TABLE(Reciept_Id INT , Amount INT)
INSERT INTO @Table
SELECT * 
FROM   (
         VALUES (1, 110),(2,110),(3,130),(4,110),(5,190)
       ) t (Reciept_Id, Amount) 

--Query

SELECT * FROM
(
    SELECT 
    Reciept_Id, 
    Amount,
    SUM(Amount) OVER(ORDER BY Reciept_Id ROWS 
       BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Total
FROM @Table
) T
WHERE T.Total <= 220

Output:

 Reciept_Id   Amount    Total
-----------   -------  ----------
    1          110      110
    2          110      220

Note : Query will work in SQL-Server 2012 and higher versions.

---

Related Topics