CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlsql-server-2008-r2
DataCrypt
DataCrypt

Reputation: 307

Query two tables to obtain list with most recent date?

I have two tables in SQL Server 2008 R2. One table is a lookup table that holds a Service description and the other table holds service history for a client. I need to write a query to list all of the service types and when they were last used. Here are the fields in the two tables that I need to work with: 

Table1: Code_Service
ULink
Description

Table2: Service_History
ServiceTypeULink
RecordUpdateDateTime

Table1 data:
ULink                                       Description
037dc811-4d56-XXXX-XXXX-XXXXXXXXXc          Dental
5589e876-9c48-XXXX-XXXX-XXXXXXXXXd          Surgical Procedure
177rt522-2s96-XXXX-XXXX-XXXXXXXXXa          Specialty

Table2 data:
ServiceTypeULink                            RecordUpdateDateTime
037dc811-4d56-XXXX-XXXX-XXXXXXXXXc          10/11/2014 12:38:02 PM
5589e876-9c48-XXXX-XXXX-XXXXXXXXXd          4/21/2013 10:45:28 AM
177rt522-2s96-XXXX-XXXX-XXXXXXXXXa          11/22/2015 11:37:35 AM

The ULink and ServiceTypeULink fields are what relates them. So, I need to query for all the "Code_Service.Description" used in Service_History which have the most recent "RecordUpdateDateTime". I wrote the following query and it seems to work, but not sure if this is correct (or a better/faster) way to do it considering I'm on SQL Server 2008.

SELECT
  Code_Service.Description
 ,MAX(Service_History.RecordUpdatedDateTime) AS DateLastUsed
FROM dbo.Service_History
LEFT OUTER JOIN dbo.Code_Service
  ON Service_History.ServiceTypeULink = Code_Service.ULink
GROUP BY Code_Service.Description
ORDER BY DateLastUsed DESC

Thank you

Upvotes: 0

Views: 45

Answers (1)

JohnHC
JohnHC

Reputation: 11195

That will do it, but if you need any more info from either table, you can also use:

with MyCTE as
(
SELECT
  Code_Service.Description
 ,Service_History.RecordUpdatedDateTime
 -- add any other columns needed in here --
 ,row_number() over (partition by ServiceTypeULink 
                     order by RecordUpdatedDateTime desc) as R_ORD
FROM dbo.Service_History
LEFT OUTER JOIN dbo.Code_Service
  ON Service_History.ServiceTypeULink = Code_Service.ULink
)
select *
from MyCTE
where R_ORD = 1

Upvotes: 1

Related Questions