I admitted this is one of the most complex SQL statement I have to face so far. I sorta hit the wall on this one and I hope somebody can give me a hand.
I have this table in the database
Item ActiveTime(sec) DateTime
-------------------------------------------
1 10 2013-06-03 17:34:22 -> Monday
2 5 2013-06-04 17:34:22 -> Tuesday
1 2 2013-06-03 12:34:22 -> Monday
1 3 2013-06-04 17:33:22 -> Tuesday
Item Mon Tues Wed Thurs Fri Sat Sun Average
-----------------------------------------------------------------------------------
1 6 3 5
2 5 5
Select *,((ISNULL([Sunday],0) +ISNULL([Monday],0)+ ISNULL([Tuesday],0)+
ISNULL([Wednesday],0)+ ISNULL([Thursday],0)+ISNULL([Friday],0)+
ISNULL([Saturday],0)) /
( CASE WHEN [Sunday] is null
THEN 0 ELSE 1 END +
CASE WHEN [Monday] is null
THEN 0 ELSE 1 END +
CASE WHEN [Tuesday] is null
THEN 0 ELSE 1 END +
CASE WHEN [Wednesday] is null
THEN 0 ELSE 1 END +
CASE WHEN [Thursday] is null
THEN 0 ELSE 1 END +
CASE WHEN [Friday] is null
THEN 0 ELSE 1 END +
CASE WHEN [Saturday] is null
THEN 0 ELSE 1 END )) as Avg
FROM ( SELECT * FROM
(
SELECT a.ResetTime as ResetTime,a.ApartmentDescription as Apartment,
DATENAME(WEEKDAY,a.DateTime) _WEEKDAY
FROM tblECEventLog a
)
AS v1 PIVOT (AVG(ResetTime) FOR _WEEKDAY IN
([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday], [Saturday])
)
AS v2
)
AS v3
Item Mon Tues Wed Thurs Fri Sat Sun Average
-----------------------------------------------------------------------------------
1 6 3 4.5
2 5 5
You should be able to use avg() over()
to get the result. This will allow you to partition the data by each item
:
avg(ActiveTime) over(partition by item) Avg_Item
So the full query will be:
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;
See SQL Demo