CREATE PROCEDURE [dbo].[PERFMET_OTForecastStatus] 

-- last updated 01-24-02 1:00 PM
-- This is a parameterized stored procedure that returns a recordset to the Forecast Status Pivot Table

@Manager as varchar(255)

AS

declare @@gblEdate as datetime 
select @@gblEdate = (select max(edate) as Current_Reporting_Date from CUR_DailyForecast_Input)
declare @@gblCombinedCF as float
select @@gblCombinedCF = (select CombinedCF from REF_CUR_LaborConvFactors where ReportDate = @@gblEdate)

if (@Manager = 'Retired')

begin

select distinct top 100 percent 
           rollitup.manager as supervisor, 
           'Retired' as manager, 
           case when rollitup.manager = 'Retired' then '2' else '1' end as mgmtlevel, 
           sum(rollitup.totalheadcount) as totalheadcount, 
           sum(rollitup.forecastedheadcount) as forecastedheadcount, 
           sum(rollitup.satot) as satot, 
           sum(rollitup.sunot) as sunot, 
           sum(rollitup.wkot) as wkot, 
           sum(rollitup.totot) as totot, 
           isnull(sum(rollitup.budgetedhours), 0) as budgetedhours, 
           isnull(((sum(rollitup.satot) + sum(rollitup.sunot) + sum(rollitup.wkot)) / (@@gblCombinedCF * sum(rollitup.totalheadcount))) * 100, 0) as FPercentage

from

(

Select distinct top 100 percent 
           CalcOT.supervisor, 
           CalcOT.manager,
           case when CalcOT.Supervisor = 'Retired' then '3'
                    when CalcOT.Supervisor = CalcOT.Manager then '2' else '1' end as MgmtLevel,           
           CalcHC.TotalHeadCount, 
           CalcHC.ForecastedHeadCount, 
           CalcOT.SatOT, CalcOT.SunOT, CalcOT.WkOT, 
           CalcOT.SatOT + CalcOT.SunOT + CalcOT.WkOT as TotOT, 
           isnull(@@gblCombinedCF * CalcHC.TotalHeadCount, 0) AS BudgetedHours,
           isnull(((CalcOT.SatOT + CalcOT.SunOT + CalcOT.WkOT) / (@@gblCombinedCF * CalcHC.TotalHeadCount)) * 100, 0) 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
  from cur_dailyforecast_input
  group by Supervisor, Manager
  order by manager, supervisor
) CalcOT
inner join
(
  select distinct top 100 percent 
             CalcTotalHC.Supervisor, CalcTotalHC.Manager, CalcTotalHC.TotalHeadCount, 
             isnull(CalcForecastedHC.ForecastHeadcount, 0) as ForecastedHeadCount
  from
  (
    select distinct top 100 percent Supervisor, Manager, count(ename) as Totalheadcount
    from cur_dailyforecast_input
    where ename <> 'Unassigned'
    group by Supervisor, Manager
    order by manager, supervisor
  ) CalcTotalHC
  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
    and ename <> 'Unassigned'
    group by Supervisor, Manager 
    order by manager, supervisor
  ) CalcForecastedHC
  on CalcTotalHC.supervisor = CalcForecastedHC.supervisor
  and CalcTotalHC.Manager = CalcForecastedHC.Manager
) CalcHC 
on CalcOT.Supervisor = CalcHC.Supervisor
and CalcOT.Manager = CalcHC.Manager
order by CalcOT.Manager, mgmtlevel desc, CalcOT.Supervisor

) Rollitup

group by rollitup.manager
order by mgmtlevel desc, supervisor

end

else

begin

Select distinct top 100 percent 
           CalcOT.supervisor, 
           CalcOT.manager,
           case when CalcOT.Supervisor = CalcOT.Manager then '2' else '1' end as MgmtLevel,           
           CalcHC.TotalHeadCount, 
           CalcHC.ForecastedHeadCount, 
           CalcOT.SatOT, CalcOT.SunOT, CalcOT.WkOT, 
           CalcOT.SatOT + CalcOT.SunOT + CalcOT.WkOT as TotOT, 
           isnull(@@gblCombinedCF * CalcHC.TotalHeadCount, 0) AS BudgetedHours,
           isnull(((CalcOT.SatOT + CalcOT.SunOT + CalcOT.WkOT) / (@@gblCombinedCF * CalcHC.TotalHeadCount)) * 100, 0) 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
  from cur_dailyforecast_input
  group by Supervisor, Manager
  order by manager, supervisor
) CalcOT
inner join
(
  select distinct top 100 percent 
             CalcTotalHC.Supervisor, CalcTotalHC.Manager, CalcTotalHC.TotalHeadCount, 
             isnull(CalcForecastedHC.ForecastHeadcount, 0) as ForecastedHeadCount
  from
  (
    select distinct top 100 percent Supervisor, Manager, count(ename) as Totalheadcount
    from cur_dailyforecast_input
    where ename <> 'Unassigned'
    group by Supervisor, Manager
    order by manager, supervisor
  ) CalcTotalHC
  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
    and ename <> 'Unassigned'
    group by Supervisor, Manager 
    order by manager, supervisor
  ) CalcForecastedHC
  on CalcTotalHC.supervisor = CalcForecastedHC.supervisor
  and CalcTotalHC.Manager = CalcForecastedHC.Manager
) CalcHC 
on CalcOT.Supervisor = CalcHC.Supervisor
and CalcOT.Manager = CalcHC.Manager
where CalcOT.manager = @Manager
order by CalcOT.Manager, mgmtlevel desc, CalcOT.Supervisor

end
GO