Questions tagged [running-totals]
59 questions
14
votes
3 answers
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 result:
id | value
----+-------
1 | 136
2 |…
peterh
- 2,137
- 8
- 28
- 41
11
votes
4 answers
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,
grp int
)
insert into #reset_runn_total
values…
Pரதீப்
- 1,409
- 5
- 18
- 37
10
votes
2 answers
Calculating stock quantity based on change log
Imagine that you have the following table structure:
LogId | ProductId | FromPositionId | ToPositionId | Date | Quantity
-----------------------------------------------------------------------------------
1 | 123 | 0 …
Henrik
- 103
- 1
- 6
7
votes
3 answers
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. But then again, my mind is feeble.
I am using SQL…
Jeff.Clark
- 627
- 7
- 26
6
votes
1 answer
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 | 3 | AO21 |
| PO002 | 10-Jan-2016 | 7 …
Mei_R
- 63
- 1
- 7
5
votes
1 answer
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 Taco_Val
VALUES
('a1', 1, '2018-08-09…
SATISD9X
- 109
- 8
5
votes
2 answers
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 -7 1
5 1 2
I would…
Ryan
- 313
- 5
- 15
5
votes
1 answer
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 database structure consisting of two…
Roland
- 53
- 1
- 1
- 3
5
votes
1 answer
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 first row's value of
rel_qty.
Second value of qoh…
Marin Mohanadas
- 478
- 4
- 7
- 19
4
votes
3 answers
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…
Felix Pamittan
- 285
- 1
- 3
- 12
3
votes
4 answers
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
SELECT pkid::int, numvalue::int, groupid::int
…
Evan Carroll
- 65,432
- 50
- 254
- 507
3
votes
1 answer
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, DepositDate.
Another table Withdrawal is created…
JamesYTL
- 137
- 1
- 1
- 7
3
votes
1 answer
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-01 : 1
2016-01-02 : 1
2016-01-03 : 5
2016-01-04 :…
Dick Pan
- 35
- 2
3
votes
1 answer
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 | state |…
vibration
- 53
- 2
- 5
2
votes
3 answers
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 week, but need to also get the transactions from…
damian
- 123
- 1
- 6