/*
This script utilises the LoginTrail table to check concurrent user logins over hour blocks.

It can be a factor in understanding the usage of the system /load on the CMO servers.
*/

IF OBJECT_ID('tempdb..#LoginDetails') IS NOT NULL
    DROP TABLE #LoginDetails

SELECT count(DISTINCT userid) AS [Distinct logins]
    ,CONVERT(DATETIME, CAST(DATEPART(YEAR, DATEADD(HOUR, 10, LoginDateTime)) AS NVARCHAR(4)) + '-' + CAST(DATEPART(MONTH, DATEADD(HOUR, 10, LoginDateTime)) AS NVARCHAR(2)) + '-' + CAST(DATEPART(DAY, DATEADD(HOUR, 10, LoginDateTime)) AS NVARCHAR(2)) + ' ' + CAST(DATEPART(HOUR, DATEADD(HOUR, 10, LoginDateTime)) AS NVARCHAR(2)) + ':00:00.000') AS [Hour Block - Melbourne Time]
INTO #LoginDetails
FROM LoginTrail l
GROUP BY CONVERT(DATETIME, CAST(DATEPART(YEAR, DATEADD(HOUR, 10, LoginDateTime)) AS NVARCHAR(4)) + '-' + CAST(DATEPART(MONTH, DATEADD(HOUR, 10, LoginDateTime)) AS NVARCHAR(2)) + '-' + CAST(DATEPART(DAY, DATEADD(HOUR, 10, LoginDateTime)) AS NVARCHAR(2)) + ' ' + CAST(DATEPART(HOUR, DATEADD(HOUR, 10, LoginDateTime)) AS NVARCHAR(2)) + ':00:00.000')
ORDER BY CONVERT(DATETIME, CAST(DATEPART(YEAR, DATEADD(HOUR, 10, LoginDateTime)) AS NVARCHAR(4)) + '-' + CAST(DATEPART(MONTH, DATEADD(HOUR, 10, LoginDateTime)) AS NVARCHAR(2)) + '-' + CAST(DATEPART(DAY, DATEADD(HOUR, 10, LoginDateTime)) AS NVARCHAR(2)) + ' ' + CAST(DATEPART(HOUR, DATEADD(HOUR, 10, LoginDateTime)) AS NVARCHAR(2)) + ':00:00.000') DESC

SELECT *
FROM #LoginDetails l
WHERE l.[Hour Block - Melbourne Time] > '2020-07-01'
ORDER BY l.[Hour Block - Melbourne Time] DESC