Adding up rows

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.
LVL 8
CamilliaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hilltopCommented:
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

CamilliaAuthor Commented:
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

CamilliaAuthor Commented:
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

Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Scott PletcherSenior DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CamilliaAuthor Commented:
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

CamilliaAuthor Commented:
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 DBACommented:
Yeah, we could override it to show 0 rather than the normal total if the CashCredited is 0, but yuck.
CamilliaAuthor Commented:
I'll leave it as is. What you have makes more sense. Ill I'll see what my manager says.
Scott PletcherSenior DBACommented:
That approach should perform much better than a cursor, at least.
CamilliaAuthor Commented:
Totally.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.