Issue with average

Transact-SQL
1

Hi All,

With the following code, I have not managed to add the name the department which has the highest average, If I add the DEPT field on the very last row, suddenly I get much more rows than needed, How could I display the TFC, Max Average, DEPT having the max average?

Many Thanks

Select
Verylong_q.TFC,
Round(MAX(verylong_q.average),2) AS HIGHEST_AVERAGE
FROM

(
SELECT
Long_Q.TFC,
Long_Q.DEPT,
Long_Q.Percentage1,
Long_Q.Percentage2,
Long_Q.Percentage3,
((Percentage1 + Percentage2 + Percentage3)/3) AS Average
FROM
(
SELECT
t_Month1.TFC,
t_Month1.DEPT,
t_Month1.Percentage1,
t_Month2.Percentage2,
t_Month3.Percentage3
From
(
Select
pos.TFC,
mv.Dept AS Sector,
sum(pos.percentage) AS Percentage3
FROM
TBO_POS pos,
TBL_MV mv
Where
pos.IV_ID = mv.IV_ID
and Date = […]
and TFC in […]
group by pos.TFC, mv.Dept, pos.Date
order by 1 DESC ) t_Month1
LEFT JOIN
(
Select
pos.TFC,
mv.Dept AS Sector,
sum(pos.percentage) AS Percentage2
FROM
TBO_POS pos,
TBL_MV mv
Where
pos.IV_ID = mv.IV_ID
and Date = […]
and TFC in […]
group by pos.TFC, mv.Dept, pos.Date
order by 1 DESC ) t_Month2
On t_month1.DEPT = t_month2.DEPT and t_month1.TFC = t_month2.TFC
LEFT JOIN
(
Select
pos.TFC,
mv.Dept AS Sector,
sum(pos.percentage) AS Percentage3
FROM
TBO_POS pos,
TBL_MV mv
Where
pos.IV_ID = mv.IV_ID
and Date = […]
and TFC in […]
group by pos.TFC, mv.Dept, pos.Date
order by 1 DESC ) t_Month3
on t_month1.DEPT = t_month3.DEPT and t_month1.TFC = t_month3.TFC
) Long_Q
) VeryLong_Q
Group by verylong_q.TFC
2

I think this will get you in the right direction (it has not been tested, as you didn't provide sample data to work with):

with cte1
  as (select p.tfc
            ,m.dept
            ,(sum(case
                     when p.[date]>=cast('2016-01-01' as date) /* january 2016 */
                      and p.[date]<cast('2016-02-01' as date)
                     then p.percentage
                     else 0
                  end
                 )
             +sum(case
                     when p.[date]>=cast('2016-02-01' as date) /* february 2016 */
                      and p.[date]<cast('2016-03-01' as date)
                     then p.percentage
                     else 0
                  end
                 )
             +sum(case
                     when p.[date]>=cast('2016-03-01' as date) /* march 2016 */
                      and p.[date]<cast('2016-04-01' as date)
                     then p.percentage
                     else 0
                  end
                 )
             )/3
             as average
        from tbo_pos as p
             inner join tbl_mv as m
                     on m.iv_id=p.iv_id
                    and p.[date]>=cast('2016-01-01' as date) /* january to march 2016 */
                    and p.[date]<cast('2016-04-01' as date)
       where p.tfc in (...) /* please fill in */
       group by p.tfc
               ,m.dept
     )
    ,cte2
  as (select tfc
            ,dept
            ,average
            ,row_number() over(partion by tfc order by average desc) as rn
        from cte1
     )
select tfc
      ,dept
      ,round(average,2) as highest_average
  from cte2
 where rn=1
;
3

Many Thanks Bitsmed! This has helped me!