Forum Discussion

JasR09's avatar
JasR09
Copper Contributor
Feb 21, 2021

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

  • olafhelper's avatar
    olafhelper
    Feb 23, 2021

    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's avatar
    olafhelper
    Bronze Contributor
    Feb 23, 2021

    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'
    • JasR09's avatar
      JasR09
      Copper Contributor
      Feb 23, 2021

      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

      • olafhelper's avatar
        olafhelper
        Bronze Contributor
        Feb 23, 2021

        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'

Resources