Select 'ITEM','NONE',rtrim(Grouped_Item),Run_Period

,case 

when Item_Regrade = 'Y' 

then coalesce(V_RawMaterial_KgL_Cost_AvgCalc,0) --If Regrade then ClassAvgCost

when Grouped_Item!=Item_Rollup

then coalesce(V_RawMaterial_KgL_Cost_Rollup,0) --If not Rollup then it gets the rollup cost

else coalesce((V_Std.Milk+V_Std.Ingredients+V_Std.Transferred),0) --Else just gets the normal cost

end as V_RawMaterial_KgL_Cost

,case 

when Item_Regrade = 'Y' 

then coalesce(V_Packaging_KgL_Cost_AvgCalc,0)

when Grouped_Item!=Item_Rollup

then coalesce(V_Packaging_KgL_Cost_Rollup,0)

else coalesce((V_Std.Packaging),0)

end as V_Packaging_KgL_Cost

,case 

when Item_Regrade = 'Y' 

then coalesce(V_DirectLabour_KgL_Cost_AvgCalc,0)

when Grouped_Item!=Item_Rollup

then coalesce(V_DirectLabour_KgL_Cost_Rollup,0)

else coalesce((V_Std.Labour),0)

end as V_DirectLabour_KgL_Cost

,case 

when Item_Regrade = 'Y' 

then coalesce(V_FactDepreciation_KgL_Cost_AvgCalc,0)

when Grouped_Item!=Item_Rollup

then coalesce(V_FactDepreciation_KgL_Cost_Rollup,0)

else coalesce((V_Std.Depreciation),0)

end as V_FactDepreciation_KgL_Cost

,case 

when Item_Regrade = 'Y' 

then coalesce(V_Energy_KgL_Cost_AvgCalc,0)

when Grouped_Item!=Item_Rollup

then coalesce(V_Energy_KgL_Cost_Rollup,0)

else coalesce((V_Std.Energy),0)

end as V_Energy_KgL_Cost

,case 

when Item_Regrade = 'Y' 

then coalesce(V_ProdFixedCosts_KgL_Cost_AvgCalc,0)

when Grouped_Item!=Item_Rollup

then coalesce(V_ProdFixedCosts_KgL_Cost_Rollup,0)

else coalesce((V_Std.FixedOH+V_Std.FixedDirOH),0)

end as V_ProdFixedCosts_KgL_Cost

,case 

when Item_Regrade = 'Y' 

then coalesce(V_ShippingAndWarehouse_KgL_Cost_AvgCalc,0)

when Grouped_Item!=Item_Rollup

then coalesce(V_ShippingAndWarehouse_KgL_Cost_Rollup,0)

else coalesce((V_Std.ShWhs),0)

end as V_ShippingAndWarehouse_KgL_Cost

,coalesce(V_Avg.Transport,0) as V_Transport_KgL_Cost

,coalesce(V_Avg.DistributionCosts,0) as V_DistributionCosts_KgL_Cost

,coalesce(V_Avg.Advertising,0) as V_Advertising_Perc_Cost

,coalesce(V_Avg.Promotion,0) as V_Promotion_Perc_Cost

,coalesce(V_Avg.PRSponsoring,0) as V_PrSponsoring_Perc_Cost

,coalesce(V_Avg.GeneralExpenses,0) as V_GeneralExpenses_Perc_Cost

,0

,0

from 

(

Select 

Item as Grouped_Item 

from 

(

Select 

Item 

from dbo.MIS_Actual_Cost_Staging_Avg

group by Item

union all

Select 

Item 

from dbo.MIS_Actual_Cost_Staging_Std

group by Item

)q1

Group by Item

)q2

left join dbo.MIS_Actual_Cost_Staging_Std V_Std on

V_Std.Item=Grouped_Item

left join dbo.MIS_Actual_Cost_Staging_Avg V_Avg on

V_Avg.Item=Grouped_Item

left join dbo.MIS_Item_Attributes on

Item_Code=Grouped_Item

Left join 

(

Select Item_Class as Item_Class_Avg

,avg(V_RawMaterial_KgL_Cost) as V_RawMaterial_KgL_Cost_AvgCalc

,avg(V_Packaging_KgL_Cost) as V_Packaging_KgL_Cost_AvgCalc

,avg(V_DirectLabour_KgL_Cost) as V_DirectLabour_KgL_Cost_AvgCalc

,avg(V_FactDepreciation_KgL_Cost) as V_FactDepreciation_KgL_Cost_AvgCalc

,avg(V_Energy_KgL_Cost) as V_Energy_KgL_Cost_AvgCalc

,avg(V_ProdFixedCosts_KgL_Cost) as V_ProdFixedCosts_KgL_Cost_AvgCalc

,avg(V_ShippingAndWarehouse_KgL_Cost) as V_ShippingAndWarehouse_KgL_Cost_AvgCalc

from

(

Select 

Item_Class

,(V_Std.Milk+V_Std.Ingredients+V_Std.Transferred) as V_RawMaterial_KgL_Cost

,V_Std.Packaging as V_Packaging_KgL_Cost

,V_Std.Labour as V_DirectLabour_KgL_Cost

,V_Std.Depreciation as V_FactDepreciation_KgL_Cost

,V_Std.Energy as V_Energy_KgL_Cost

,(V_Std.FixedOH+V_Std.FixedDirOH) as V_ProdFixedCosts_KgL_Cost

,V_Std.ShWhs as V_ShippingAndWarehouse_KgL_Cost


from 

(

Select 

Item as Grouped_Item 

from 

(

Select 

Item 

from dbo.MIS_Actual_Cost_Staging_Avg

group by Item

union all

Select 

Item 

from dbo.MIS_Actual_Cost_Staging_Std

group by Item

)q1

Group by Item

)q2

left join dbo.MIS_Actual_Cost_Staging_Std V_Std on

V_Std.Item=Grouped_Item

left join dbo.MIS_Actual_Cost_Staging_Avg V_Avg on

V_Avg.Item=Grouped_Item

left join dbo.MIS_Item_Attributes on

Item_Code=Grouped_Item

Where (V_Std.Milk+V_Std.Ingredients+V_Std.Transferred) is not null

)q1

Group by Item_Class

)V_Class on 

V_Class.Item_Class_Avg=Item_Class

left join 

(

Select Item as CostRollupItem

,(V_Std.Milk+V_Std.Ingredients+V_Std.Transferred) as V_RawMaterial_KgL_Cost_Rollup

,V_Std.Packaging as V_Packaging_KgL_Cost_Rollup

,V_Std.Labour as V_DirectLabour_KgL_Cost_Rollup

,V_Std.Depreciation as V_FactDepreciation_KgL_Cost_Rollup

,V_Std.Energy as V_Energy_KgL_Cost_Rollup

,(V_Std.FixedOH+V_Std.FixedDirOH) as V_ProdFixedCosts_KgL_Cost_Rollup

,V_Std.ShWhs as V_ShippingAndWarehouse_KgL_Cost_Rollup

from 

dbo.MIS_Actual_Cost_Staging_Std V_Std

left join dbo.MIS_Item_Attributes on

Item_Code=Item

where Item=Item_Rollup

)V_Rollup on

CostRollupItem=Item_Rollup

Left join dbo.MIS_System_Parameters on

Run_Type='DAILY_COST'

 

 

 

left join

(

Select V_Main.Item as ItemMax,V_Main.RawMaterial_KgL_Cost as Cost from 

dbo.MIS_Actual_Cost_Daily_Measures V_Main

left join 

(

Select Item, max(Period) as V_Max_Period

from dbo.MIS_Actual_Cost_Daily_Measures

where COSTTYPE='ITEM'

and RawMaterial_KgL_Cost !=0

Group by Item

)q1 on q1.Item=V_Main.Item

and V_Max_Period=Period

Where V_Max_Period is not null

)Q_RawMaterial_KgL_Cost on ItemMax=Grouped_Item

left join

(

Select V_Main.Item as ItemMax,V_Main.Packaging_KgL_Cost as Cost from 

dbo.MIS_Actual_Cost_Daily_Measures V_Main

left join 

(

Select Item, max(Period) as V_Max_Period

from dbo.MIS_Actual_Cost_Daily_Measures

where COSTTYPE='ITEM'

and Packaging_KgL_Cost !=0

Group by Item

)q1 on q1.Item=V_Main.Item

and V_Max_Period=Period

Where V_Max_Period is not null

)Q_Packaging_KgL_Cost on Q_Packaging_KgL_Cost.ItemMax=Grouped_Item

--

left join

(

Select V_Main.Item as ItemMax,V_Main.DirectLabour_KgL_Cost as Cost from 

dbo.MIS_Actual_Cost_Daily_Measures V_Main

left join 

(

Select Item, max(Period) as V_Max_Period

from dbo.MIS_Actual_Cost_Daily_Measures

where COSTTYPE='ITEM'

and DirectLabour_KgL_Cost !=0

Group by Item

)q1 on q1.Item=V_Main.Item

and V_Max_Period=Period

Where V_Max_Period is not null

)Q_DirectLabour_KgL_Cost on Q_DirectLabour_KgL_Cost.ItemMax=Grouped_Item

--

left join

(

Select V_Main.Item as ItemMax,V_Main.Energy_KgL_Cost as Cost from 

dbo.MIS_Actual_Cost_Daily_Measures V_Main

left join 

(

Select Item, max(Period) as V_Max_Period

from dbo.MIS_Actual_Cost_Daily_Measures

where COSTTYPE='ITEM'

and Energy_KgL_Cost !=0

Group by Item

)q1 on q1.Item=V_Main.Item

and V_Max_Period=Period

Where V_Max_Period is not null

)Q_Energy_KgL_Cost on Q_Energy_KgL_Cost.ItemMax=Grouped_Item

--

left join

(

Select V_Main.Item as ItemMax,V_Main.Transport_KgL_Cost as Cost from 

dbo.MIS_Actual_Cost_Daily_Measures V_Main

left join 

(

Select Item, max(Period) as V_Max_Period

from dbo.MIS_Actual_Cost_Daily_Measures

where COSTTYPE='ITEM'

and Transport_KgL_Cost !=0

Group by Item

)q1 on q1.Item=V_Main.Item

and V_Max_Period=Period

Where V_Max_Period is not null

)Q_Transport_KgL_Cost on Q_Transport_KgL_Cost.ItemMax=Grouped_Item

--

left join

(

Select V_Main.Item as ItemMax,V_Main.DistributionCosts_KgL_Cost as Cost from 

dbo.MIS_Actual_Cost_Daily_Measures V_Main

left join 

(

Select Item, max(Period) as V_Max_Period

from dbo.MIS_Actual_Cost_Daily_Measures

where COSTTYPE='ITEM'

and DistributionCosts_KgL_Cost !=0

Group by Item

)q1 on q1.Item=V_Main.Item

and V_Max_Period=Period

Where V_Max_Period is not null

)Q_DistributionCosts_KgL_Cost on Q_DistributionCosts_KgL_Cost.ItemMax=Grouped_Item

--

left join

(

Select V_Main.Item as ItemMax,V_Main.FactDepreciation_KgL_Cost as Cost from 

dbo.MIS_Actual_Cost_Daily_Measures V_Main

left join 

(

Select Item, max(Period) as V_Max_Period

from dbo.MIS_Actual_Cost_Daily_Measures

where COSTTYPE='ITEM'

and FactDepreciation_KgL_Cost !=0

Group by Item

)q1 on q1.Item=V_Main.Item

and V_Max_Period=Period

Where V_Max_Period is not null

)Q_FactDepreciation_KgL_Cost on Q_FactDepreciation_KgL_Cost.ItemMax=Grouped_Item

--

left join

(

Select V_Main.Item as ItemMax,V_Main.ProdFixedCosts_KgL_Cost as Cost from 

dbo.MIS_Actual_Cost_Daily_Measures V_Main

left join 

(

Select Item, max(Period) as V_Max_Period

from dbo.MIS_Actual_Cost_Daily_Measures

where COSTTYPE='ITEM'

and ProdFixedCosts_KgL_Cost !=0

Group by Item

)q1 on q1.Item=V_Main.Item

and V_Max_Period=Period

Where V_Max_Period is not null

)Q_ProdFixedCosts_KgL_Cost on Q_ProdFixedCosts_KgL_Cost.ItemMax=Grouped_Item

--

left join

(

Select V_Main.Item as ItemMax,V_Main.Advertising_Perc_Cost as Cost from 

dbo.MIS_Actual_Cost_Daily_Measures V_Main

left join 

(

Select Item, max(Period) as V_Max_Period

from dbo.MIS_Actual_Cost_Daily_Measures

where COSTTYPE='ITEM'

and Advertising_Perc_Cost !=0

Group by Item

)q1 on q1.Item=V_Main.Item

and V_Max_Period=Period

Where V_Max_Period is not null

)Q_Advertising_Perc_Cost on Q_Advertising_Perc_Cost.ItemMax=Grouped_Item

--

left join

(

Select V_Main.Item as ItemMax,V_Main.Promotion_Perc_Cost as Cost from 

dbo.MIS_Actual_Cost_Daily_Measures V_Main

left join 

(

Select Item, max(Period) as V_Max_Period

from dbo.MIS_Actual_Cost_Daily_Measures

where COSTTYPE='ITEM'

and Promotion_Perc_Cost !=0

Group by Item

)q1 on q1.Item=V_Main.Item

and V_Max_Period=Period

Where V_Max_Period is not null

)Q_Promotion_Perc_Cost on Q_Promotion_Perc_Cost.ItemMax=Grouped_Item

--

left join

(

Select V_Main.Item as ItemMax,V_Main.PrSponsoring_Perc_Cost as Cost from 

dbo.MIS_Actual_Cost_Daily_Measures V_Main

left join 

(

Select Item, max(Period) as V_Max_Period

from dbo.MIS_Actual_Cost_Daily_Measures

where COSTTYPE='ITEM'

and PrSponsoring_Perc_Cost !=0

Group by Item

)q1 on q1.Item=V_Main.Item

and V_Max_Period=Period

Where V_Max_Period is not null

)Q_PrSponsoring_Perc_Cost on Q_PrSponsoring_Perc_Cost.ItemMax=Grouped_Item

--

left join

(

Select V_Main.Item as ItemMax,V_Main.GeneralExpenses_Perc_Cost as Cost from 

dbo.MIS_Actual_Cost_Daily_Measures V_Main

left join 

(

Select Item, max(Period) as V_Max_Period

from dbo.MIS_Actual_Cost_Daily_Measures

where COSTTYPE='ITEM'

and GeneralExpenses_Perc_Cost !=0

Group by Item

)q1 on q1.Item=V_Main.Item

and V_Max_Period=Period

Where V_Max_Period is not null

)Q_GeneralExpenses_Perc_Cost on Q_GeneralExpenses_Perc_Cost.ItemMax=Grouped_Item