Adding up rows

Camillia
Camillia used Ask the Experts™
on
This is SQL 2012. I'll go step by step and show what I need done. I'm thinking I need a loop but also somehow to see the next row

1. Sample data
  Create Table #test
(
 Device VARCHAR(250),
 CashCredited Money,
 PlayType VarChar(50),
 DateAndTime DateTime,
 [Transaction ID] BIGINT,
 accunulated money)

--inserts

 INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated

 )
 SELECT 'MX',-2.50,'Debit','2019-02-05 11:12:21.920',1211793,0 --last column needs to be -2.50


INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated

 )
 SELECT 'MX', 0,'Credit','2019-02-05 11:12:45.000',1211794,0

 INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated

 )
 SELECT 'MX', -1,'Debit','2019-02-05 11:30:26.383',1211795,0 -- last column needs to be -3.50

  INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated
 )
 SELECT 'FSS',0 ,'Posting','2019-02-05 11:30:27.383',8888,0 --second column needs to be 3.50

   INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated
 )
 SELECT 'MX',0,'credit','2019-02-05 11:31:01.000',1211796,0

    INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated
 )
  SELECT 'MX',-1,'Debit','2019-02-05 11:34:42.137',1211797, 0 --last column needs to be -1

      INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated
 )

 SELECT 'MX',-1,'Debit','2019-02-05 12:03:15.453',1211799, 0 --last column needs to be -2

      INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated
 )
 SELECT 'FSS',0 ,'Posting','2019-02-05 12:30:26.383',8888,0 --> second column needs to be 2

 --- select from the table
 SELECT * FROM #test
ORDER BY DateAndTime DESC

--TRUNCATE TABLE #test

Open in new window


2. I want 2 columns populated: CashCreidted and Accumulated

3. This is how I need it to look like

ForEE.png
This is how it should add up:
You see row 8 --> it has -2.50 in Cashcreidted so Accoumlated column needs to be -2.5
               row 7 --> it has 0.00 in Cashcredited so Accoumlated column needs to be 0.00
              row 6 --> it has -1 in CashCredited so Accumulated is -3.5 ( added up the rows-2.5 + 0 + -1)
              row 5 --> it has 'FSS' so we stop and have Accumlated column as zero (resetting the column) and CashCredited is 3.5
We start again
             row 4 --> has 0 in Cashcredited so Accumulated is 0
             row 3 has -1 in CashCredited so Acculamted is -1
             row 2 has -1 in CashCredited so Accumlated is -2 (added 0 + -1 + -1)
             row 1 has 'FSS" so we stop and put -2 in CashCredited and Accumulated is zero (reset)

How can do this? the rows go by datetime column. Thinking out loud and looking at the screenshot...
a. I should add an identity field to #test
 b. After #test is populated, I need a loop
     1. get count(*) of the rows
     2. have a counter to get the highest row (but I think this can be done if the rows are in asc)
     3. get the next row columns
     4. compare the rows
     5. update the appropriate column

Any help would be great.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Would a cursor be handy? Opening a cursor on a result set allows processing the result set one row at a time.

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/cursors-transact-sql?view=sql-server-2017
DECLARE MY_CURSOR Cursor 
FOR 
Select all, your, fields from youtable

Open My_Cursor 
DECLARE @Field1 nvarchar(50), @Field2 nvarchar(50), @Field3 nvarchar(50)

Fetch NEXT FROM MY_Cursor INTO @Field1, @Field1, @Field3 
While (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)

--do some stuff here


FETCH NEXT FROM MY_CURSOR INTO @Field1, @Field1, @Field3
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

Open in new window

I was on this track

DECLARE @count INT
DECLARE @maxrow INT
DECLARE @nextrow INT
DECLARE @maxcashcredited MONEY
DECLARE @nextcashcredited money

SELECT @count = COUNT(*) FROM #test

WHILE (@count > 0)
 BEGIN

   SELECT @maxrow = MAX(id) FROM #test
   SELECT @nextrow = MAX(id) -1 FROM #test

   SELECT @maxcashcredited = cashcredited FROM #test WHERE id = @maxrow
   SELECT @nextcashcredited = cashcredited FROM #test WHERE id = @nextrow

   --********* do comparison stuff here and see if it works
   SET @count = @count -1
 END 

Open in new window

Continuing with it, this is what I have so far

DECLARE @count INT
DECLARE @maxrow INT
DECLARE @nextrow INT
DECLARE @maxcashcredited MONEY
DECLARE @nextcashcredited MONEY
DECLARE @isFSS BIT
DECLARE @maxdevice AS VARCHAR(250)
DECLARE @nextdevice AS VARCHAR(250)

SELECT @count = COUNT(*) FROM #test

WHILE (@count > 0)
 BEGIN

   SELECT @maxrow = MAX(id) FROM #test
   SELECT @nextrow = MAX(id) -1 FROM #test

   SELECT @maxdevice = Device FROM #test WHERE id = @maxrow -- see if  it's 'Fss'
   SELECT @nextdevice = device FROM #test WHERE id = @nextrow

   SELECT @maxcashcredited = cashcredited FROM #test WHERE id = @maxrow
   SELECT @nextcashcredited = cashcredited FROM #test WHERE id = @nextrow

   --do comparision here
   IF (@maxdevice != 'FSS')
    BEGIN 
	    IF (@maxcashcredited <> 0)
     UPDATE #test SET accunulated = @maxcashcredited
	END
    
 

   

   SET @count = @count -1
 END 

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
We all know to stay away from cursors whenever we can.

Give this a shot.  

Btw, you might not need a separate temp table.  Instead, a nonclus index on the original source table, filtered on "Device = 'FSS'", should be able to provide the relevant FSS datetime as well..  But, let's not risk that now, just see if the code works first.  Then we can test the query to see if a permanent index can be used instead of a temp table.

CREATE TABLE #FSS ( DateAndTime datetime PRIMARY KEY );
INSERT INTO #FSS
SELECT DateAndTime
FROM #test
WHERE Device = 'FSS'

SELECT
    t.Device,
    CASE WHEN t.DateAndTime <> fss.DateAndTime THEN t.CashCredited ELSE
        -SUM(t.CashCredited) OVER(PARTITION BY fss.DateAndTime ORDER BY t.DateAndTime
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) END AS CashCredited,
    t.PlayType, t.DateAndTime, t.[Transaction ID],
    CASE WHEN t.DateAndTime <> fss.DateAndTime THEN
        SUM(t.CashCredited) OVER(PARTITION BY fss.DateAndTime ORDER BY t.DateAndTime
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        ELSE 0 END AS accumulated
FROM #test t
CROSS APPLY (
    SELECT TOP (1) *
    FROM #FSS f
    WHERE f.DateAndTime >= t.DateAndTime
    ORDER BY f.DateAndTime
) AS fss
ORDER BY t.DateAndTime DESC
Thanks, scott, let me try your sql.

This is my latest

DECLARE @count INT
DECLARE @maxrow INT
DECLARE @nextrow INT
DECLARE @maxcashcredited MONEY
DECLARE @nextcashcredited MONEY
DECLARE @isFSS BIT
DECLARE @maxdevice AS VARCHAR(250)
DECLARE @nextdevice AS VARCHAR(250)

SELECT @count = COUNT(*) FROM #test

WHILE (@count > 0)
 BEGIN

   SELECT @maxrow = MAX(id) FROM #test
   SELECT @nextrow = MAX(id) -1 FROM #test

   SELECT @maxdevice = Device FROM #test WHERE id = @maxrow -- see if  it's 'Fss'
   SELECT @nextdevice = device FROM #test WHERE id = @nextrow

   SELECT @maxcashcredited = cashcredited FROM #test WHERE id = @maxrow
   SELECT @nextcashcredited = cashcredited FROM #test WHERE id = @nextrow

   --do comparision here
   IF (@maxdevice <> 'FSS')
    BEGIN 
	    IF (@maxcashcredited <> 0)
		begin
           UPDATE #test SET accunulated = @maxcashcredited WHERE id = @maxrow
        end
	   IF (@nextdevice <> 'FSS')
	    BEGIN
           UPDATE #test SET accunulated = @maxcashcredited WHERE id = @nextrow --fix have to add up rows and maybe check for zero. Or, i can fix the data after the table is populated
		END
        

	END
    

Open in new window

Scott,

I think yours will work. Need to tweak it. The row I have marked..accumulated column...needs to be zero because Cashcreidted is zero and users don't want to see a value in there. I'll ask my manager because it doesn't make sense. I think what you have is correct and we should have a value in there.

ee2.png
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Yeah, we could override it to show 0 rather than the normal total if the CashCredited is 0, but yuck.
I'll leave it as is. What you have makes more sense. Ill I'll see what my manager says.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
That approach should perform much better than a cursor, at least.
Totally.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial