Guest User

Untitled

a guest
Jun 16th, 2019
59
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select Time_Stamp_Hour=dateadd(hh,datepart(hh,StartDateTime), cast(CAST(StartDateTime as date) as datetime)), Count(*) as amount
  2. from dbo.Sessions
  3. group by dateadd(hh,datepart(hh,StartDateTime), cast(CAST(StartDateTime as date) as datetime))
  4. order by Time_Stamp_Hour desc
  5.  
  6. CREATE TABLE #Hours (Hour INT)
  7.  
  8. ;WITH CTE AS (
  9. SELECT 1 AS Level
  10. UNION ALL
  11. SELECT 1+Level
  12. FROM CTE
  13. WHERE Level <= 23
  14. )
  15.  
  16. INSERT INTO #Hours (Hour)
  17. SELECT Level
  18. FROM CTE
  19.  
  20. CREATE TABLE #Sessions (UserID INT,
  21. StartDate DATETIME,
  22. EndDate DATETIME
  23. )
  24.  
  25. INSERT INTO #Sessions (UserID, StartDate, EndDate)
  26. VALUES
  27. (1, '2019-05-29 08:00:00', '2019-05-29 16:00:00')
  28. ,(2, '2019-05-29 10:00:00', '2019-05-29 21:00:00')
  29. ,(3, '2019-05-29 02:00:00', '2019-05-29 08:00:00')
  30. ,(4, '2019-05-29 15:00:00', '2019-05-29 23:00:00')
  31. ,(5, '2019-05-29 09:00:00', '2019-05-29 17:00:00')
  32. ,(6, '2019-05-29 13:00:00', '2019-05-29 18:00:00')
  33. ,(7, '2019-05-29 04:00:00', '2019-05-30 00:00:00')
  34.  
  35. SELECT h.Hour,
  36. COUNT(UserID) AS [ActiveUsers]
  37. FROM
  38. (
  39. SELECT UserID, StartDate, EndDate,
  40. CASE
  41. WHEN DATEPART(HOUR, StartDate) = 0 THEN 24
  42. ELSE DATEPART(HOUR, StartDate)
  43. END AS StartHour,
  44. CASE
  45. WHEN DATEPART(HOUR, EndDate) = 0 THEN 24
  46. ELSE DATEPART(HOUR, EndDate)
  47. END AS EndHour
  48. FROM #Sessions
  49. ) s
  50. FULL OUTER JOIN #Hours h ON h.Hour BETWEEN StartHour AND EndHour
  51. GROUP BY h.Hour
  52.  
  53. Hour ActiveUsers
  54. -------------------
  55. 1 0
  56. 2 1
  57. 3 1
  58. 4 2
  59. 5 2
  60. 6 2
  61. 7 2
  62. 8 3
  63. 9 3
  64. 10 4
  65. 11 4
  66. 12 4
  67. 13 5
  68. 14 5
  69. 15 6
  70. 16 6
  71. 17 5
  72. 18 4
  73. 19 3
  74. 20 3
  75. 21 3
  76. 22 2
  77. 23 2
  78. 24 1
RAW Paste Data