declare @@gblEdate as datetime 
Select @@gblEdate = (select max(edate) as Current_Reporting_Date from CUR_DailyForecast_Input)
declare @@gblCombinedCF as float
select @@gblCombinedCF = (select Combined from REF_LaborConvFactors where ReportDate = @@gblEdate)

Select total.supervisor, 
           total.manager,
           case when total.Supervisor = 'Retired' then '3' when total.Supervisor = total.Manager then '2' else '1' end as MgmtLevel,           
           total.totalheadcount, 
           isnull(forecasted.forecastheadcount, 0) as ForecastHeadCount, 
           total.SatOT, total.SunOT, total.WkOT, 
           total.SatOT + total.SunOT + total.WkOT as TotOT, 
          @@gblCombinedCF * total.totalHeadCount AS BudgetedHours,
          ((total.SatOT + total.SunOT + total.WkOT) / (@@gblCombinedCF * total.totalHeadCount)) * 100 AS FPercentage
from
(
  select distinct top 100 percent Supervisor, Manager, sum(sat_forecast) as SatOT, sum(sun_forecast) as SunOT, sum(wk_forecast) as WkOT, 
                                                     count(ename) as Totalheadcount
  from cur_dailyforecast_input
  group by Supervisor, Manager
  order by manager, supervisor
) total
left outer join
(
  select distinct top 100 percent Supervisor, Manager, count(ename) as ForecastHeadcount
  from cur_dailyforecast_input
  where sat_forecast + sun_forecast + wk_forecast > 0
  group by Supervisor, Manager 
  order by manager, supervisor
) Forecasted
on total.supervisor = Forecasted.supervisor
and total.manager = Forecasted.manager
order by total.manager, mgmtlevel desc, total.supervisor