• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 750
  • Last Modified:

SQL - Get the latest records

I have a table with columns

TimeStamp
FirstName
LastName
EmployeeID (Primary Key)
LastInsuranceClaimAmount

I like to create a query to generate a table of the latest claim for each employee since Jan 1, 2013.  In the table there can be more than one records related to a EmployeeID. Records before Jan 1, 2013 will not be take into consideration.

The result should have the same column as the above table.

Can anyone give me an idea how to do that? Thanks.
0
tommym121
Asked:
tommym121
  • 3
  • 3
  • 3
2 Solutions
 
chaauCommented:
If I understood you correctly, this is what you need to do:

;WITH cte AS
(
Select EmployeeID,
[TimeStamp],
ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY [TimeStamp] DESC) rn
FROM Table1
)
Select t.[TimeStamp],
t.FirstName,
t.LastName,
t.EmployeeID,
t.LastInsuranceClaimAmount FROM Table1 t INNER JOIN CTE c ON c.EmployeeID=t.EmployeeID AND c.[TimeStamp] = t.[TimeStamp] AND c.rn=1
WHERE t.[TimeStamp] >= '20130101'

Open in new window

0
 
chaauCommented:
BTW, to create a table from SELECT statement, just add an INTO clause, like this:

;WITH cte AS
(
Select EmployeeID,
[TimeStamp],
ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY [TimeStamp] DESC) rn
FROM Table1
)
Select t.[TimeStamp],
t.FirstName,
t.LastName,
t.EmployeeID,
t.LastInsuranceClaimAmount 
INTO NewTable
FROM Table1 t INNER JOIN CTE c ON c.EmployeeID=t.EmployeeID AND c.[TimeStamp] = t.[TimeStamp] AND c.rn=1
WHERE t.[TimeStamp] >= '20130101'

Open in new window

0
 
PortletPaulCommented:
Note that in SQL Server a timestamp data type isn't actually related to dates or times
(and Microsoft now prefer then to be referred to a "rowversion")
The rowversion data type is just an incrementing number and does not preserve a date or a time. ...
This binary data type provides uniqueness for each record but isn't guaranteed to produce "the latest" (but probably will suffice for that).

However you can't use [timestamp] against a date in the where clause such as >= '20130101'

Can you confirm the actual data type is "timestamp"?
Do you have any other date/datetime fields on the table you can use?
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
tommym121Author Commented:
My timestamp is generated using the GetDate()
0
 
PortletPaulCommented:
ok then my comments don't apply,
but you probably should not use the word 'timestamp' as a fieldname :)
0
 
chaauCommented:
That's why I have exposed it in brackets in my answer
0
 
PortletPaulCommented:
:) agreed, that certainly is needed.

But it's sort of worse than just using a reserved word because "timestamp" is currently so misleading in SQL Server Plus, at sometime in the future MS will adopt the standard meaning I believe - which will be a small nightmare in itself. Better IMHO to avoid using that term as a field name whenever possible.

time_stamp might be an alternative?
0
 
tommym121Author Commented:
PortierPaul, thanks to straighten me on terms.
0
 
tommym121Author Commented:
Thank you
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now