Solved

only select new or changed row sql

Posted on 2014-12-08
11
66 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
  • 7
  • 4
11 Comments
 
LVL 45

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 45

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
 
LVL 45

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 45

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 45

Accepted Solution

by:
Vitor Montalvão earned 500 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 45

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 45

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now