Running total - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Running total

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-03-25 : 13:33:46

I'm using the following codein a MSSQL/PHP intranet project for paging through records:

DECLARE
@PageSize INT,
@PageNumber INT,
@FirstRow INT,
@LastRow INT

SELECT @PageSize = $pageLength,
@PageNumber = $pageNumber;

SELECT @FirstRow = ( @PageNumber - 1) * @PageSize + 1,
@LastRow = (@PageNumber - 1) * @PageSize + @PageSize;

WITH results AS

( SELECT

t1.Mortgage_Number,
t1.Advance_Number,
t1.Effective_Date,
t1.Due_Date,
t1.Transaction_Type,
t1.Posting_Type,
t1.Transaction_Amount,
t1.Account_Source,
t1.Sub_Posting_Type,
CASE
WHEN t2.description IS NOT NULL
THEN t2.description
ELSE t1.Transaction_Description
END AS Transaction_Description,
ROW_NUMBER() OVER (ORDER BY [Effective_Date] $direction) AS RowNumber

FROM format_transactions AS t1

LEFT JOIN sd_posting_types AS t2
ON t1.Transaction_Type = t2.Transaction_Type
AND t1.Posting_Type = t2.Posting_Type
AND t1.Sub_Posting_Type = t2.Sub_Posting_Type
AND t1.Account_Source = t2.Account_Type

WHERE (Mortgage_Number = '$mortgage' $additional_date_params)

)

SELECT

Mortgage_Number,
Advance_Number,
Effective_Date,
Due_Date,
Transaction_Type,
Posting_Type,
Transaction_Amount,
Account_Source,
Sub_Posting_Type,
Transaction_Description

FROM results

WHERE RowNumber BETWEEN @FirstRow AND @LastRow
ORDER BY [Effective_Date] $direction
[/code]

I need a running total column for the column 'Transaction_Amount which is correct nomatter what page the user goes to.

How can I achieve this please? So far my PHP code effortshave only brough success then moving forward through the 'pages'. Things got all screwed up then paging backwards. My SQL skills are basic which is why I need expert help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-25 : 14:19:28

Ok. Thats fine. Now what is your question?

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-03-25 : 14:26:39

That's strange, my posting was truncated.

My question was that I need to create an additional running total column based upon the column 'Transaction_amount' so that it is correct for paging though the records.
So far my php code attempts have only been successful when paging forward. Backward paging has been screwed up, so I though that using the sql query to do the whole thing may be a better course of action.

Not sure how to do this though.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-25 : 14:47:50

Try like this:-

CREATE TABLE #temp
(
RowNumber int,
Mortgage_Number int,
Advance_Number int,
Effective_Date datetime,
Due_Date datetime,
Transaction_Type varchar(20),
Posting_Type varchar(20),
Transaction_Amount float,
Account_Source varchar(20),
Sub_Posting_Type varchar(20),
Transaction_Description varchar(100),
Transaction_Total float
)

DECLARE
@PageSize INT,
@PageNumber INT,
@FirstRow INT,
@LastRow INT,
@Transaction_total float

SET @Transaction_Total=0
SELECT @PageSize = $pageLength,
@PageNumber = $pageNumber;

SELECT @FirstRow = ( @PageNumber - 1) * @PageSize + 1,
@LastRow = (@PageNumber - 1) * @PageSize + @PageSize;


WITH results AS


( SELECT


t1.Mortgage_Number,
t1.Advance_Number,
t1.Effective_Date,
t1.Due_Date,
t1.Transaction_Type,
t1.Posting_Type,
t1.Transaction_Amount,
t1.Account_Source,
t1.Sub_Posting_Type,
CASE
WHEN t2.description IS NOT NULL
THEN t2.description
ELSE t1.Transaction_Description
END AS Transaction_Description, 
ROW_NUMBER() OVER (ORDER BY [Effective_Date] $direction) AS RowNumber

FROM format_transactions AS t1

LEFT JOIN sd_posting_types AS t2
ON t1.Transaction_Type = t2.Transaction_Type
AND t1.Posting_Type = t2.Posting_Type
AND t1.Sub_Posting_Type = t2.Sub_Posting_Type
AND t1.Account_Source = t2.Account_Type

WHERE (Mortgage_Number = '$mortgage' $additional_date_params)



)
INSERT INTO #temp
(Mortgage_Number,
Advance_Number,
Effective_Date,
Due_Date,
Transaction_Type,
Posting_Type,
Transaction_Amount,
Account_Source,
Sub_Posting_Type,
Transaction_Description )
SELECT 
RowNumber
Mortgage_Number,
Advance_Number,
Effective_Date,
Due_Date,
Transaction_Type,
Posting_Type,
Transaction_Amount,
Account_Source,
Sub_Posting_Type,
Transaction_Description 

FROM results


UPDATE #temp
SET @Transaction_Total=Transaction_Total=@Transaction_Total+ Transaction_amount

SELECT *
FROM #temp
WHERE RowNumber BETWEEN @FirstRow AND @LastRow
ORDER BY [Effective_Date] $direction

stephe40
Posting Yak Master

218 Posts

Posted - 2008-03-25 : 15:36:32

Have you read this: http://www.sqlteam.com/article/calculating-running-totals

- Eric

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-03-25 : 16:27:52

Many thanks visakh16. But this gives me the error:

Cannot insert the value NULL into column 'RowNumber', table 'tempdb.dbo.#temp column does not allow nulls. INSERT fails. (severity 16)

Thanks to Eric too.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-03-25 : 16:36:06

Oh, are there any SQL gurus/smarties here that can show me how to adapt my original script in line with solutions 2 and 3 shown in the link that Eric posted please?

I'd like to educate myself further by seeing that in action.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-03-26 : 07:45:50

From Eric's example link I thought that I would try and use solution 2:


SELECT DayCount,
       Sales,
       Sales+COALESCE((SELECT SUM(Sales) 
                      FROM Sales b 
                      WHERE b.DayCount < a.DayCount),0)
                         AS RunningTotal
FROM Sales a
ORDER BY DayCount

So I altered my query to:


DECLARE
	@PageSize INT,
	@PageNumber INT,
	@FirstRow INT,
	@LastRow INT
   
	SELECT	@PageSize = $pageLength,
	@PageNumber = $pageNumber;
   
	SELECT	@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
	@LastRow = (@PageNumber - 1) * @PageSize + @PageSize;
					
					
	WITH results AS
					
					
	(  SELECT
   
   
   t1.Mortgage_Number,
   t1.Advance_Number,
   t1.Effective_Date,
   t1.Due_Date,
   t1.Transaction_Type,
   t1.Posting_Type,
   t1.Transaction_Amount,
   t1.Account_Source,
   t1.Sub_Posting_Type,
   CASE
	WHEN t2.description IS NOT NULL
	THEN t2.description
	ELSE t1.Transaction_Description
    END AS Transaction_Description,	
	ROW_NUMBER() OVER (ORDER BY [Effective_Date] $direction) AS RowNumber,
	t1.Transaction_Amount+
	       COALESCE((
		       SELECT SUM(t1.Transaction_Amount)		
			   FROM format_transactions AS a
			   WHERE RowNumber <= RowNumber),0)
			   AS RunningTotal   
    FROM format_transactions AS t1
   
    LEFT JOIN sd_posting_types AS t2
    ON  t1.Transaction_Type = t2.Transaction_Type
    AND t1.Posting_Type = t2.Posting_Type
    AND t1.Sub_Posting_Type = t2.Sub_Posting_Type
    AND t1.Account_Source = t2.Account_Type
							
    WHERE (Mortgage_Number = '$mortgage'  $additional_date_params)
							 
   
							
							)
							
   SELECT  
								  
   Mortgage_Number,
   Advance_Number,
   Effective_Date,
   Due_Date,
   Transaction_Type,
   Posting_Type,
   Transaction_Amount,
   Account_Source,
   Sub_Posting_Type,
   Transaction_Description,
   RunningTotal					  
								  
   FROM results
   
   WHERE	RowNumber BETWEEN @FirstRow AND @LastRow
   ORDER BY [Effective_Date] $direction

But I got 'Invalid column name 'RowNumber''

Trouble is, unlike the example, my table does not have a unique sequential field to work from.

Can anyone please see a way for me to create a working solution to my problem?

Many thanks to all those that have replied thus far.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources