WITH base
AS (SELECT dbo.Fn_decrypt(fcuname)
user_name,
fcsessionid,
fcmodid,
fccompno,
clienttype,
fccompno
company_number,
LEFT(compname, Charindex('#', compname) - 1)
server_name,
Substring(compname, Charindex('#', compname) + 1, Len(compname))
client_name
FROM m2msystem.dbo.utscreen
LEFT JOIN utulog
ON utscreen.fcsessionid = utulog.fcsessid),
unique_sessions
AS (SELECT DISTINCT user_name,
fcsessionid
FROM base
WHERE clienttype NOT LIKE '%BCP%'),
session_count
AS (SELECT user_name,
Count(user_name) total_session_count
FROM unique_sessions
GROUP BY user_name),
unique_screens
AS (SELECT user_name,
Count(user_name) total_screen_count
FROM base
WHERE clienttype NOT LIKE '%BCP%'
GROUP BY user_name,
fcsessionid)
SELECT DISTINCT sc.*,
b.company_number,
us.total_screen_count,
b.server_name,
b.client_name
FROM session_count sc
INNER JOIN unique_screens us
ON sc.user_name = us.user_name
INNER JOIN base b
ON sc.user_name = b.user_name
ORDER BY total_session_count DESC,
total_screen_count DESC
/*
*/
