question

Bam avatar image
Bam asked

looping update from two tables

hey guys I have a problem I cant seem to get my head around. I have two tables: Account status: Account ID Status Date 1 logged 1/02/2012 1 active 5/12/2013 1 closed 31/03/2014 2 logged 4/09/2013 2 active 9/10/2014 2 declined 11/12/2014 2 closed 24/12/2014 3 logged 2/03/2013 Account Transactions Account ID TransactionDate 1 3/03/2012 1 4/09/2013 1 2/01/2014 1 20/03/2014 2 5/10/2013 2 12/04/2014 2 13/07/2014 2 14/09/2014 2 15/10/2014 2 16/12/2014 3 4/05/2013 3 23/10/2014 I need a script that will merge the two tables into a new one that shows all the account transactions but also shows the account status at that date. I was thinking that a loop would be required of some sort where for each transaction it could select the status that fits within the date. I cant seem to figure out how I would do it though. Can anyone help out? I don't mind if its either a select script that displays the results as I need it or an update script that creates a new table with the results. Thanks if you can help out. Phil
scriptloop
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Tom Staab avatar image
Tom Staab answered
I recently created a new database on my home computer just to have a sandbox for helping with questions like this on this site. This was my first use of that database, and I'm very glad I did it. I much prefer being able to test my answers before posting them so I know I have both the syntax and logic correct. Having said that, this is what I used for the test: CREATE SCHEMA Q117043; GO CREATE TABLE Q117043.AccountStatus ( Id int IDENTITY(1,1) , AccountId int , StatusCode varchar(20) , StatusDate date , CONSTRAINT [Q117043.AccountStatus.Id_PK] PRIMARY KEY CLUSTERED (Id) ); CREATE TABLE Q117043.AccountTransactions ( Id int IDENTITY(1,1) , AccountId int , TransactionDate date , CONSTRAINT [Q117043.AccountTransactions.Id_PK] PRIMARY KEY CLUSTERED (Id) ); GO INSERT Q117043.AccountStatus (AccountId, StatusCode, StatusDate) VALUES ('1', 'logged', CONVERT(date, '1/02/2012', 103)) , ('1', 'active', CONVERT(date, '5/12/2013', 103)) , ('1', 'closed', CONVERT(date, '31/03/2014', 103)) , ('2', 'logged', CONVERT(date, '4/09/2013', 103)) , ('2', 'active', CONVERT(date, '9/10/2014', 103)) , ('2', 'declined', CONVERT(date, '11/12/2014', 103)) , ('2', 'closed', CONVERT(date, '24/12/2014', 103)) , ('3', 'logged', CONVERT(date, '2/03/2013', 103)) ; INSERT Q117043.AccountTransactions (AccountId, TransactionDate) VALUES ('1', CONVERT(date, '3/03/2012', 103)) , ('1', CONVERT(date, '4/09/2013', 103)) , ('1', CONVERT(date, '2/01/2014', 103)) , ('1', CONVERT(date, '20/03/2014', 103)) , ('2', CONVERT(date, '5/10/2013', 103)) , ('2', CONVERT(date, '12/04/2014', 103)) , ('2', CONVERT(date, '13/07/2014', 103)) , ('2', CONVERT(date, '14/09/2014', 103)) , ('2', CONVERT(date, '15/10/2014', 103)) , ('2', CONVERT(date, '16/12/2014', 103)) , ('3', CONVERT(date, '4/05/2013', 103)) , ('3', CONVERT(date, '23/10/2014', 103)) ; Being in the United States, your dates messed me up at first, but converting with date style 103 (British/French) did the trick. Once i had the dataset, I came up with 2 potential solutions. I believe the first will perform better, but the second is sometimes better in some cases (like if you have to get the min or max of a combination of 2 columns). WITH CurrentAndPreviousStatuses AS ( SELECT t.AccountId, t.TransactionDate , MostRecentStatusDate = MAX(s.StatusDate) FROM Q117043.AccountTransactions t INNER JOIN Q117043.AccountStatus s ON s.AccountId = t.AccountId AND s.StatusDate
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 I had started to write an answer, but I got distracted and it took me over 8 hours to return....my answer was along the lines as your second solution. Oh, and welcome back Tom
0 Likes 0 ·
Thanks, Kev. It's been far too long, but I'm very happy to once again be active here. I need to stop letting life get in the way of, well, life. :)
0 Likes 0 ·
Thanks heaps! That worked well however the end result loses alot of transaction records. I'm looking into why but if you could take a look would be good. I used the first method.
0 Likes 0 ·
Try changing both inner joins to left joins. I thought of this scenario earlier, and now I'm sorry I didn't mention it. With inner joins, I was assuming you always had a known account status before any transactions for that account. The left join will give you every transaction and a null if there is no known status at that time.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.