Questions tagged [running-totals]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
14 votes
3 answers
13k views

How to get the last not-null value in an ordered column of a huge table?

I have the following input: id | value ----+------- 1 | 136 2 | NULL 3 | 650 4 | NULL 5 | NULL 6 | NULL 7 | 954 8 | NULL 9 | 104 10 | NULL I expect the following ...
peterh's user avatar
  • 2,077
11 votes
4 answers
7k views

Reset Running Total based on another column

Am trying to calculate running total. But it should reset when the cummulative sum greater than another column value create table #reset_runn_total ( id int identity(1,1), val int, reset_val int, ...
Pரதீப்'s user avatar
10 votes
2 answers
3k views

Calculating stock quantity based on change log

Imagine that you have the following table structure: LogId | ProductId | FromPositionId | ToPositionId | Date | Quantity ---------------------------------------------------------------...
Henrik's user avatar
  • 103
7 votes
3 answers
997 views

Is it possible to do this math in a view?

I have been tasked with creating a view for a client. Specifically it must be in a view. However, there is some math that I am not sure how to do within a view. I do not know if it is even possible. ...
Jeff.Clark's user avatar
6 votes
1 answer
9k views

Calculate Quantity Based on First in, First Out (FIFO)

I'm trying to get result of quantity based on FIFO, with 2 tables below: Table Purchase: | PO | Date | Quantity | Item | |-------|--------------|----------|------| | PO001 | 01-Jan-2016 ...
Mei_R's user avatar
  • 63
5 votes
1 answer
10k views

SQL Query for cumulative sum

I am experiencing problems with formulating a (relatively) simple SQL Query (SQL Server 2012 is used). We have a database which counts something up for certain users. Therefore we have a really simple ...
Roland's user avatar
  • 53
5 votes
1 answer
26k views

Subtracting sum of previous rows' data from current row's data in SQL

I want to create a table or view in SSMS which should has a column called qoh. In that column should be calculate sum values of the column rel_qty. i.e. The first data of qoh is the same of the ...
Marin Mohanadas's user avatar
5 votes
1 answer
663 views

Cumulative duration between value resets

--DROP TABLE Taco_Val; CREATE TABLE Taco_Val ( ID INT IDENTITY(1,1), AuditID VARCHAR(5), CreditID VARCHAR(10), TS DATETIME, Val BIT ); INSERT INTO ...
SATISD9X's user avatar
  • 109
5 votes
2 answers
3k views

Set non-negative floor for rolling sum, in SQL Server

I need to set a floor on the rolling sum calculation. For example, with PKID NumValue GroupID ---------------------------- 1 -1 1 2 -2 1 3 5 1 4 ...
Ryan's user avatar
  • 313
4 votes
3 answers
2k views

Running Total: To store or compute

Say I have a product table with the following columns: product -------- product_id name And I have another table to record the transaction (IN/OUT) for each product product_transaction -------------...
Felix Pamittan's user avatar
3 votes
4 answers
6k views

Set non-negative floor for rolling sum, in PostgreSQL

This is a really fun question (asked for SQL Server) and I wanted to try it out to see how it was done in PostgreSQL. Let's see if anyone else can do it better. Taking this data, CREATE TABLE foo AS ...
Evan Carroll's user avatar
  • 62.7k
3 votes
1 answer
13k views

Calculate balance for every row by sequentially subtracting each row's amount from an initial sum

I'll explain the issue with an example. A query that will select and show the Withdrawal Process I made and show the status of my deposit balance. A table Deposit is created with columns TotalAmount, ...
JamesYTL's user avatar
  • 137
3 votes
1 answer
8k views

Total hours worked per employee per day including overnight sessions

I have a table like this: CREATE TABLE Table1 (ID int, empid int, time datetime, state int); +--------------+---------------------+-----------------+ | empid | time | ...
vibration's user avatar
3 votes
1 answer
103 views

Generate linear growth report

My table has the following data: 2016-01-01 : 1 2016-01-02 : 0 2016-01-03 : 4 2016-01-04 : 1 2016-01-05 : 2 I want to generate a report (linear growth) like this: date : linear growth 2016-01-...
Dick Pan's user avatar
2 votes
2 answers
122 views

Count Total time difference between selected row

My data contains an ID, status and time(hh:mm:ss). I need to calculate the total time between every 'Location' and 'Out of tunnel' status Current table ID || time || Status ...
Mrparkin's user avatar
2 votes
2 answers
7k views

Add cumulative sum to time-series query PostgreSQL 9.5

I wrote the query that gives me time-series over some date range and interval that shows revenue for each time interval: SELECT interval_date, coalesce(campaign_revenue,0) AS campaign_revenue,...
Alechko's user avatar
  • 229
2 votes
2 answers
2k views

How to create a running balance column based on account number?

I have a stored procedure that returns account statement for one or multiple accounts Here is the part I have issues with: returning a running balance in each record based on account number. My query ...
Abeer Sul's user avatar
  • 165
2 votes
1 answer
6k views

Calculating Running Total After Reset

I have a SQL Server 2012 table which has customer id, amount, and a reset columns. I’m trying to calculate the running total and also calculate the running total after reset. Once the reset "flag" is ...
user20907's user avatar
  • 173
2 votes
3 answers
4k views

Rolling count of total transactions over time

I need to get a set of total transactions over time on a weekly interval. Essentially I need a total-transactions-to-date column. When I group by WEEK(Date) I get the amount of transactions for that ...
damian's user avatar
  • 123
2 votes
2 answers
105 views

Calculate change in value window function when the identifier has dropped of the current period

I was wondering how can i got about finding the change in value from one period to another but in my scenario the identifier has dropped off the current period. I'm think this could be done with a ...
Glenn Sampson's user avatar
2 votes
2 answers
163 views

Group by running totals with reset

Sample Data Expected Data Need to append trades under an order in a record, was able to do that using string_agg, now I need to split the orders with a maximum value of 10. That is, the quantity for ...
macro32's user avatar
  • 23
2 votes
1 answer
215 views

Determining the overlap between payments and invoices

Given a table containing a list of invoices and a table containing a list of payments, I have the following problem: Payments should be allocated to invoices up to the total amount of the invoice. If ...
warhorus's user avatar
2 votes
2 answers
92 views

SQL Sum with break-out (audits on inventory)

I'm having some difficulty getting this mysql query to work. I have the following tables create table items ( item_id int not null auto_increment, item_name varchar(25), primary key (...
Jason Chvat's user avatar
2 votes
1 answer
1k views

Cumulative sum by day with non-negative floor with PostgreSQL

Using PostgreSQL 12, I need to calculate the cumulative sum by day while filling in missing days. I also need to set a minimum value of 0 if any cumulative sum goes negative. Background There are two ...
brianz's user avatar
  • 121
2 votes
1 answer
740 views

Cumulative sum in a period of months

I have this table: month_rep fruits harvested 2021-09-01 139 139 2021-10-01 143 11 2021-11-01 152 14 2021-12-01 112 9 2022-01-01 133 10 2022-02-01 145 12 2022-03-01 123 5 2022-04-01 111 4 ...
Jason's user avatar
  • 23
2 votes
4 answers
7k views

Running Total of a count per day in SQLite

I usually write a couple of very simple queries on a redshift database which used in a marketing visualization tool. And I repeat - VERY simple - I am a novice at this. I am now faced with a new ...
user132928's user avatar
1 vote
2 answers
2k views

SQL to choose a price between two points in time

Given a time-series of datetime price changes and another disparate time-series of N events, how do I query the last known price at the time of each event without running N queries? Essentially, I ...
Petrus Theron's user avatar
1 vote
2 answers
1k views

Running ratio of unique counts to total counts

Any ideas on how to code a running ratio of counts (unique and total) for ordered observations (rows) in t-SQL (SQL Server 2017 Community Edition or higher)? Given a data set, which is necessarily ...
Oleg Melnikov's user avatar
1 vote
2 answers
74 views

Need to sum a field in a row with same it and result needs to be cumulative in a field

This is my SQL Server 2008 R2 table: ID Amount date_time 001 9.00 01/01/2015 01:01:00 001 20.00 01/02/2015 01:02:00 001 20.00 01/02/2015 01:03:00 002 20.00 01/01/2015 01:01:00 002 20.00 ...
Kbert's user avatar
  • 11
1 vote
2 answers
616 views

How can I calculate the loan balance based on following data set

There are two loans 12345, 54321. I want to arrive at 'New Column' which is calculated column based on Balance and Transaction_amount. Also, need to calculate till maturity date. CREATE TABLE #Test (...
Sunil Babu Vudimudi's user avatar
1 vote
2 answers
2k views

I have a beginning balance I need to calculate ending balance running total by month rows

I have been searching this site and others trying to find an answer to this. I tried various things but cannot get my head around an answer so here goes. Here my data: What I need is to start with ...
C.Smith's user avatar
  • 11
1 vote
1 answer
693 views

Calculate running total of "consecutive days without views" for each group, that can restart multiple times within each group based on conditions

Summary I'm working to create a counter metric that shows, for each client's slug, the number of consecutive days of zero views. The counter should increase for each consecutive day that the client's ...
Matthew's user avatar
  • 21
1 vote
1 answer
614 views

Running sum total over 14 days for each date

In a database of transactions spanning about 50 customers over a period of 8 months, I need to find which customers have 0 total transactions over a consecutive 14-day period. The customer table (...
Darrel 's user avatar
1 vote
2 answers
819 views

Sum all unique values from previous dates

Let's say, for simplicity sake, I have the following table: id amount p_id date ------------------------------------------------ 1 5 1 2020-01-01T01:00:00 2 10 ...
tony's user avatar
  • 13
1 vote
1 answer
363 views

How to calculate running balance for many accounts?

please help me fix query below to calculate running balance correctly. What I need to do is: Calculate running balance (debit- credit) by account_no Reset the balance when account_no changes Reset ...
Abeer Sul's user avatar
  • 165
1 vote
1 answer
1k views

Running total until specific condition is met

I have a question about running totals, I know there are several approaches. However I have slight modification of it and I'm struggling to find the right way. So I have orders, each order has its ...
Irdis's user avatar
  • 205
1 vote
2 answers
4k views

Subtracting sum of previous rows

I want to create total field in this table. But in SQL server 2008 can't use 'order by' in OVER clause. I have same PO and difference INV. I want to Qty field minus Quantity field and next row Total ...
midtonight's user avatar
1 vote
1 answer
99 views

Group rows by running aggregate

I have a set of transactions that can either be payments or purchases. The purchases can be of different types (e.g. 'ApplePurchase', 'OrangePurchase'). I will select some payments by their coupon ...
S Holt's user avatar
  • 21
1 vote
1 answer
21 views

How do I show a running total from the previous records?

I wonder if someone can please assist. I have the following code which basically inserts 10 records into a table. From this table I'm trying to show a running total for the amount of sales against the ...
Dave's user avatar
  • 25
1 vote
1 answer
2k views

Distribute multiple payments to invoice lines

I'm having a problem allocating payments to invoice lines. We’re using MsSql 2014 Data looks like this: Invoice lines table (sales): lineId invoiceId value 1 1 100 2 ...
Claudiu Radu's user avatar
1 vote
0 answers
973 views

How to find consecutive count last 3 months count and last 6 months counts

Column names: Site_key. Billing_date ,Reason_type Things to Find: Consecutive count for last 5 months: Last 3 month Count Last 6 month Count I have to find Consecutive count for last 5 months for a ...
hardik's user avatar
  • 11
1 vote
3 answers
9k views

Building year to date aggregations using SQL

I need some help with building a monthly aggregation using only SQL. Imagine the following table: TranID DateCode Account Value 1 20140101 1 5 2 20140106 1 -3 3 ...
Taher's user avatar
  • 113
0 votes
1 answer
108 views

Getting Running Total from user defined function

I have the following query. Select x1.TotalAmount, [ReturnTaxAmount](x1.TotalAmount,@TaxRate) AS TaxAmount, x2.DocumentType FROM table1 X1 INNER JOIN table2 x2 on t1.id = t2.t1id Now how ...
JamesYTL's user avatar
  • 137
0 votes
2 answers
9k views

Sql getting debit, credit and balance issue

I am using the query below to get Debit/Credit Balance from table statement SELECT t.[InvoiceID], t.S_Type as Type, t.Date, t.Debit, t.Credit, b.Balance FROM Statement as t CROSS apply ...
user3403112's user avatar
0 votes
1 answer
2k views

How to calculate conditional running additions in SQL Server 2012

I have a table with below mentioned structure create table #test ( ID int identity(1,1), A int, B int ); and having data as ID A B 1 9 NULL 2 9 1 3 9 2 4 8 NULL 5 8 4 6 8 ...
sagar's user avatar
  • 103
0 votes
1 answer
220 views

Merging two Select queries with different where statements

I have this query below in which I am trying to get results for the month and year to date (account balance). SELECT T3.AcctCode, T3.AcctName, SUM(T1.Debit - T1.Credit)* -1 AS 'Balance' FROM OJDT T0 ...
Kinyanjui Kamau's user avatar
0 votes
1 answer
789 views

Is there any way to let Postgres know it can filter results and then calculate aggregations?

Given a table A with 10 million records and another table B with child items (one-to-many), I have to provide a consumable view with totals from several fields in B grouped by the FK of A. None of the ...
Dane's user avatar
  • 1
0 votes
1 answer
142 views

Analytical function: sum the cumulative of previous column

Using an analytical function, I want that the column 'sum' contains the cumulative of the previous column. But my code gets the total sum of all encounters. Here is both table and data for testing: ...
PakLui's user avatar
  • 77
0 votes
1 answer
331 views

Applying Multiple Window Functions in 1 SELECT using different partition

I was wondering if there is anything wrong with using this method. I have a working demo of what i'm trying to do below In the SELECT statement I have used 2 OVER clauses. Is there any risk that ...
Glenn Sampson's user avatar
0 votes
1 answer
3k views

CROSS APPLY query with CASE and running totals [closed]

I'm trying to make this business logic make sense, but I'm not having any luck. The rule is I can have a sale with discounts from percentage and value. But the value is to be subtracted after ...
Ruisf's user avatar
  • 1