Geek Answers Handbook

Search for a specific row in another table by date

I have this table:

SELECT * FROM #BH2 BookingID | Detail | CreatedAgentCode | ChangeDate ----------|------------------------------------------------------|------------------|-------------------------- 196162093 | MRS LUCIENE CORREA correa MRS LUCIENE CORREA | lclisboa | 2015-01-18 13:29:35.130 196162093 | MRS LUCIENE CORREA LISBOA MRS LUCIENE CORREA correa | VOMATOS | 2015-01-18 13:25:26.420

And this:

 SELECT * FROM BookingPassengerVersion WHERE BookingID = 196162093 ORDER BY ModifiedDate DESC BookingID | Title | FirstName | MiddleName | LastName | AgentCode | ModifiedDate ----------|-------------------------------------------------------|---------------------------- 196162093 | MRS | LUCIENE | | CORREA | lclisboa | 2015-01-18 13:29:35.130 196162093 | MRS | LUCIENE | CORREA | correa | VOMATOS | 2015-01-18 13:25:26.420 196162093 | MRS | LUCIENE | CORREA | LISBOA | ADM | 2015-01-12 18:01:09.503 196162093 | MRS | LUCIENE | CORREA | LISBOA | ADM | 2015-01-12 18:01:05.227

I need to add a new column in the old name and new name :

I tried this query:

 BEGIN TRY DROP TABLE #FINAL_TABLE END TRY BEGIN CATCH END CATCH SELECT DISTINCT BH.BookingID, -- S OldName, (CASE WHEN _NewName.Title>'' THEN _NewName.Title+' ' ELSE '' END)+_NewName.FirstName+' '+ _NewName.MiddleName+' '+_NewName.LastName AS NewName, BH.CreatedAgentCode, BH.ChangeDate, INTO #FINAL_TABLE FROM #BH2 BH CROSS APPLY ( SELECT TOP 2 Title , FirstName , MiddleName , LastName FROM BookingPassengerVersion WHERE BookingID = BH.BookingID AND BH.ChangeDate = ModifiedDate ORDER BY ModifiedDate DESC ) _NewName

But I could not get this result:

 BookingID | OldName | NewName | Detail | CreatedAgentCode | ChangeDate ----------|---------------------------|---------------------------|-----------------------------------------------------|------------------|-------------------------- 196162093 | MRS LUCIENE CORREA correa | MRS LUCIENE CORREA | MRS LUCIENE CORREA correa MRS LUCIENE CORREA | lclisboa | 2015-01-18 13:29:35.130 196162093 | MRS LUCIENE CORREA LISBOA | MRS LUCIENE CORREA correa | MRS LUCIENE CORREA LISBOA MRS LUCIENE CORREA correa | VOMATOS | 2015-01-18 13:25:26.420

Table # BH2 has a detail column, this column is the old name plus new name .

I need the old name and new name be split into two columns, so I will use the BookingPassengerVersion table, which has a change history.

The name is formed by adding title , first name , middle name and last name .

+5
source share
3 answers

You can try this. SQL Fiddle

 WITH cteBookingPassengerVersion AS ( SELECT BookingID, RTRIM( CONCAT ( ISNULL(Title + ' ', ''), ISNULL(FirstName + ' ', ''), ISNULL(MiddleName + ' ', ''), ISNULL(LastName, '') ) ) AS NAME, ModifiedDate, ROW_NUMBER()OVER(PARTITION BY BookingID ORDER BY ModifiedDate DESC) rowNum FROM BookingPassengerVersion ) SELECT cte.BookingID, ctePrev.NAME AS OldName, cte.NAME AS NewName, bh.Detail, bh.CreatedAgentCode, bh.ChangeDate FROM BH2 bh JOIN cteBookingPassengerVersion cte ON bh.BookingID = cte.BookingID AND bh.ChangeDate = cte.ModifiedDate LEFT JOIN cteBookingPassengerVersion ctePrev ON ctePrev.BookingID = cte.BookingId AND ctePrev.rowNum = cte.rowNum + 1 ORDER BY cte.BookingID, bh.ChangeDate DESC

EDIT I updated the request to join the date and receive all updates for all orders

Update New SQL Script

To filter the CTE by "ReservationID" number in BH2, you can either do

 WITH cteBookingPassengerVersion AS ( SELECT BookingID, RTRIM( CONCAT ( ISNULLLL(Title + ' ', ''), ISNULL(FirstName + ' ', ''), ISNULL(MiddleName + ' ', ''), ISNULL(LastName, '') ) ) AS NAME, ModifiedDate, ROW_NUMBER()OVER(PARTITION BY BookingID ORDER BY ModifiedDate DESC) rowNum FROM BH2 JOIN BookingPassengerVersion ON BH2.BookingID = BookingPassengerVersion.BookingID )

or

 WITH cteBookingPassengerVersion AS ( SELECT BookingID, RTRIM( CONCAT ( ISNULLLL(Title + ' ', ''), ISNULL(FirstName + ' ', ''), ISNULL(MiddleName + ' ', ''), ISNULL(LastName, '') ) ) AS NAME, ModifiedDate, ROW_NUMBER()OVER(PARTITION BY BookingID ORDER BY ModifiedDate DESC) rowNum FROM BookingPassengerVersion WHERE BookingID IN (SELECT BookingID FROM BH2) )

you have to try different things when dealing with large data sets. I would even replace cte with a temp table and see if it helps. Check your execution plan to see if you need any indexes.

temp table instead of cte

 SELECT BookingID, RTRIM( CONCAT ( ISNULLLL(Title + ' ', ''), ISNULL(FirstName + ' ', ''), ISNULL(MiddleName + ' ', ''), ISNULL(LastName, '') ) ) AS NAME, ModifiedDate, ROW_NUMBER()OVER(PARTITION BY BookingID ORDER BY ModifiedDate DESC) rowNum INTO #bpv FROM BookingPassengerVersion WHERE BookingID IN (SELECT BookingID FROM BH2) SELECT cte.BookingID, ctePrev.NAME AS OldName, cte.NAME AS NewName, bh.Detail, bh.CreatedAgentCode, bh.ChangeDate FROM BH2 bh JOIN #bpv cte ON bh.BookingID = cte.BookingID AND bh.ChangeDate = cte.ModifiedDate LEFT JOIN #bpv ctePrev ON ctePrev.BookingID = cte.BookingId AND ctePrev.rowNum = cte.rowNum + 1 ORDER BY cte.BookingID, bh.ChangeDate DESC
+3
source

You can try this in SqlFiddle. I am updating user1221684's answer to remove duplicate rows.

 WITH cteBookingPassengerVersion AS ( SELECT BookingID, RTRIM( CONCAT ( ISNULL(Title + ' ', ''), ISNULL(FirstName + ' ', ''), ISNULL(MiddleName + ' ', ''), ISNULL(LastName, '') ) ) AS NAME, AgentCode, -- add this line ROW_NUMBER()OVER(PARTITION BY BookingID ORDER BY ModifiedDate DESC) rowNum FROM BookingPassengerVersion WHERE BookingID = 196162093 ) SELECT cte.BookingID, ctePrev.NAME AS OldName, cte.NAME AS NewName, bh.Detail, bh.CreatedAgentCode, bh.ChangeDate, cte.rowNum, ctePrev.rowNum FROM BH2 bh JOIN cteBookingPassengerVersion cte ON (bh.BookingID = cte.BookingID and bh.CreatedAgentCode = cte.AgentCode) --Update this line LEFT JOIN cteBookingPassengerVersion ctePrev ON ctePrev.rowNum = cte.rowNum + 1 WHERE cte.rowNum <= 2
+2
source

For this SqlFiddle schema , the expected result:

 BookingID | OldName | NewName | Detail | CreatedAgentCode | ChangeDate ----------|---------------------------|---------------------------|-----------------------------------------------------|------------------|-------------------------- 196162093 | MRS LUCIENE CORREA correa | MRS LUCIENE CORREA | MRS LUCIENE CORREA correa MRS LUCIENE CORREA | lclisboa | 2015-01-18 13:29:35.130 196162093 | MRS LUCIENE CORREA LISBOA | MRS LUCIENE CORREA correa | MRS LUCIENE CORREA LISBOA MRS LUCIENE CORREA correa | VOMATOS | 2015-01-18 13:25:26.420 195668459 | MR CARLOS PERHARDT JUNIOR | MR CARLOS PERHARDT | MR CARLOS PERHARDT JUNIOR MR CARLOS PERHARDT | azbussmann | 2015-01-06 16:36:28.323

CHANGE!

The final solution, I used user1221684 to create a temporary table for "BookingPassengerVersion"

 SELECT * INTO #BPV FROM BookingPassengerVersion WHERE BookingID IN (SELECT DISTINCT BookingID FROM #BH2); WITH cteBookingPassengerVersion AS ( SELECT BookingID, RTRIM( CONCAT ( ISNULL(Title + ' ', ''), ISNULL(FirstName + ' ', ''), ISNULL(MiddleName + ' ', ''), ISNULL(LastName, '') ) ) AS NAME, ModifiedDate, ROW_NUMBER()OVER(PARTITION BY BookingID ORDER BY ModifiedDate DESC) rowNum FROM #BPV ) SELECT cte.BookingID, ctePrev.NAME AS OldName, cte.NAME AS NewName, bh.Detail, bh.CreatedAgentCode, bh.ChangeDate FROM BH2 bh JOIN cteBookingPassengerVersion cte ON bh.BookingID = cte.BookingID AND bh.ChangeDate = cte.ModifiedDate LEFT JOIN cteBookingPassengerVersion ctePrev ON ctePrev.BookingID = cte.BookingId AND ctePrev.rowNum = cte.rowNum + 1 ORDER BY cte.BookingID, bh.ChangeDate DESC
0
source

More articles:


All Articles