June 26, 2013 at 9:22 am
I need to create a table of running totals. Suppose initially we start with $50, then write a check for $3 (leaving $47), then write another check for $7 (leaving $40), and finally a check for $9 (leaving $31). Here's the end table.
check # check_amount Available_Funds
----------------------------------------------
0000 0 50
0001 3 47
0002 7 40
0003 9 31
Other than using a cursor to populate the Available_funds column how could you generate those values using t-sql?
TIA,
BD
June 26, 2013 at 9:29 am
Barkingdog (6/26/2013)
I need to create a table of running totals. Suppose initially we start with $50, then write a check for $3 (leaving $47), then write another check for $7 (leaving $40), and finally a check for $9 (leaving $31). Here's the end table.check # check_amount Available_Funds
----------------------------------------------
0000 0 50
0001 3 47
0002 7 40
0003 9 31
Other than using a cursor to populate the Available_funds column how could you generate those values using t-sql?
TIA,
BD
http://www.sqlservercentral.com/articles/68467/[/url]
Make sure you read the specific requirements for this or you will drive yourself crazy. If you need actual coding help...well you have been around here long to know what we need.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2013 at 8:41 pm
Its seems more involved than using a cursor! I am reading the article slowly.
BD
June 27, 2013 at 1:51 am
Hi, Please check the following code .
declare @table table (id int identity(1,1),item varchar(10),amt int)
insert into @table values (1,100),(2,200),(3,300),(4,400)
declare @sumtotal int
select @sumtotal = SUM(amt) from @table
SELECT a.id, a.amt,@sumtotal-SUM(b.amt)
FROM @table a,
@table b
WHERE b.id <= a.id
GROUP BY a.id, a.amt
ORDER BY a.id ;
Regards
Siva Kumar J.
June 27, 2013 at 2:00 am
Great idea. Thanks.
BD
June 27, 2013 at 6:22 am
sivaj2k (6/27/2013)
Hi, Please check the following code .declare @table table (id int identity(1,1),item varchar(10),amt int)
insert into @table values (1,100),(2,200),(3,300),(4,400)
declare @sumtotal int
select @sumtotal = SUM(amt) from @table
SELECT a.id, a.amt,@sumtotal-SUM(b.amt)
FROM @table a,
@table b
WHERE b.id <= a.id
GROUP BY a.id, a.amt
ORDER BY a.id ;
Regards
Siva Kumar J.
http://www.sqlservercentral.com/articles/T-SQL/61539/[/url]
June 27, 2013 at 7:27 am
sivaj2k (6/27/2013)
Hi, Please check the following code .declare @table table (id int identity(1,1),item varchar(10),amt int)
insert into @table values (1,100),(2,200),(3,300),(4,400)
declare @sumtotal int
select @sumtotal = SUM(amt) from @table
SELECT a.id, a.amt,@sumtotal-SUM(b.amt)
FROM @table a,
@table b
WHERE b.id <= a.id
GROUP BY a.id, a.amt
ORDER BY a.id ;
Regards
Siva Kumar J.
To expand slightly on the link that Cadavre posted. The query suggested here is called a triangular join and the performance of this type of query is unbelievably poor. It looks like a great idea but the reality is that it is super duper slow. Please read the article linked.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 27, 2013 at 7:29 am
Barkingdog (6/26/2013)
Its seems more involved than using a cursor! I am reading the article slowly.BD
Not sure that it is more involved than a cursor. It is a different mindset than a cursor. It will initially be a slower process because you are learning, just like the first time you created a cursor. Work through the examples and read the details. Once you understand the process you will be blown away by how much faster it is than a cursor. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 27, 2013 at 8:07 am
I had never heard of a "triangular" join but I verified it is incredibly slow. . (I actually took the very data involved, exported it to a CSV file, and used Excel to compute the running totals. answers. Total Excel computation time: 15 seconds! Every product has its strengths and weaknesses.)
BD
June 27, 2013 at 8:17 am
SQL Server 2012 is good for running totals.
I've also used Hugo's cursor for running totals and Jeff's quirky update method. Jeff's is quicker, so I generally use that for when they need to be calculated. I then normally have a job that runs once a week to confirm that the calculated running totals are correct using Hugo's cursor (can be found somewhere in the discussion for the quirky update article).
June 27, 2013 at 8:30 am
Barkingdog (6/27/2013)
I had never heard of a "triangular" join but I verified it is incredibly slow. . (I actually took the very data involved, exported it to a CSV file, and used Excel to compute the running totals. answers. Total Excel computation time: 15 seconds! Every product has its strengths and weaknesses.)BD
You might be surprised at how fast the quirky update will run. Unless this is a one time thing that is the first approach I would consider.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 27, 2013 at 12:56 pm
Barkingdog (6/27/2013)
I had never heard of a "triangular" join but I verified it is incredibly slow. . (I actually took the very data involved, exported it to a CSV file, and used Excel to compute the running totals. answers. Total Excel computation time: 15 seconds! Every product has its strengths and weaknesses.)BD
Triangular joins are bad; this is a great article about why: Hidden RBAR: Triangular Joins[/url]
As Sean mentioned, the quirky update is the way to go. I put together an example using the information provided; this will perform much better than the Triangular join method proposed earlier:
-- (1) Target Table
-----------------------------------------------
IF OBJECT_ID('tempdb..#checkbook') IS NOT NULL
DROP TABLE #checkbook;
CREATE TABLE #checkbook
(check_no char(4) primary key,
check_amount int not null,
available_funds int null);
DECLARE @starting_bal int=50;
DECLARE@total int=@starting_bal;
INSERT #checkbook
SELECT '0000',0,NULL UNION ALL
SELECT '0001',3,NULL UNION ALL
SELECT '0002',7,NULL UNION ALL
SELECT '0003',9,NULL;
-- (2) Running total query:
-----------------------------------------------
UPDATE #checkbook
SET @total = available_funds = @total - check_amount
FROM #checkbook;
SELECT *
FROM #checkbook
-- Itzik Ben-Gan 2001
June 27, 2013 at 6:25 pm
Alan,
Thanks for the great URL on triangular JOINS and an equally great solution.
BD
June 27, 2013 at 8:36 pm
sivaj2k (6/27/2013)
Hi, Please check the following code .declare @table table (id int identity(1,1),item varchar(10),amt int)
insert into @table values (1,100),(2,200),(3,300),(4,400)
declare @sumtotal int
select @sumtotal = SUM(amt) from @table
SELECT a.id, a.amt,@sumtotal-SUM(b.amt)
FROM @table a,
@table b
WHERE b.id <= a.id
GROUP BY a.id, a.amt
ORDER BY a.id ;
Regards
Siva Kumar J.
Be careful now. That makes for a "Triangular Join". Please see the following article for why you should probably avoid such things.
http://www.sqlservercentral.com/articles/T-SQL/61539/
{EDIT while banging head on table with the occasional face-palm) Reminder to self... scroll down [font="Arial Black"]before [/font]posting. :blush:
--Jeff Moden
Change is inevitable... Change for the better is not.
June 27, 2013 at 8:50 pm
Alan.B (6/27/2013)
Barkingdog (6/27/2013)
I had never heard of a "triangular" join but I verified it is incredibly slow. . (I actually took the very data involved, exported it to a CSV file, and used Excel to compute the running totals. answers. Total Excel computation time: 15 seconds! Every product has its strengths and weaknesses.)BD
Triangular joins are bad; this is a great article about why: Hidden RBAR: Triangular Joins[/url]
As Sean mentioned, the quirky update is the way to go. I put together an example using the information provided; this will perform much better than the Triangular join method proposed earlier:
-- (1) Target Table
-----------------------------------------------
IF OBJECT_ID('tempdb..#checkbook') IS NOT NULL
DROP TABLE #checkbook;
CREATE TABLE #checkbook
(check_no char(4) primary key,
check_amount int not null,
available_funds int null);
DECLARE @starting_bal int=50;
DECLARE@total int=@starting_bal;
INSERT #checkbook
SELECT '0000',0,NULL UNION ALL
SELECT '0001',3,NULL UNION ALL
SELECT '0002',7,NULL UNION ALL
SELECT '0003',9,NULL;
-- (2) Running total query:
-----------------------------------------------
UPDATE #checkbook
SET @total = available_funds = @total - check_amount
FROM #checkbook;
SELECT *
FROM #checkbook
Oh... now I can say it for real. Be careful now. While you don't need to follow as many rules when you're using a Temp Table (because you have absolute control over the table), you really do need to guarantee that parallelism cannot occur which would really mess things up. Like this... (the index hint wasn't in the latest article because so many people started bleeding at the gums when they saw it. Gotta please the bleeders. 😛 ).
UPDATE #checkbook
SET @total = available_funds = @total - check_amount
FROM #checkbook WITH INDEX(1)
OPTION (MAXDOP 1);
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply