SQL 2012 query help on custom audit logs

[JasR09]

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 user.

In the table below, User_1 logs in several times and the EntryURL can be different for each login

ID	User ID	Date	Attribute	Value
1	User_1	20.02.2021	Login	User_1
2	User_1	20.02.2021	PasswordUpdate	********
3	User_1	20.02.2021	EntryURL	Value_1
4	User_1	20.02.2021	Logout	User_1
5	User_1	20.02.2021	Login	User_1
6	User_1	20.02.2021	EntryURL	Value_2
7	User_2	20.02.2021	Login	User_2
8	User_2	20.02.2021	EntryURL	www.new.com
9	User_2	20.02.2021	MiscText	Test
10	User_3	20.02.2021	Login	User_3
11	User_1	20.02.2021	Logout	User_3
12	User_1	20.02.2021	Question1	2
13	User_2	20.02.2021	Question1	4
14	User_1	20.02.2021	Logout	User_1
15	User_1	20.02.2021	Login	User_1
16	User_1	20.02.2021	EntryURL	www.home.com

I need to return the following information results

User_1	20.02.2021	Question1	2	Value_2
User_2	20.02.2021	Question1	4	www.new.com

I've tried a few queries but just can't seem to get it to work. Any help is greatly appreciated.

Thanks

[#2]

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'

[#3]

@olafhelper 

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.

Thanks

[#4 · Accepted solution]

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'

[#5]

@olafhelper 

Thanks for the query. Seems to work as expected.

Many thanks