SQL: Show Records Once SUM Threshold Is Reached
sql rolling sum by date
running total sql
cumulative sum in sql
sql sum balance
sql sum over
sql cumulative sum by date
sql sum all columns
I have a table, sorted on a date value (ASC).
+----+------------+-------+ | Id | Date | Value | +----+------------+-------+ | 1 | 2018-01-01 | 10 | | 2 | 2018-01-02 | 5 | | 3 | 2018-01-03 | 15 | | 4 | 2018-01-04 | 0 | | 5 | 2018-01-05 | 5 | | 6 | 2018-01-06 | 10 | | 7 | 2018-01-07 | 5 | | 8 | 2018-01-08 | 0 | | 9 | 2018-01-09 | 0 | | 10 | 2018-01-10 | 10 | +----+------------+-------+
I would like to create a view that only returns the records once the SUM of the Value is higher than 30, starting from the first record. So my threshold is 30, every record with a value that fits in the first 30 should be hidden. All records that follow once this threshold is reached, need to be shown.
This means that my required result looks like this:
+----+------------+-------+ | Id | Date | Value | +----+------------+-------+ | 4 | 2018-01-04 | 0 | | 5 | 2018-01-05 | 5 | | 6 | 2018-01-06 | 10 | | 7 | 2018-01-07 | 5 | | 8 | 2018-01-08 | 0 | | 9 | 2018-01-09 | 0 | | 10 | 2018-01-10 | 10 | +----+------------+-------+
As you can see, Id's 1, 2 and 3 are left out, because their values (10, 5 and 15) SUM up to 30. Once this threshold is reached, the remaining records are visible (even the 0 value of Id 4).
I've created some scripts to setup a test table with data:
-- Create test table CREATE TABLE thresholdTest ( [Id] INT IDENTITY(1,1) PRIMARY KEY, [Date] DATE NOT NULL, [Value] INT NOT NULL ) -- Insert dummies INSERT INTO [thresholdTest] ([Date],[Value]) VALUES ('2018-01-01',10), ('2018-01-02',5), ('2018-01-03',15), ('2018-01-04',0), ('2018-01-05',5), ('2018-01-06',10), ('2018-01-07',5), ('2018-01-08',0), ('2018-01-09',0), ('2018-01-10',10); -- Select ordered by date SELECT * FROM [thresholdTest] ORDER BY [Date] ASC
All I need is a SELECT statement / view. The threshold is always static (30 in this example). The data could ofcourse differ, but it's always sorted on a Date and includes a Value.
Thank you in advance.
I'd use a window function:
;with cte as( select *, tot = sum([Value]) over (order by [Date]) from thresholdTest ) select Id, [Date], [Value] from cte where (tot >= 30 and [Value] = 0) or tot > 30
SQL select top X rows having sum limit, Just Join the table to itself and make the appropriate sum. This query will match each row in the table to all rows in the table with a smaller ID I had to borrow the code to create the temporary tables from David :) The below query will sum the purchase quantity and then filter out the record that exceeded the closing stock. Per your example on AB01, I don't see an easy way to display 4 rows and hide the fifth because 43 > 42. Maybe someone can help you throw an extra logic in it.
You can try to use
SUM with window function in subquery to accumulated
totle then write condition in main query.
select Id, Date, Value from ( SELECT *, SUM(Value) OVER(ORDER BY Date) totle FROM thresholdTest ) t WHERE totle > 30 OR (Value = 0 AND totle = 30)
| Id | Date | Value | |----|------------|-------| | 4 | 2018-01-04 | 0 | | 5 | 2018-01-05 | 5 | | 6 | 2018-01-06 | 10 | | 7 | 2018-01-07 | 5 | | 8 | 2018-01-08 | 0 | | 9 | 2018-01-09 | 0 | | 10 | 2018-01-10 | 10 |
Sum of previous n number of columns based on some category , SELECT category, year, week, value, sum(value) OVER (PARTITION BY NOTE that the x = 3 of your example gets translated to (the current row and the 2 preceding ones). 1) Can't test with SQL Server 2012 because I don't have one. I recently came across a problem that required having to perform a calculation in a query that involved a value in the current row and a value in the previous row. The problem is that SQL queries perform operations on a row-by-row basis; accessing data on different rows at the same time requires the query … Continue reading How to Use Values from Previous or Next Rows in a SQL Server Query
Yet another way to do it
select t1.id, t1.Date,t1.Value from [thresholdTest] t1 inner join [thresholdTest] t2 on t1.id >= t2.id group by t1.id, t1.value, t1.Date HAVING SUM(t2.VAlue)>30 OR( SUM(t2.value)=30 AND t1.value=0)
Calculate Running Total until sum reach input value , I want to get the results set which are reached my threshold value at each account As I red SQL 2012 have better approach and performance. But I want to display records when running total is reached my threshold value. The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets the number of rows or non NULL column values. COUNT() returns 0 if there were no matching rows.
Get rows in SQL until sum of a column(purchase qty) exceed closing , Thanks for providing example data and schema, that was really helpful. --Option A SELECT A.Material_Code, A.Batch, SUM(A.Purchase_qty) AS Total of purchase id's by @BatchKeys, gets cleared after each batch key loop purchase id's below threshold DECLARE @FinalPurchaseIds TABLE ( Id Is there a way to do tell Excel to stop summing once it reaches a certain number even if the range exceeds that number? I also need to tell the formula in Row 4 (the regular hours), that if 40 hours has been reached (from left to right), to leave the cell blank, and allow the overtime cell beneath in row 5 to retrieve the value entered in on
Calculating Values within a Rolling Window in Transact SQL , Before the SQL Window functions were implemented, it was tricky to calculate UNION ALL SELECT '2011-02-01',231 UNION ALL SELECT '2011-03-01',572 but to make a long story short it is simply a table that has one integer column represents the period we want to sum the 2011-01-01 row into. Did you know that you can use the SQL Server aggregate functions SUM, COUNT, MAX, MIN and AVG with an OVER Clause now? Using an OVER clause you can produce individual record values along with aggregate values to different levels, without using a GROUP BY clause. You can also produce running totals, rolling averages, etc.
4 Ways to Calculate a Running Total With SQL, Calculating a running total/rolling sum in SQL is a useful skill to have. An analytic function lets you partition data by a specific field. This will join all the dates from one table that are greater than the One of the last ways we have seen people calculate a running total is by using a subquery in the select SQL 2012 :: Cost Threshold For Parallelism - Subtree Cost; ADVERTISEMENT Le Threshold Maximum Feb 9, 1999. Hi, I have a table with 188376 rows and the data size = 3012 KB, index size = 5884 KB . LE threshold max is set to 2000 and LE threshol percent to 20% I have an index on that table and observed that it is not getting used.
- Your table isn't sorted on the date value unless a clustered index is created on that column. It's simply sorted on the return using the
order byas you have written. I just want to highlight this is very different and many people mistakenly think their data is sorted on disk in the order it is inserted. Without a clustered index, this isn't true.
- Your query will ignore
4 | 2018-01-04 | 0:) this row
- Thank you scsimon! Works like a charm :)