select last record customer record
-
Hi, I have an application database that creates a record with the action and timestamp everytime the end user of the application modifies a customer record, like a historica audit trail. What I'd like to do is create a query that will show me only the last transaction of each record based on the latest 'event time' of that action.
I have been trying to use the following query but it is still giving me all transactions on each record:
SELECT MAX(ID), CUSTPHONENO, EVENTTIME, DUEDATE, ASSIGNEDFORDATE
from tbl_table1
group by ID, CUSTPHONENO, EVENTTIME, DUEDATE, ASSIGNEDFORDATE
Does anyone have any other ideas on how to do this?
Thank you in advance.
Marcus.
-
We had a similar problem and solved it, nicely, like this:
SELECT...
FROM Table1
JOIN Table2
on table2.PK = table1.Pk
and table2.AuditDate = (SELECT TOP(1) t2.AuditDate
FROM t2.AuditDate t2
WHERE t2.Pk = Table2.Pk
ORDER BY t2.AuditDate DESC)
WHERE...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Maybe this
WITH CTE AS
(SELECT ID, CUSTPHONENO, EVENTTIME, DUEDATE, ASSIGNEDFORDATE,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY EVENTTIME DESC) as rn
FROM tbl_table1)
SELECT ID, CUSTPHONENO, EVENTTIME, DUEDATE, ASSIGNEDFORDATE
FROM CTE
WHERE rn=1
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
We also did some this way:
SELECT...
FROM Table x
CROSS APPLY (SELECT TOP(1) whatever..
FROM TABLE
WHERE pk's match
ORDER BY version desc) AS v
WHERE...
It worked nicely too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Markc that worked perfectly, I just started using cte's recently and they are great! I'll have to look at your script in more detail to get a real understanding of what you did but thank you.
Grant thank you for your time, I tried your solution but was unable to apply it properly. In your solution, were you doing a self join on the same table? and I wasn't sure what to put in the where clause after the last line - 'WHERE...'
again thank you both.
-
Sorry, I just plucked it from the middle of existing code. Our system has one table that doesn't have versions and all the rest do. So the query included the join. All the elipsis were for whatever code you needed to stick in there. Sorry it didn't help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
no worries Grant, I got what I wanted and learned something new so I'm happy.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply