This can be used to get the most frequent logon account in the last 60 days.
-- First query: Get the most frequent logon user for each AssetID
WITH LogonCounts
AS (SELECT AssetID,
username,
COUNT(*) AS logon_count,
ROW_NUMBER() OVER (PARTITION BY AssetID ORDER BY COUNT(*) DESC) AS rn
FROM tblCPlogoninfo
WHERE logontime >= DATEADD(DAY, -60, GETDATE())
GROUP BY AssetID,
username)
SELECT AssetID,
username AS PrimaryLogin,
logon_count
INTO #tempLogonInfo
FROM LogonCounts
WHERE rn = 1
ORDER BY AssetID;
