T-SQL query help 1

[organicg]

I have an AuditDB table that tracks all changes to the DB. Cols like:
ID, TableName, OldVal, NewVal, UpdateDate

I have a 2nd table that tracks user logins.
Cols:
ID, UserName, LoginDT

The tables can't be simply joined on Timestamp. I'd like to return the AuditDB cols and the UserName of the person logged in most recently to the time the row was updated. That is, the UserName of the person most likely to have made the DB change!
I might be close, but this statement won't work because I have 'ddiff' as a 2nd column in my subquery, but I don't know how else to do the calculation.

SELECT db.[AuditID]
      ,db.[Type]
      ,db.[TableName]
      ,db.[PK]
      ,db.[FieldName]
      ,db.[OldValue]
      ,db.[NewValue]
	  ,(SELECT TOP(1) UserName, DATEDIFF(hh, LoginDT,db.[UpdateDate])AS ddiff FROM Audit_LoggedIn ORDER BY ddiff)
      ,db.[UpdateDate]
FROM [Audit_DB] db
WHERE db.[UpdateDate] BETWEEN @StartDateTime AND @EndDateTime
ORDER BY db.[UpdateDate] desc

 

[TheBugSlayer]

Don' t you have to order your subquery in descending order since you are trying to retrieve the latest between Start and End time?

MCITP SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[traingamer]

You can avoid this in the future by simply adding the UserName to the AuditDB table and capturing it at update time.

As for the existing data, try somthing along the lines of this (tested in Access, so the date function may be different)

SELECT AuditDB.TID, AuditDB.UpdateDate, Max(Audit_LoggedIn.LoginDT) AS MaxOfLoginDT
FROM AuditDB, Audit_LoggedIn
WHERE (((DateValue([updatedate]))=DateValue([LoginDT])) AND ((Audit_LoggedIn.LoginDT)<[updateDate]))
GROUP BY AuditDB.TID, AuditDB.UpdateDate;

data:[tt]
TID UpdateDate TableName
2 09/10/2010 12:47:21 PM Test
3 09/10/2010 12:47:25 PM test
4 09/10/2010 12:47:30 PM Test2
5 09/10/2010 12:47:35 PM Test
6 09/10/2010 12:47:41 PM Test
7 09/10/2010 12:50:43 PM Test
8 09/10/2010 12:51:15 PM Test

UID UserName LoginDT
1 TestUser 09/10/2010 11:50:26 AM
2 GjM 09/10/2010 12:50:27 PM
3 Test2 09/10/2010 12:50:32 PM
4 Var 09/10/2010 12:50:53 PM[/tt]

results: [tt]
TID UpdateDate MaxOfLoginDT
2 09/10/2010 12:47:21 PM 09/10/2010 11:50:26 AM
3 09/10/2010 12:47:25 PM 09/10/2010 11:50:26 AM
4 09/10/2010 12:47:30 PM 09/10/2010 11:50:26 AM
5 09/10/2010 12:47:35 PM 09/10/2010 11:50:26 AM
6 09/10/2010 12:47:41 PM 09/10/2010 11:50:26 AM
7 09/10/2010 12:50:43 PM 09/10/2010 12:50:32 PM
8 09/10/2010 12:51:15 PM 09/10/2010 12:50:53 PM
[/tt]
then link using the MaxofLoginDT to get the user name (rather than the UID)


Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[traingamer]

This would get really ugly with big tables. You actually don't need the date link:

SELECT AuditDB.TID, AuditDB.UpdateDate, Max(Audit_LoggedIn.LoginDT) AS MaxOfLoginDT
FROM AuditDB, Audit_LoggedIn
WHERE (((Audit_LoggedIn.LoginDT)<[updateDate]))
GROUP BY AuditDB.TID, AuditDB.UpdateDate;

will work just as well.

None of these will work definitively if more than one person was logged in at a time.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[organicg]

Nice, interesting, thanks a lot. I can't get that last step...getting the username, though. I'm trying this:

SELECT
	db.AuditID, 
	db.UpdateDate, 
	Max(lin.LoginDT) AS MaxOfLoginDT,
	lin.UserName
FROM Audit_DB db, Audit_LoggedIn lin

WHERE (((lin.LoginDT)<[updateDate]))
AND MaxOfLoginDT = lin.LoginDT

GROUP BY db.AuditID, db.UpdateDate;

 

[traingamer]

Not tested - do an inner join on the nested query - this assumes that the login datetime is unique:

SELECT db.[AuditID]
      ,db.[Type]
      ,db.[TableName]
      ,db.[PK]
      ,db.[FieldName]
      ,db.[OldValue]
      ,db.[NewValue]
      ,ALI.LoginDT
      ,ALI.UserName
from [Audit_DB] db
inner join [blue](SELECT AuditDB.AuditID as ABTID, AuditDB.UpdateDate as ABUD, Max(Audit_LoggedIn.LoginDT) AS MaxOfLoginDT
FROM AuditDB, Audit_LoggedIn
WHERE (((Audit_LoggedIn.LoginDT)<[updateDate]))
GROUP BY AuditDB.TID, AuditDB.UpdateDate;) as IQ[/blue]
on IQ.ABTID = db.AuditID
inner join Audit_LoggedIn ALI
on ALI.LoginDT = IQ.MaxOfLoginDT

Have a nice weekend.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard