Hi guy's!
I'm currently trying to satisfy my bosses need for reports and now I have the unfortunate task of writing a report that lists all the users (and assets) that logged in between 12:30 and 17:00 on the current day.
Now I've managed to make a report that seems to do that, but while testing I noticed that some people's logins aren't listed (myself included), even tho they did login within the chosen timeframe...
Since I'm a bit of a noob when it comes to SQL i have to admit... I have no idea what's wrong and that's the reason I'm asking for your help!
Here's the code:
Select Top 1000000 SubQuery.Username,
SubQuery.Domain As Userdomain,
SubQuery.LastLogon As [Letztes Login],
tblAssets.AssetName,
tblAssets.Domain
From tblCPlogoninfo
Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) SubQuery On tblCPlogoninfo.Username =
SubQuery.Username And tblCPlogoninfo.Domain = SubQuery.Domain And
tblCPlogoninfo.logontime = SubQuery.LastLogon
Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID
Where Convert(date,SubQuery.LastLogon,104) = Convert(date,GetDate(),104) And
Convert(time(0),SubQuery.LastLogon) > Cast('12:30:00.000000' As TIME)
Thanks for your help in advance and have a nice day!
