We help IT Professionals succeed at work.
Get Started

only select new or changed row sql

118 Views
Last Modified: 2021-04-21
Hi,

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

Open in new window


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.
Comment
Watch Question
IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
Unlock 1 Answer and 11 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE