0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for amateur radio enthusiasts

CREATE TABLE #TableA
    ([Item] int, [ActiveTime] int, [DateTime] datetime)
;
    
INSERT INTO #TableA
    ([Item], [ActiveTime], [DateTime])
VALUES
    (1, 10, '2013-06-03 17:34:22'),
    (2, 5, '2013-06-04 17:34:22'),
    (1, 2, '2013-06-03 12:34:22'),
    (1, 3, '2013-06-04 17:33:22')
;

SELECT a.ActiveTime as ActiveTime,a.Item as  Item,DATENAME(WEEKDAY,a.DateTime) _WEEKDAY,
    avg(ActiveTime) over(partition by item) Avg_Item
  FROM #TableA a 

SELECT item,
  [Sunday],
  [Monday],
  [Tuesday],
  [Wednesday],
  [Thursday],
  [Friday],
  [Saturday],
  Avg_Item
FROM 
( 
  SELECT a.ActiveTime as ActiveTime,a.Item as  Item,DATENAME(WEEKDAY,a.DateTime) _WEEKDAY,
    avg(ActiveTime) over(partition by item) Avg_Item
  FROM #TableA a  
) AS v1 PIVOT 
(
  AVG(ActiveTime) 
  FOR _WEEKDAY IN 
(
  [Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday])
) AS v2;

fork query

edited nov 8

created aug 10 16 anonymous