asked on

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

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.

hilltop

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

Camillia

ASKER

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

Camillia

ASKER

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

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Camillia

ASKER

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

Camillia

ASKER

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.

Scott Pletcher

Yeah, we could override it to show 0 rather than the normal total if the CashCredited is 0, but yuck.

Camillia

ASKER

I'll leave it as is. What you have makes more sense. Ill I'll see what my manager says.

Scott Pletcher

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

Camillia

ASKER

Totally.