• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 105
  • Last Modified:

only select new or changed row sql

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.
0
deborahhowson00
Asked:
deborahhowson00
  • 7
  • 4
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
My question is, how do I check the original transaction table for any New or Changed transactions
There's a way to know that? I mean, there's an audit table that log all the changes made? Or a column in the table that has a date with the last modification made to a record?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Worked on your query. Maybe a CTE will give you a better performance rather than a sub-query:
WITH Balance_CTE(Balance)
AS
(SELECT SUM(TransactionAmount)
FROM dbo.tblTenancyTransactions AS D1
	INNER JOIN dbo.tblTenancyTransactions  AS D2 ON D1.TenancySystemRef = D2.TenancySystemRef 
WHERE D1.TransactionNo <= D2.TransactionNo) 

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,B.Balance
FROM dbo.tblTenancyTransactions  AS D2 
	INNER JOIN dbo.DimTenancy ON D2.TenancySystemRef = dbo.DimTenancy.TenancySystemRef 
	INNER JOIN Balance_CTE AS B
ORDER BY dbo.DimTenancy.TenancySystemRef, D2.TransactionNo DESC

Open in new window

And if you can get ride of the ORDER BY clause will help a lot.
0
 
deborahhowson00Author Commented:
No afraid not.  
Only way to find out if it's new is to compare the last transno for that sysref and import anything greater than that so think that would cover the 'new' transactions.

For the changed transactions there is a from and to date and current indicator as shown below, this is all on the same sysref...

TransactionNo	TransactionWeek	TransactionYear	TransactionAmount	TransactionDate	AccountCode	FromDate	ToDate	Curr
861	29	1314	-90.3	2013-10-14 00:00:00.000	PO	2013-10-15 19:51:29.000	NULL	1
862	30	1314	90.3	2013-10-21 00:00:00.000	DEBIT	2013-10-20 12:43:55.000	NULL	1
863	30	1314	-90.3	2013-10-21 00:00:00.000	PO	2013-10-22 12:49:35.000	NULL	1
864	31	1314	90.3	2013-10-28 00:00:00.000	DEBIT	2013-10-27 12:44:28.000	NULL	1
865	31	1314	-90.3	2013-10-28 00:00:00.000	PO	2013-10-29 12:57:38.000	NULL	1
866	32	1314	90.3	2013-11-04 00:00:00.000	DEBIT	2013-11-03 12:43:26.000	NULL	1
867	32	1314	-90.3	2013-11-04 00:00:00.000	PO	2013-11-05 12:46:04.000	NULL	1
868	33	1314	90.3	2013-11-11 00:00:00.000	DEBIT	2013-11-10 12:44:47.000	2013-11-24 22:47:30.000	0
868	33	1314	90.3	2013-11-11 00:00:00.000	DEBIT	2013-11-24 22:47:30.000	NULL	1
869	33	1314	-90.3	2013-11-11 00:00:00.000	PO	2013-11-12 12:59:03.000	2013-11-24 22:47:30.000	0
869	33	1314	-90.3	2013-11-11 00:00:00.000	PO	2013-11-24 22:47:30.000	NULL	1
870	34	1314	90.3	2013-11-18 00:00:00.000	DEBIT	2013-11-17 20:08:53.000	2013-11-24 22:47:30.000	0
870	34	1314	90.3	2013-11-18 00:00:00.000	DEBIT	2013-11-24 22:47:30.000	NULL	1
871	34	1314	-90.3	2013-11-18 00:00:00.000	PO	2013-11-19 12:58:42.000	2013-11-24 22:47:30.000	0
871	34	1314	-90.3	2013-11-18 00:00:00.000	PO	2013-11-24 22:47:30.000	NULL	1

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
My suggestion is to partitionate the table so the new records and possible the updated also, can be in one partition so you'll only need to query a partition instead of all table.
0
 
deborahhowson00Author Commented:
Thanks for the cte but it gives me this error

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'B'.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, I forgot the JOIN fields:
WITH Balance_CTE(TenancySystemRef, Balance)
AS
(SELECT D1.TenancySystemRef, SUM(TransactionAmount)
FROM dbo.tblTenancyTransactions AS D1
	INNER JOIN dbo.tblTenancyTransactions  AS D2 ON D1.TenancySystemRef = D2.TenancySystemRef 
WHERE D1.TransactionNo <= D2.TransactionNo) 

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,B.Balance
FROM dbo.tblTenancyTransactions  AS D2 
	INNER JOIN dbo.DimTenancy ON D2.TenancySystemRef = dbo.DimTenancy.TenancySystemRef 
	INNER JOIN Balance_CTE AS B ON D2.TenancySystemRef = B.TenancySystemRef 
ORDER BY dbo.DimTenancy.TenancySystemRef, D2.TransactionNo DESC

Open in new window

0
 
deborahhowson00Author Commented:
Doesn't work, I get 68 instances of every transaction number.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
It's the problem with can't test. GROUP BY missing:
WITH Balance_CTE(TenancySystemRef, Balance)
AS
(SELECT D1.TenancySystemRef, SUM(TransactionAmount)
FROM dbo.tblTenancyTransactions AS D1
	INNER JOIN dbo.tblTenancyTransactions  AS D2 ON D1.TenancySystemRef = D2.TenancySystemRef 
WHERE D1.TransactionNo <= D2.TransactionNo
GROUP BY D1.TenancySystemRef) 

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,B.Balance
FROM dbo.tblTenancyTransactions  AS D2 
	INNER JOIN dbo.DimTenancy ON D2.TenancySystemRef = dbo.DimTenancy.TenancySystemRef 
	INNER JOIN Balance_CTE AS B ON D2.TenancySystemRef = B.TenancySystemRef 
ORDER BY dbo.DimTenancy.TenancySystemRef, D2.TransactionNo DESC

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why the grade C?
0
 
deborahhowson00Author Commented:
Because the answer you gave although an answer of sorts, isn't actually the answer to my original question.  Hope that makes sense.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Don't makes sense at all closing a question without having the right answer.
I suggested the partition table but seems like you didn't work on it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now