Union two tables and get most recent client number

Question

Post reply

Union two tables and get most recent client number

Wainaps

SSC Journeyman

Points: 81
More actions
November 15, 2010 at 7:36 pm

#241236

I have two tables that I have already joined on the needed columns and made into a view. From this view I am trying to write a query to return only one client number for each record (people may have > 1 record) but it has to be the most recent record. The most current record can be found using the audit_date_time column.
Please share your ideas. Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

MAX(Audit_Date_Time) GROUP BY ClientNumber;

TOP (1) ORDER BY Audit_Date_Time DESC

SELECT ...

RN = ROW_NUMBER() OVER (PARTITION BY ClientNumber ORDER BY Audit_Date_Time DESC)

FROM view

WHERE RN = 1;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Wainaps SSC Journeyman Points: 81 More actions · Answer 2

Thanks Wayne! I'll be trying it with those ingredients. I was already using MAX GROUP BY and Top 1, but not PARTITION BY

Wainaps SSC Journeyman Points: 81 More actions · Answer 3

I was able to accomplish it by doing a view upon another view. Thanks for Wayne for the input! 🙂

CREATE VIEW map_people AS

SELECT [CLIENT_NUMBER]

,[CLIENT_SURNAME]

,[CLIENT_GIVEN_NAME]

,[CLIENT_INITIALS]

,[SEX]

,[DATE_OF_BIRTH]

,[CLT_DEATH_DATE]

....

,[AUDIT_DATE_TIME]

,ROW_NUMBER() OVER(PARTITION BY Client_number ORDER BY Audit_Date_Time DESC) as Total_Client_NUMBER

FROM people_union

GO

SELECT [CLIENT_NUMBER]

,[CLIENT_SURNAME]

,[CLIENT_GIVEN_NAME]

,[CLIENT_INITIALS]

,[SEX]

,[DATE_OF_BIRTH]

,[CLT_DEATH_DATE]

,[MARITAL_STATUS]

,....

,[AUDIT_DATE_TIME]

FROM map_people

WHERE TOTAL_CLIENT_NUMBER = 1

ORDER BY client_number

WayneS SSC Guru Points: 95460 More actions · Answer 4

Note that you can accomplish this with a CTE:

;WITH CTE AS

(

SELECT [CLIENT_NUMBER],

[CLIENT_SURNAME],

[CLIENT_GIVEN_NAME],

[CLIENT_INITIALS],

[SEX],

[DATE_OF_BIRTH],

[CLT_DEATH_DATE],

....,

[AUDIT_DATE_TIME],

ROW_NUMBER() OVER(PARTITION BY Client_number ORDER BY Audit_Date_Time DESC) as Total_Client_NUMBER

FROM people_union

)

SELECT [CLIENT_NUMBER],

[CLIENT_SURNAME],

[CLIENT_GIVEN_NAME],

[CLIENT_INITIALS],

[SEX],

[DATE_OF_BIRTH],

[CLT_DEATH_DATE],

[MARITAL_STATUS],

....,

[AUDIT_DATE_TIME]

FROM CTE

WHERE TOTAL_CLIENT_NUMBER = 1

ORDER BY client_number;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Wainaps SSC Journeyman Points: 81 More actions · Answer 5

Wainaps

SSC Journeyman

Points: 81

November 16, 2010 at 11:43 am

#1251515

Cool, thanks Wayne!

WayneS SSC Guru Points: 95460 More actions · Answer 6

Welcome.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2