logo
down
shadow

TSQL - Creating a running total of a column


TSQL - Creating a running total of a column

By : hoang thao
Date : November 17 2020, 11:52 AM
I wish did fix the issue. I am writing a production record report and am having some difficulty. Here is what I am trying to accomplish: , Test Data
code :
DECLARE @Table TABLE ( Part VARCHAR(100), Qty INT)
INSERT INTO @Table VALUES 
('part1',  55), 
('part1',  105),
('part1',  70 ),
('part2',  100),
('part2',  25),
('part3',  150),
('part3',  50),
('part3',  35),
('part3',  80)
SELECT t.* , ISNULL(C.RunningTotal, 0) + t.Qty AS RunningTotal
FROM @Table t
    CROSS APPLY (SELECT SUM(Qty)
                 FROM @Table 
                 WHERE t.Part = Part
                  AND t.Qty > Qty)c(RunningTotal)
ORDER BY t.Part, t.Qty
╔═══════╦═════╦══════════════╗
║ Part  ║ Qty ║ RunningTotal ║
╠═══════╬═════╬══════════════╣
║ part1 ║  55 ║           55 ║
║ part1 ║  70 ║          125 ║
║ part1 ║ 105 ║          230 ║
║ part2 ║  25 ║           25 ║
║ part2 ║ 100 ║          125 ║
║ part3 ║  35 ║           35 ║
║ part3 ║  50 ║           85 ║
║ part3 ║  80 ║          165 ║
║ part3 ║ 150 ║          315 ║
╚═══════╩═════╩══════════════╝


Share : facebook icon twitter icon
Performant way to get the maximum value of a running total in TSQL

Performant way to get the maximum value of a running total in TSQL


By : Tim Gordon
Date : March 29 2020, 07:55 AM
this will help SQL Server sucks in calculating running totals.
Here's a solution for your very query (which groups by dates):
code :
WITH    q AS
        (
        SELECT  TranxDate, SUM(TranxAmt) AS TranxSum
        FROM    t_transaction
        GROUP BY
                TranxDate
        ),
        m (TranxDate, TranxSum) AS
        (
        SELECT  MIN(TranxDate), SUM(TranxAmt)
        FROM    (
                SELECT  TOP 1 WITH TIES *
                FROM    t_transaction
                ORDER BY
                        TranxDate
                ) q
        UNION ALL
        SELECT  DATEADD(day, 1, m.TranxDate),
                m.TranxSum + q.TranxSum
        FROM    m
        CROSS APPLY
                (
                SELECT  TranxSum
                FROM    q
                WHERE   q.TranxDate = DATEADD(day, 1, m.TranxDate) 
                ) q
        WHERE   m.TranxDate <= GETDATE()
        )
SELECT  TOP 1 *
FROM    m
ORDER BY
        TranxSum DESC
OPTION (MAXRECURSION 0)
TSQL Running total with start and end date

TSQL Running total with start and end date


By : Paco
Date : March 29 2020, 07:55 AM
To fix this issue I have two tables, , If you can use windowing functions then you can do like this:
code :
DECLARE @A TABLE
    (
      SurrKey INT ,
      ID INT ,
      StartDate DATE ,
      EndDate DATE ,
      Allotment MONEY
    )
DECLARE @B TABLE
    (
      ID INT ,
      Date DATE ,
      Used MONEY
    )


INSERT  INTO @A
VALUES  ( 1, 1, '20150101', '20150131', 1000 ),
        ( 2, 1, '20150115', '20150215', 1500 ),
        ( 3, 2, '20150101', '20150131', 1200 ),
        ( 4, 2, '20150210', '20150310', 1000 )


INSERT  INTO @B
VALUES  ( 1, '20150101', 800 ),
        ( 1, '20150114', 300 ),
        ( 1, '20150115', 100 ),
        ( 1, '20150118', 200 ),
        ( 2, '20150101', 700 ),
        ( 2, '20150114', 300 ),
        ( 2, '20150115', 150 ),
        ( 2, '20150205', 90 ),
        ( 2, '20150211', 100 );
WITH    cte
          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) AS RN ,
                        b.ID ,
                        b.Date ,
                        b.Used ,
                        SUM(b.Used) OVER ( PARTITION BY b.ID, o.SurrKey ORDER BY b.Date ) AS RTotal ,
                        -SUM(b.Used) OVER ( PARTITION BY b.ID, o.SurrKey ORDER BY b.Date ) + o.Allotment AS Remaining
               FROM     @B b
                        OUTER APPLY ( SELECT TOP 1 *
                                      FROM      @A a
                                      WHERE     a.ID = b.ID
                                                AND b.Date >= a.StartDate
                                      ORDER BY  a.StartDate DESC
                                    ) o
             )
    SELECT  * FROM    cte
RN  ID  Date        Used    RTotal   Remaining
1   1   2015-01-01  800.00  800.00   200.00
2   1   2015-01-14  300.00  1100.00  -100.00
3   1   2015-01-15  100.00  100.00   1400.00
4   1   2015-01-18  200.00  300.00   1200.00
5   2   2015-01-01  700.00  700.00   500.00
6   2   2015-01-14  300.00  1000.00  200.00
7   2   2015-01-15  150.00  1150.00  50.00
8   2   2015-02-05  90.00   1240.00  -40.00
9   2   2015-02-11  100.00  100.00   900.00
WITH    cte
          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) AS RN ,
                        b.ID ,
                        b.Date ,
                        b.Used ,
                        o.SurrKey ,
                        o.Allotment
               FROM     @B b
                        OUTER APPLY ( SELECT TOP 1 *
                                      FROM      @A a
                                      WHERE     a.ID = b.ID
                                                AND b.Date >= a.StartDate
                                      ORDER BY  a.StartDate DESC
                                    ) o
             ),
        cte1
          AS ( SELECT   cte.RN ,
                        cte.ID ,
                        cte.Date ,
                        cte.Used ,
                        ( SELECT    SUM(Used)
                          FROM      cte i
                          WHERE     i.ID = cte.ID
                                    AND i.SurrKey = cte.SurrKey
                                    AND i.Date <= cte.Date
                        ) AS RToTal ,
                        cte.Allotment
                        - ( SELECT  SUM(Used)
                            FROM    cte i
                            WHERE   i.ID = cte.ID
                                    AND i.SurrKey = cte.SurrKey
                                    AND i.Date <= cte.Date
                          ) AS Remaining
               FROM     cte
             )
    SELECT  * FROM    cte1  
tSQL - Recursive Running Total

tSQL - Recursive Running Total


By : AdirChen
Date : March 29 2020, 07:55 AM
it fixes the issue Ended up using a sum of LAG() unbounded. This issue became a mute issue, once scope of the project was altered dramatically and conditions no longer required a complex rolling totals, allowing for simple rolling totals (2012 introduces LAG() and LEAD() functions making rolling totals easier, in my opinion).
Thank you for the previous suggestions.
TSQL - running total

TSQL - running total


By : Andrey M.
Date : March 29 2020, 07:55 AM
wish of those help I have a script which I'm trying to work out a running balance on a set of transactions:
code :
Select Balance = OpeningBalance + 
        Sum(TotalValue) Over (Partition By AccountId
                              Order By PostingDate
                              Rows Between Unbounded Preceding And Current Row)

  From t;
Creating a Running Total column in a matrix?

Creating a Running Total column in a matrix?


By : Simon Bond
Date : March 29 2020, 07:55 AM
it fixes the issue For your purposes, an easy way to keep track of z would be to initialise it outside of the loop, then keep adding the value of sum_2_dice.
code :
def dice(n):
    z = 0
    rolls = np.empty(shape=(n, 3),dtype=int)
    for i in range(n):
        x = random.randint(1,6)
        y = random.randint(1,6)
        if x in [1,3,5] or y in [1,3,5]:
            sum_2_dice = x + y
            z += sum_2_dice
            rolls[i,:] = x, y, z
        else:
            sum_2_dice = -(x+y)  # meaning you "lose the sum" basically
            z += sum_2_dice
            rolls[i,:] = x, y, z
    return rolls


print (dice(3))
#[[ 6  2 -8]
# [ 4  5  1]
# [ 1  5  7]]
test = np.arange(0,5,1)
print (test)
# [0 1 2 3 4]

print (np.cumsum(test))
# [0 1 3 6 10]
Related Posts Related Posts :
  • Trigger on extended properties? SQL SERVER
  • Database Schema guidance for an app store
  • sql trigger to update based on specific column is updated and if value is equal to 'x'
  • How to descope a local variable in t-sql
  • SQL Server Compact stored procedure with multiple values in 1 input
  • How to validate the ICCID number of a SIM card in TSQL?
  • how to calculate all previous datas sum of a sql column on a date range report
  • How to create view listing all friendship for "friend1,friend2" table in T-SQL and prevent inserting duplicate
  • Sum of multiple column in SQL Server
  • Check month and day lies between two dates
  • How would I configure Effort Testing Tool to mock Entity Framework's DbContext withOut the actual SQL Server Database up
  • While looping through a function, Error: Maximum stored procedure, function, trigger, or view nesting level exceeded (li
  • Collapsing records with adjacent start and end dates
  • SQL Server Management Studio 2008 - Timeout Expired error
  • Insert date from VB to SQL Server
  • How to find what table foreign key is from?
  • Sql Server Nested Inserts possible
  • Deterministic way of getting WeekEnding (Sun) for given date column (for use in computed column)
  • SQL Server, why does one order by query work with a string, but not with an integer?
  • How do I install a SQL Server on the hosted build agent in Visual Studio Online?
  • EXECUTE AS/REVERT within a transaction in SQL Server
  • Distinct 2 field AS one column SQL SERVER
  • how to create a new database after a trigger fired from a table
  • How to sort string alphabetically
  • Checking data in one column in a temp table to a column in another table
  • What's wrong with my IF/ELSE? "ELSE: Incorrect syntax near 'ELSE'."
  • CASE CLAUSE IN WHERE CONDITION
  • Why am I getting not null here?
  • Why does sp_refreshview raise an exception on views that are not schema bound?
  • EXEC parameters from a SELECT
  • SQL Server Compact connection
  • Star Schema Structure - To many Dimensions
  • sort utf string in sql server with join clause
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org