SOLVED

SQL 2012 query help on custom audit logs

New Contributor

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

IDUser IDDateAttributeValue
1User_120.02.2021LoginUser_1
2User_120.02.2021PasswordUpdate********
3User_120.02.2021EntryURLValue_1
4User_120.02.2021LogoutUser_1
5User_120.02.2021LoginUser_1
6User_120.02.2021EntryURLValue_2
7User_220.02.2021LoginUser_2
8User_220.02.2021EntryURLwww.new.com
9User_220.02.2021MiscTextTest
10User_320.02.2021LoginUser_3
11User_120.02.2021LogoutUser_3
12User_120.02.2021Question1 2
13User_220.02.2021Question1 4
14User_120.02.2021LogoutUser_1
15User_120.02.2021LoginUser_1
16User_120.02.2021EntryURLwww.home.com

 

I need to return the following information results

 

 

User_120.02.2021Question1 2Value_2
User_220.02.2021Question1 4www.new.com

 

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

 

Thanks

4 Replies

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'

@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

best response confirmed by JasR09 (New Contributor)
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'

@olafhelper 

Thanks for the query. Seems to work as expected.

 

Many thanks