SOLVED

SQL 2012 query help on custom audit logs

%3CLINGO-SUB%20id%3D%22lingo-sub-2154149%22%20slang%3D%22en-US%22%3ESQL%202012%20query%20help%20on%20custom%20audit%20logs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2154149%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20some%20help%20creating%20a%20query%20on%20an%20audit%20table%20to%20summarise%20certain%20events.%20In%20the%20sample%20table%20below%2C%20I%20need%20to%20show%20the%20latest%20EntryURL%20for%20each%20user%20along%20with%20the%20Question1%20answered%20by%20each%20user.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EIn%20the%20table%20below%2C%20User_1%20logs%20in%20several%20times%20and%20the%20EntryURL%20can%20be%20different%20for%20each%20login%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CTABLE%20width%3D%22475%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%3EID%3C%2FTD%3E%3CTD%20width%3D%2284%22%3EUser%20ID%3C%2FTD%3E%3CTD%20width%3D%2282%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%22113%22%3EAttribute%3C%2FTD%3E%3CTD%20width%3D%22132%22%3EValue%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3EUser_1%3C%2FTD%3E%3CTD%3E20.02.2021%3C%2FTD%3E%3CTD%3ELogin%3C%2FTD%3E%3CTD%3EUser_1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3EUser_1%3C%2FTD%3E%3CTD%3E20.02.2021%3C%2FTD%3E%3CTD%3EPasswordUpdate%3C%2FTD%3E%3CTD%3E********%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3EUser_1%3C%2FTD%3E%3CTD%3E20.02.2021%3C%2FTD%3E%3CTD%3EEntryURL%3C%2FTD%3E%3CTD%3E%3CA%20href%3D%22http%3A%2F%2Fwww.address.com%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EValue_1%3C%2FA%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3EUser_1%3C%2FTD%3E%3CTD%3E20.02.2021%3C%2FTD%3E%3CTD%3ELogout%3C%2FTD%3E%3CTD%3EUser_1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3EUser_1%3C%2FTD%3E%3CTD%3E20.02.2021%3C%2FTD%3E%3CTD%3ELogin%3C%2FTD%3E%3CTD%3EUser_1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3EUser_1%3C%2FTD%3E%3CTD%3E20.02.2021%3C%2FTD%3E%3CTD%3EEntryURL%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23000080%22%3E%3CA%20href%3D%22http%3A%2F%2Fwww.address.com%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EValue_2%3C%2FA%3E%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3EUser_2%3C%2FTD%3E%3CTD%3E20.02.2021%3C%2FTD%3E%3CTD%3ELogin%3C%2FTD%3E%3CTD%3EUser_2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3EUser_2%3C%2FTD%3E%3CTD%3E20.02.2021%3C%2FTD%3E%3CTD%3EEntryURL%3C%2FTD%3E%3CTD%3E%3CA%20href%3D%22http%3A%2F%2Fwww.new.com%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ewww.new.com%3C%2FA%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3EUser_2%3C%2FTD%3E%3CTD%3E20.02.2021%3C%2FTD%3E%3CTD%3EMiscText%3C%2FTD%3E%3CTD%3ETest%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3EUser_3%3C%2FTD%3E%3CTD%3E20.02.2021%3C%2FTD%3E%3CTD%3ELogin%3C%2FTD%3E%3CTD%3EUser_3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E11%3C%2FTD%3E%3CTD%3EUser_1%3C%2FTD%3E%3CTD%3E20.02.2021%3C%2FTD%3E%3CTD%3ELogout%3C%2FTD%3E%3CTD%3EUser_3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3EUser_1%3C%2FTD%3E%3CTD%3E20.02.2021%3C%2FTD%3E%3CTD%3EQuestion1%26nbsp%3B%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E13%3C%2FTD%3E%3CTD%3EUser_2%3C%2FTD%3E%3CTD%3E20.02.2021%3C%2FTD%3E%3CTD%3EQuestion1%26nbsp%3B%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E14%3C%2FTD%3E%3CTD%3EUser_1%3C%2FTD%3E%3CTD%3E20.02.2021%3C%2FTD%3E%3CTD%3ELogout%3C%2FTD%3E%3CTD%3EUser_1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E15%3C%2FTD%3E%3CTD%3EUser_1%3C%2FTD%3E%3CTD%3E20.02.2021%3C%2FTD%3E%3CTD%3ELogin%3C%2FTD%3E%3CTD%3EUser_1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E16%3C%2FTD%3E%3CTD%3EUser_1%3C%2FTD%3E%3CTD%3E20.02.2021%3C%2FTD%3E%3CTD%3EEntryURL%3C%2FTD%3E%3CTD%3E%3CA%20href%3D%22http%3A%2F%2Fwww.home.com%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ewww.home.com%3C%2FA%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20return%20the%20following%20information%20results%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22513%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2284%22%3EUser_1%3C%2FTD%3E%3CTD%20width%3D%2282%22%3E20.02.2021%3C%2FTD%3E%3CTD%20width%3D%22113%22%3EQuestion1%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22132%22%3E2%3C%2FTD%3E%3CTD%20width%3D%22102%22%3E%3CA%20href%3D%22http%3A%2F%2Fwww.address.com%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EValue_2%3C%2FA%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EUser_2%3C%2FTD%3E%3CTD%3E20.02.2021%3C%2FTD%3E%3CTD%3EQuestion1%26nbsp%3B%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E%3CA%20href%3D%22http%3A%2F%2Fwww.new.com%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ewww.new.com%3C%2FA%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20a%20few%20queries%20but%20just%20can't%20seem%20to%20get%20it%20to%20work.%20Any%20help%20is%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2158193%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%202012%20query%20help%20on%20custom%20audit%20logs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2158193%22%20slang%3D%22en-US%22%3E%3CP%3EYour%20sample%20result%20don't%20match%20your%20description%3B%20you%20asked%20for%20the%20latest%20EntryURL.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20a%20sub-query%20for%20this%2C%20like%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ESELECT%20main.UserID%2C%20main.Date%2C%20main.Attribute%2C%20main.Value%2C%0A%20%20%20%20%20%20%20(SELECT%20TOP%201%20*%0A%20%20%20%20%20%20%20%20FROM%20yourTable%20AS%20sub%0A%20%20%20%20%20%20%20%20WHERE%20sub.UserID%20%3D%20main.UserID%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20AND%20sub.Attribute%20%3D%20'EntryURL'%0A%20%20%20%20%20%20%20%20ORDER%20BY%20sub.ID%20DESC)%20AS%20LastEntryZRL%0AFROM%20yourTable%20AS%20main%0AWHERE%20main.Attribute%20%3D%20'Question1'%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
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