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
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
+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
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. :)
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.
15 People are following this question.
