troubleshooting Question

only select new or changed row sql

Avatar of deborahhowson00
deborahhowson00 asked on
Microsoft SQL ServerSQL
11 Comments1 Solution119 ViewsLast Modified:

I have a very large table of transactions, an example of some data would be:

SysRef  TransNo, TransYear, TransDate, TransAmount
1              1           2014           01/12/2014   30.00                    
1              2           2014           02/12/2014   10.00                    
2              1           2013           01/12/2013   10.00                    
2              2           2014           02/12/2014   20.00                    

I currently have run the following query to give me the above with the Balance, as the Balance is NOT stored anywhere in the database, it is something you have to calculate.

SELECT     dbo.DimTenancy.tenancyref, dbo.DimTenancy.rentgroup, D2.accountcode, D2.TransactionNo, D2.TransactionWeek, D2.TransactionYear, D2.PostingWeek, D2.PostingYear,
                      D2.accounttype, D2.TenancySystemRef, dbo.DimTenancy.CurrentBalance, 
                      D2.TransactionAmount, D2.CreatedDate, D2.TransactionDate,  
                      dbo.DimTenancy.placeref, dbo.DimTenancy.TenancyEnd, dbo.DimTenancy.TenancyStart,
 (SELECT SUM(TransactionAmount)
	FROM dbo.tblTenancyTransactions AS D1
	WHERE D1.TenancySystemRef = D2.TenancySystemRef AND D1.TransactionNo <= D2.TransactionNo) as Balance
FROM                     dbo.tblTenancyTransactions  AS D2 INNER JOIN
                      dbo.DimTenancy ON D2.TenancySystemRef = dbo.DimTenancy.TenancySystemRef 

ORDER BY dbo.DimTenancy.TenancySystemRef, D2.TransactionNo DESC

This would give me
SysRef  TransNo, TransYear, TransDate, TransAmount, Balance
1              1           2014           01/12/2014   30.00                    30.00
1              2           2014           02/12/2014   10.00                    40.00
2              1           2013           01/12/2013   10.00                    10.00
2              2           2014           02/12/2014   20.00                    30.00

These results are huge and are stored in a table called 'Balances'.  

My question is, how do I check the original transaction table for any New or Changed transactions and add them or amend them to the Balance table?

The original takes ages to load due to the millions of transactions so want to cut down on this processing time.

Thanks in advance.
Vitor Montalvão
IT Engineer
Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros