SHARE
TWEET

Untitled

a guest Jun 16th, 2019 57 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top