deborahhowson00
asked on
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.
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.
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 DESCThis 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
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?
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 DESCASKER
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...
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
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.
ASKER
Thanks for the cte but it gives me this error
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'B'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'B'.
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 DESCASKER
Doesn't work, I get 68 instances of every transaction number.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Why the grade C?
ASKER
Because the answer you gave although an answer of sorts, isn't actually the answer to my original question. Hope that makes sense.
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.
I suggested the partition table but seems like you didn't work on it.