0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

DECLARE @DisplayName VARCHAR(200)
SET @DisplayName = 'Jon Skeet'

SELECT CAST(CAST(StartHour AS VARCHAR(2)) + ':' + CAST(StartMinute AS VARCHAR(2)) AS TIME) AS StartTime
  , CAST(CAST(EndHour AS VARCHAR(2)) + ':' + CAST(EndMinute AS VARCHAR(2)) AS TIME) AS EndTime
FROM
(
  SELECT FLOOR(StartTime/100) AS StartHour
    , FLOOR((CAST(FLOOR(StartTime) AS INT)%100) * 60 / 100) AS StartMinute
    , FLOOR(EndTime/100) AS EndHour
    , FLOOR((CAST(FLOOR(EndTime) AS INT)%100) * 60 / 100) AS EndMinute
  FROM
  (
    SELECT AVG(AvgTime) - AVG(StdevTime) AS StartTime
      , AVG(AvgTime) + AVG(StdevTime) AS EndTime
    FROM
    (
      SELECT AVG((DATEPART(HH, c.CreationDate)*100) + (DATEPART(mi, c.CreationDate) * 100 / 60)) AS AvgTime
        , STDEV((DATEPART(HH, c.CreationDate)*100) + (DATEPART(mi, c.CreationDate) * 100 / 60)) AS StdevTime
        , COUNT(*) AS C
      FROM Users u
      LEFT JOIN Comments c
        ON u.Id = c.UserId
      WHERE u.DisplayName = @DisplayName
      
        UNION ALL
        
      SELECT AVG((DATEPART(HH, c.CreationDate)*100) + (DATEPART(mi, c.CreationDate) * 100 / 60)) AS AvgTime
        , STDEV((DATEPART(HH, c.CreationDate)*100) + (DATEPART(mi, c.CreationDate) * 100 / 60)) AS StdevTime
        , COUNT(*) AS C
      FROM Users u
      LEFT JOIN Posts c
        ON u.Id = c.OwnerUserId
      WHERE u.DisplayName = @DisplayName
    ) a
  ) a
) a

fork query

created may 3 13 anonymous