Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

only select new or changed row sql

Posted on 2014-12-08
11
Medium Priority
?
91 Views
Last Modified: 2014-12-16
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
Comment
Question by:deborahhowson00
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40486822
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
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40486835
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
 

Author Comment

by:deborahhowson00
ID: 40486851
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40486862
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
 

Author Comment

by:deborahhowson00
ID: 40486871
Thanks for the cte but it gives me this error

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'B'.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40486930
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
 

Author Comment

by:deborahhowson00
ID: 40487001
Doesn't work, I get 68 instances of every transaction number.
0
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 1000 total points
ID: 40487036
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
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40502757
Why the grade C?
0
 

Author Comment

by:deborahhowson00
ID: 40502842
Because the answer you gave although an answer of sorts, isn't actually the answer to my original question.  Hope that makes sense.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40502852
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question