Forum Discussion
Solved
SQL 2012 query help on custom audit logs
I need some help creating a query on an audit table to summarise certain events. In the sample table below, I need to show the latest EntryURL for each user along with the Question1 answered by each ...
That's just a small modification of the sub-query: Compare on Date and the ID must be small the the one of "Question1" entry
SELECT main.UserID, main.Date, main.Attribute, main.Value, (SELECT TOP 1 sub.Value FROM yourTable AS sub WHERE sub.UserID = main.UserID AND sub.Date = main.Date AND sub.Attribute = 'EntryURL' AND sub.ID < main.ID ORDER BY sub.ID DESC) AS LastEntryZRL FROM yourTable AS main WHERE main.Attribute = 'Question1'
Your sample result don't match your description; you asked for the latest EntryURL.
You can use a sub-query for this, like
SELECT main.UserID, main.Date, main.Attribute, main.Value,
(SELECT TOP 1 *
FROM yourTable AS sub
WHERE sub.UserID = main.UserID
AND sub.Attribute = 'EntryURL'
ORDER BY sub.ID DESC) AS LastEntryZRL
FROM yourTable AS main
WHERE main.Attribute = 'Question1'Thanks for the reply. Sorry, I wasn't clear on my requirements. I'll try again: I need to include the last EntryURL value that precedes Question1 for each user and date. So for user_1 this would be Value_2 and not www.home.com.
Hope that makes sense.
ThanksThat's just a small modification of the sub-query: Compare on Date and the ID must be small the the one of "Question1" entry
SELECT main.UserID, main.Date, main.Attribute, main.Value, (SELECT TOP 1 sub.Value FROM yourTable AS sub WHERE sub.UserID = main.UserID AND sub.Date = main.Date AND sub.Attribute = 'EntryURL' AND sub.ID < main.ID ORDER BY sub.ID DESC) AS LastEntryZRL FROM yourTable AS main WHERE main.Attribute = 'Question1'
