Questions tagged [running-totals]
The running-totals tag has no usage guidance.
1
vote
2answers
60 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 (...
9
votes
3answers
1k 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 ...
2
votes
2answers
30 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 ...
0
votes
1answer
45 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 ...
1
vote
2answers
44 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 ...
0
votes
2answers
105 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 ...
4
votes
1answer
107 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 ...
1
vote
1answer
219 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 ...
0
votes
1answer
92 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 ...
10
votes
2answers
556 views
Calculating stock quantity based on change log
Imagine that you have the following table structure:
LogId | ProductId | FromPositionId | ToPositionId | Date | Quantity
---------------------------------------------------------------...
2
votes
2answers
614 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,...
0
votes
1answer
18 views
Using OLAP-specs to compute selective running totals
Let's say I have a table with columns id, subid, state, value, where (id, subid) is a unique tuple, state may be 0 or some value <> 0, and value is just some number. I want to build a query that ...
0
votes
1answer
30 views
Running total from another field
I have a table just like that:
date sales rest
1/1/2017 52 0
2/1/2017 30 0
3/1/2017 10 200.
I only have the rest in the last date and i want to make it like this:
...
1
vote
2answers
166 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 ...
2
votes
4answers
1k 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 ...
3
votes
4answers
2k 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
...
5
votes
2answers
623 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 ...
10
votes
4answers
2k 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,
...
1
vote
2answers
251 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 ...
0
votes
1answer
1k 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 ...
0
votes
1answer
60 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 ...
7
votes
3answers
382 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. ...
1
vote
1answer
1k 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 ...
2
votes
1answer
4k 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 ...
5
votes
1answer
6k 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 ...
4
votes
1answer
10k 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 ...
1
vote
2answers
66 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 ...
3
votes
1answer
78 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-...
4
votes
1answer
3k 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 | ...
1
vote
1answer
87 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 ...
0
votes
2answers
6k 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
...
1
vote
0answers
716 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 ...
3
votes
3answers
1k 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
-------------...
0
votes
1answer
193 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
...
1
vote
3answers
5k 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 ...
2
votes
3answers
2k 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 ...
