| Author |
 Topic  |
|
|
ismailc
Constraint Violating Yak Guru
South Africa
290 Posts |
 Posted - 02/21/2008 : 09:47:11
|
Hi, I need help on simplyfying this Update query!
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
Please Assist!
Regards
|
|
|
mrg
Starting Member
1 Posts |
Posted - 03/18/2008 : 17:32:21
|
| No. Do it yourself. |
 |
|
|
willhaney
Starting Member
1 Posts |
|
|
dnask8
Starting Member
1 Posts |
Posted - 03/18/2008 : 18:26:25
|
| Get Jet Reports. |
|
|
|
spiderx
Starting Member
1 Posts |
Posted - 03/18/2008 : 20:27:08
|
quote:
I need help on simplyfying this Update query
This is not an "update query" it is a select query. What do you want to simplify? |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2875 Posts |
Posted - 03/18/2008 : 20:55:35
|
These posts are neither helpful nor humorous. If you're going to give somebody a hard time, at least be clever about it. Ismail, you know the drill -- DDLs, sample data, indexes, etc.
Jim |
|
|
|
willpost
Starting Member
4 Posts |
Posted - 03/18/2008 : 21:00:47
|
It appears to be for a Dairy or Bakery
The following is simplified by separating into SQL Server views.
Based on your SQL, I made assumptions for the following tables:
MIS_Actual_Cost_Daily_Measures
Item varchar 10
COSTTYPE varchar 10
Period int 4
GeneralExpenses_Perc_Cost money 8
PrSponsoring_Perc_Cost money 8
Promotion_Perc_Cost money 8
Advertising_Perc_Cost money 8
ProdFixedCosts_KgL_Cost money 8
FactDepreciation_KgL_Cost money 8
DistributionCosts_KgL_Cost money 8
Transport_KgL_Cost money 8
Energy_KgL_Cost money 8
DirectLabour_KgL_Cost money 8
Packaging_KgL_Cost money 8
RawMaterial_KgL_Cost money 8
MIS_Actual_Cost_Staging_Avg
Item varchar 10
Transport money 8
DistributionCosts money 8
Advertising money 8
Promotion money 8
PRSponsoring money 10
GeneralExpenses money 8
MIS_Actual_Cost_Staging_Std
Item varchar 10
Item_Rollup varchar 10
Milk money 8
Ingredients money 8
Transferred money 8
Packaging money 8
Labour money 8
Depreciation money 8
Energy money 8
FixedOH money 8
FixedDirOH money 8
ShWhs money 8
MIS_Item_Attributes
Item varchar 10
Item_Class varchar 10
Item_Regrade varchar 1
MIS_System_Parameters
Run_Type varchar 10
Run_Period varchar 10
First, save the following as views with the corresponding names
qryMIS_ACDM_Expenses
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
qryMIS_ACDM_Sponsoring
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
qryMIS_ACDM_Promotion
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
qryMIS_ACDM_Advertising
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
qryMIS_ACDM_FixedCosts
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
qryMIS_ACDM_Depreciation
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
qryMIS_ACDM_Distribution
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
qryMIS_ACDM_Transport
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
qryMIS_ACDM_Energy
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
qryMIS_ACDM_DirectLabour
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
qryMIS_ACDM_Packaging
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
qryMIS_ACDM_RawMaterial
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
qryMIS_ACS_AvgStd
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
Second, save the following as views with the corresponding names
qryMIS_ACDM_Expenses_Cost
SELECT dbo.MIS_Actual_Cost_Daily_Measures.Item AS ItemMax, dbo.MIS_Actual_Cost_Daily_Measures.GeneralExpenses_Perc_Cost As Cost, dbo.qryMIS_ACDM_Expenses.V_Max_Period
FROM dbo.MIS_Actual_Cost_Daily_Measures
LEFT OUTER JOIN dbo.qryMIS_ACDM_Expenses ON dbo.MIS_Actual_Cost_Daily_Measures.Item = dbo.qryMIS_ACDM_Expenses.Item
AND dbo.MIS_Actual_Cost_Daily_Measures.Period = dbo.qryMIS_ACDM_Expenses.V_Max_Period
WHERE (dbo.qryMIS_ACDM_Expenses.V_Max_Period IS NOT NULL)
qryMIS_ACDM_Sponsoring_Cost
SELECT dbo.MIS_Actual_Cost_Daily_Measures.Item AS ItemMax, dbo.MIS_Actual_Cost_Daily_Measures.PrSponsoring_Perc_Cost As Cost, dbo.qryMIS_ACDM_Sponsoring.V_Max_Period
FROM dbo.MIS_Actual_Cost_Daily_Measures
LEFT OUTER JOIN dbo.qryMIS_ACDM_Sponsoring ON dbo.MIS_Actual_Cost_Daily_Measures.Item = dbo.qryMIS_ACDM_Sponsoring.Item
AND dbo.MIS_Actual_Cost_Daily_Measures.Period = dbo.qryMIS_ACDM_Sponsoring.V_Max_Period
WHERE (dbo.qryMIS_ACDM_Sponsoring.V_Max_Period IS NOT NULL)
qryMIS_ACDM_Promotion_Cost
SELECT dbo.MIS_Actual_Cost_Daily_Measures.Item AS ItemMax, dbo.MIS_Actual_Cost_Daily_Measures.Promotion_Perc_Cost As Cost, dbo.qryMIS_ACDM_Promotion.V_Max_Period
FROM dbo.MIS_Actual_Cost_Daily_Measures
LEFT OUTER JOIN dbo.qryMIS_ACDM_Promotion ON dbo.MIS_Actual_Cost_Daily_Measures.Item = dbo.qryMIS_ACDM_Promotion.Item
AND dbo.MIS_Actual_Cost_Daily_Measures.Period = dbo.qryMIS_ACDM_Promotion.V_Max_Period
WHERE (dbo.qryMIS_ACDM_Promotion.V_Max_Period IS NOT NULL)
qryMIS_ACDM_Advertising_Cost
SELECT dbo.MIS_Actual_Cost_Daily_Measures.Item AS ItemMax, dbo.MIS_Actual_Cost_Daily_Measures.Advertising_Perc_Cost As Cost, dbo.qryMIS_ACDM_Advertising.V_Max_Period
FROM dbo.MIS_Actual_Cost_Daily_Measures
LEFT OUTER JOIN dbo.qryMIS_ACDM_Advertising ON dbo.MIS_Actual_Cost_Daily_Measures.Item = dbo.qryMIS_ACDM_Advertising.Item
AND dbo.MIS_Actual_Cost_Daily_Measures.Period = dbo.qryMIS_ACDM_Advertising.V_Max_Period
WHERE (dbo.qryMIS_ACDM_Advertising.V_Max_Period IS NOT NULL)
qryMIS_ACDM_FixedCosts_Cost
SELECT dbo.MIS_Actual_Cost_Daily_Measures.Item AS ItemMax, dbo.MIS_Actual_Cost_Daily_Measures.ProdFixedCosts_KgL_Cost As Cost, dbo.qryMIS_ACDM_FixedCosts.V_Max_Period
FROM dbo.MIS_Actual_Cost_Daily_Measures
LEFT OUTER JOIN dbo.qryMIS_ACDM_FixedCosts ON dbo.MIS_Actual_Cost_Daily_Measures.Item = dbo.qryMIS_ACDM_FixedCosts.Item
AND dbo.MIS_Actual_Cost_Daily_Measures.Period = dbo.qryMIS_ACDM_FixedCosts.V_Max_Period
WHERE (dbo.qryMIS_ACDM_FixedCosts.V_Max_Period IS NOT NULL)
qryMIS_ACDM_Depreciation_Cost
SELECT dbo.MIS_Actual_Cost_Daily_Measures.Item AS ItemMax, dbo.MIS_Actual_Cost_Daily_Measures.FactDepreciation_KgL_Cost As Cost, dbo.qryMIS_ACDM_Depreciation.V_Max_Period
FROM dbo.MIS_Actual_Cost_Daily_Measures
LEFT OUTER JOIN dbo.qryMIS_ACDM_Depreciation ON dbo.MIS_Actual_Cost_Daily_Measures.Item = dbo.qryMIS_ACDM_Depreciation.Item
AND dbo.MIS_Actual_Cost_Daily_Measures.Period = dbo.qryMIS_ACDM_Depreciation.V_Max_Period
WHERE (dbo.qryMIS_ACDM_Depreciation.V_Max_Period IS NOT NULL)
qryMIS_ACDM_Distribution_Cost
SELECT dbo.MIS_Actual_Cost_Daily_Measures.Item AS ItemMax, dbo.MIS_Actual_Cost_Daily_Measures.DistributionCosts_KgL_Cost As Cost, dbo.qryMIS_ACDM_Distribution.V_Max_Period
FROM dbo.MIS_Actual_Cost_Daily_Measures
LEFT OUTER JOIN dbo.qryMIS_ACDM_Distribution ON dbo.MIS_Actual_Cost_Daily_Measures.Item = dbo.qryMIS_ACDM_Distribution.Item
AND dbo.MIS_Actual_Cost_Daily_Measures.Period = dbo.qryMIS_ACDM_Distribution.V_Max_Period
WHERE (dbo.qryMIS_ACDM_Distribution.V_Max_Period IS NOT NULL)
qryMIS_ACDM_Transport_Cost
SELECT dbo.MIS_Actual_Cost_Daily_Measures.Item AS ItemMax, dbo.MIS_Actual_Cost_Daily_Measures.Transport_KgL_Cost As Cost, dbo.qryMIS_ACDM_Transport.V_Max_Period
FROM dbo.MIS_Actual_Cost_Daily_Measures
LEFT OUTER JOIN dbo.qryMIS_ACDM_Transport ON dbo.MIS_Actual_Cost_Daily_Measures.Item = dbo.qryMIS_ACDM_Transport.Item
AND dbo.MIS_Actual_Cost_Daily_Measures.Period = dbo.qryMIS_ACDM_Transport.V_Max_Period
WHERE (dbo.qryMIS_ACDM_Transport.V_Max_Period IS NOT NULL)
qryMIS_ACDM_Energy_Cost
SELECT dbo.MIS_Actual_Cost_Daily_Measures.Item AS ItemMax, dbo.MIS_Actual_Cost_Daily_Measures.Energy_KgL_Cost As Cost, dbo.qryMIS_ACDM_Energy.V_Max_Period
FROM dbo.MIS_Actual_Cost_Daily_Measures
LEFT OUTER JOIN dbo.qryMIS_ACDM_Energy ON dbo.MIS_Actual_Cost_Daily_Measures.Item = dbo.qryMIS_ACDM_Energy.Item
AND dbo.MIS_Actual_Cost_Daily_Measures.Period = dbo.qryMIS_ACDM_Energy.V_Max_Period
WHERE (dbo.qryMIS_ACDM_Energy.V_Max_Period IS NOT NULL)
qryMIS_ACDM_DirectLabour_Cost
SELECT dbo.MIS_Actual_Cost_Daily_Measures.Item AS ItemMax, dbo.MIS_Actual_Cost_Daily_Measures.DirectLabour_KgL_Cost As Cost, dbo.qryMIS_ACDM_DirectLabour.V_Max_Period
FROM dbo.MIS_Actual_Cost_Daily_Measures
LEFT OUTER JOIN dbo.qryMIS_ACDM_DirectLabour ON dbo.MIS_Actual_Cost_Daily_Measures.Item = dbo.qryMIS_ACDM_DirectLabour.Item
AND dbo.MIS_Actual_Cost_Daily_Measures.Period = dbo.qryMIS_ACDM_DirectLabour.V_Max_Period
WHERE (dbo.qryMIS_ACDM_DirectLabour.V_Max_Period IS NOT NULL)
qryMIS_ACDM_Packaging_Cost
SELECT dbo.MIS_Actual_Cost_Daily_Measures.Item AS ItemMax, dbo.MIS_Actual_Cost_Daily_Measures.Packaging_KgL_Cost AS Cost,
dbo.qryMIS_ACDM_Packaging.V_Max_Period
FROM dbo.MIS_Actual_Cost_Daily_Measures
LEFT OUTER JOIN
dbo.qryMIS_ACDM_Packaging ON dbo.MIS_Actual_Cost_Daily_Measures.Item = dbo.qryMIS_ACDM_Packaging.Item
AND dbo.MIS_Actual_Cost_Daily_Measures.Period = dbo.qryMIS_ACDM_Packaging.V_Max_Period
WHERE (dbo.qryMIS_ACDM_Packaging.V_Max_Period IS NOT NULL)
qryMIS_ACDM_RawMaterial_Cost
SELECT dbo.MIS_Actual_Cost_Daily_Measures.Item AS ItemMax, dbo.MIS_Actual_Cost_Daily_Measures.RawMaterial_KgL_Cost As Cost, dbo.qryMIS_ACDM_RawMaterial.V_Max_Period
FROM dbo.MIS_Actual_Cost_Daily_Measures
LEFT OUTER JOIN dbo.qryMIS_ACDM_RawMaterial ON dbo.MIS_Actual_Cost_Daily_Measures.Item = dbo.qryMIS_ACDM_RawMaterial.Item
AND dbo.MIS_Actual_Cost_Daily_Measures.Period = dbo.qryMIS_ACDM_RawMaterial.V_Max_Period
WHERE (dbo.qryMIS_ACDM_RawMaterial.V_Max_Period IS NOT NULL)
qryMIS_ACSS
SELECT dbo.MIS_Actual_Cost_Staging_Std.Item AS CostRollupItem,
dbo.MIS_Actual_Cost_Staging_Std.Milk + dbo.MIS_Actual_Cost_Staging_Std.Ingredients + dbo.MIS_Actual_Cost_Staging_Std.Transferred AS V_RawMaterial_KgL_Cost_Rollup,
dbo.MIS_Actual_Cost_Staging_Std.Packaging AS V_Packaging_KgL_Cost_Rollup,
dbo.MIS_Actual_Cost_Staging_Std.Labour AS V_DirectLabour_KgL_Cost_Rollup,
dbo.MIS_Actual_Cost_Staging_Std.Depreciation AS V_FactDepreciation_KgL_Cost_Rollup,
dbo.MIS_Actual_Cost_Staging_Std.Energy AS V_Energy_KgL_Cost_Rollup,
dbo.MIS_Actual_Cost_Staging_Std.FixedOH + dbo.MIS_Actual_Cost_Staging_Std.FixedDirOH AS V_ProdFixedCosts_KgL_Cost_Rollup,
dbo.MIS_Actual_Cost_Staging_Std.ShWhs AS V_ShippingAndWarehouse_KgL_Cost_Rollup
FROM dbo.MIS_Actual_Cost_Staging_Std LEFT OUTER JOIN
dbo.MIS_Item_Attributes ON dbo.MIS_Actual_Cost_Staging_Std.Item = dbo.MIS_Item_Attributes.Item_Code
qryMIS_ACS_AvgStd_Group
SELECT Item AS Grouped_Item
FROM dbo.qryMIS_ACS_AvgStd
GROUP BY Item
Third, save the following as views with the corresponding names
qryMIS_ACS_Costs
SELECT dbo.MIS_Item_Attributes.Item_Class,
dbo.MIS_Actual_Cost_Staging_Std.Milk + dbo.MIS_Actual_Cost_Staging_Std.Ingredients + dbo.MIS_Actual_Cost_Staging_Std.Transferred AS V_RawMaterial_KgL_Cost,
dbo.MIS_Actual_Cost_Staging_Std.Packaging AS V_Packaging_KgL_Cost, dbo.MIS_Actual_Cost_Staging_Std.Labour AS V_DirectLabour_KgL_Cost,
dbo.MIS_Actual_Cost_Staging_Std.Depreciation AS V_FactDepreciation_KgL_Cost, dbo.MIS_Actual_Cost_Staging_Std.Energy AS V_Energy_KgL_Cost,
dbo.MIS_Actual_Cost_Staging_Std.FixedOH + dbo.MIS_Actual_Cost_Staging_Std.FixedDirOH AS V_ProdFixedCosts_KgL_Cost,
dbo.MIS_Actual_Cost_Staging_Std.ShWhs AS V_ShippingAndWarehouse_KgL_Cost
FROM dbo.qryMIS_ACS_AvgStd_Group LEFT OUTER JOIN
dbo.MIS_Item_Attributes ON dbo.qryMIS_ACS_AvgStd_Group.Grouped_Item = dbo.MIS_Item_Attributes.Item_Code LEFT OUTER JOIN
dbo.MIS_Actual_Cost_Staging_Std ON dbo.qryMIS_ACS_AvgStd_Group.Grouped_Item = dbo.MIS_Actual_Cost_Staging_Std.Item LEFT OUTER JOIN
dbo.MIS_Actual_Cost_Staging_Avg ON dbo.qryMIS_ACS_AvgStd_Group.Grouped_Item = dbo.MIS_Actual_Cost_Staging_Avg.Item
WHERE (dbo.MIS_Actual_Cost_Staging_Std.Milk + dbo.MIS_Actual_Cost_Staging_Std.Ingredients + dbo.MIS_Actual_Cost_Staging_Std.Transferred IS NOT NULL)
Fourth, save the following as views with the corresponding names
qryMIS_ACS_Costs_Avg
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 dbo.qryMIS_ACS_Costs
GROUP BY Item_Class
Fifth, save the following as views with the corresponding names
qryMIS_ACS_Costs_ItemMax
SELECT dbo.qryMIS_ACS_AvgStd_Group.Grouped_Item, dbo.MIS_Actual_Cost_Staging_Std.Item_Rollup,
dbo.qryMIS_ACS_Costs_Avg.V_RawMaterial_KgL_Cost_AvgCalc, dbo.qryMIS_ACS_Costs_Avg.V_Packaging_KgL_Cost_AvgCalc,
dbo.qryMIS_ACS_Costs_Avg.V_DirectLabour_KgL_Cost_AvgCalc, dbo.qryMIS_ACS_Costs_Avg.V_FactDepreciation_KgL_Cost_AvgCalc,
dbo.qryMIS_ACS_Costs_Avg.V_Energy_KgL_Cost_AvgCalc, dbo.qryMIS_ACS_Costs_Avg.V_ProdFixedCosts_KgL_Cost_AvgCalc,
dbo.qryMIS_ACS_Costs_Avg.V_ShippingAndWarehouse_KgL_Cost_AvgCalc, dbo.qryMIS_ACSS_Rollup.V_RawMaterial_KgL_Cost_Rollup,
dbo.qryMIS_ACSS_Rollup.V_Packaging_KgL_Cost_Rollup, dbo.qryMIS_ACSS_Rollup.V_DirectLabour_KgL_Cost_Rollup,
dbo.qryMIS_ACSS_Rollup.V_FactDepreciation_KgL_Cost_Rollup, dbo.qryMIS_ACSS_Rollup.V_Energy_KgL_Cost_Rollup,
dbo.qryMIS_ACSS_Rollup.V_ProdFixedCosts_KgL_Cost_Rollup, dbo.qryMIS_ACSS_Rollup.V_ShippingAndWarehouse_KgL_Cost_Rollup,
dbo.MIS_Actual_Cost_Staging_Std.Milk, dbo.MIS_Actual_Cost_Staging_Std.Ingredients, dbo.MIS_Actual_Cost_Staging_Std.Transferred,
dbo.MIS_Actual_Cost_Staging_Std.Packaging, dbo.MIS_Actual_Cost_Staging_Std.Labour, dbo.MIS_Actual_Cost_Staging_Std.Depreciation,
dbo.MIS_Actual_Cost_Staging_Std.Energy, dbo.MIS_Actual_Cost_Staging_Std.FixedOH, dbo.MIS_Actual_Cost_Staging_Std.FixedDirOH,
dbo.MIS_Actual_Cost_Staging_Std.ShWhs, dbo.MIS_Actual_Cost_Staging_Avg.Transport, dbo.MIS_Actual_Cost_Staging_Avg.DistributionCosts,
dbo.MIS_Actual_Cost_Staging_Avg.Advertising, dbo.MIS_Actual_Cost_Staging_Avg.Promotion, dbo.MIS_Actual_Cost_Staging_Avg.PRSponsoring,
dbo.MIS_Actual_Cost_Staging_Avg.GeneralExpenses, dbo.MIS_System_Parameters.Run_Period
FROM dbo.qryMIS_ACS_AvgStd_Group LEFT OUTER JOIN
dbo.MIS_Actual_Cost_Staging_Std ON dbo.MIS_Actual_Cost_Staging_Std.Item = dbo.qryMIS_ACS_AvgStd_Group.Grouped_Item LEFT OUTER JOIN
dbo.MIS_Actual_Cost_Staging_Avg ON dbo.MIS_Actual_Cost_Staging_Avg.Item = dbo.qryMIS_ACS_AvgStd_Group.Grouped_Item LEFT OUTER JOIN
dbo.MIS_Item_Attributes ON dbo.MIS_Item_Attributes.Item_Code = dbo.qryMIS_ACS_AvgStd_Group.Grouped_Item LEFT OUTER JOIN
dbo.qryMIS_ACS_Costs_Avg ON dbo.qryMIS_ACS_Costs_Avg.Item_Class_Avg = dbo.MIS_Item_Attributes.Item_Class LEFT OUTER JOIN
dbo.qryMIS_ACSS_Rollup ON dbo.qryMIS_ACSS_Rollup.CostRollupItem = dbo.MIS_Actual_Cost_Staging_Std.Item_Rollup LEFT OUTER JOIN
dbo.qryMIS_ACDM_RawMaterial_Cost ON
dbo.qryMIS_ACDM_RawMaterial_Cost.ItemMax = dbo.qryMIS_ACS_AvgStd_Group.Grouped_Item LEFT OUTER JOIN
dbo.qryMIS_ACDM_Packaging_Cost ON
dbo.qryMIS_ACDM_Packaging_Cost.ItemMax = dbo.qryMIS_ACS_AvgStd_Group.Grouped_Item LEFT OUTER JOIN
dbo.qryMIS_ACDM_DirectLabour_Cost ON
dbo.qryMIS_ACDM_DirectLabour_Cost.ItemMax = dbo.qryMIS_ACS_AvgStd_Group.Grouped_Item LEFT OUTER JOIN
dbo.qryMIS_ACDM_Energy_Cost ON dbo.qryMIS_ACDM_Energy_Cost.ItemMax = dbo.qryMIS_ACS_AvgStd_Group.Grouped_Item LEFT OUTER JOIN
dbo.qryMIS_ACDM_Transport_Cost ON dbo.qryMIS_ACDM_Transport_Cost.ItemMax = dbo.qryMIS_ACS_AvgStd_Group.Grouped_Item LEFT OUTER JOIN
dbo.qryMIS_ACDM_Distribution_Cost ON
dbo.qryMIS_ACDM_Distribution_Cost.ItemMax = dbo.qryMIS_ACS_AvgStd_Group.Grouped_Item LEFT OUTER JOIN
dbo.qryMIS_ACDM_Depreciation_Cost ON
dbo.qryMIS_ACDM_Depreciation_Cost.ItemMax = dbo.qryMIS_ACS_AvgStd_Group.Grouped_Item LEFT OUTER JOIN
dbo.qryMIS_ACDM_FixedCosts_Cost ON
dbo.qryMIS_ACDM_FixedCosts_Cost.ItemMax = dbo.qryMIS_ACS_AvgStd_Group.Grouped_Item LEFT OUTER JOIN
dbo.qryMIS_ACDM_Advertising_Cost ON
dbo.qryMIS_ACDM_Advertising_Cost.ItemMax = dbo.qryMIS_ACS_AvgStd_Group.Grouped_Item LEFT OUTER JOIN
dbo.qryMIS_ACDM_Promotion_Cost ON
dbo.qryMIS_ACDM_Promotion_Cost.ItemMax = dbo.qryMIS_ACS_AvgStd_Group.Grouped_Item LEFT OUTER JOIN
dbo.qryMIS_ACDM_Sponsoring_Cost ON
dbo.qryMIS_ACDM_Sponsoring_Cost.ItemMax = dbo.qryMIS_ACS_AvgStd_Group.Grouped_Item LEFT OUTER JOIN
dbo.qryMIS_ACDM_Expenses_Cost ON dbo.qryMIS_ACDM_Expenses_Cost.ItemMax = dbo.qryMIS_ACS_AvgStd_Group.Grouped_Item CROSS JOIN
dbo.MIS_System_Parameters
WHERE (dbo.MIS_System_Parameters.Run_Type = 'DAILY_COST')
Your final query will probably look like the following:
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((Milk+Ingredients+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((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((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((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((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((FixedOH+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((ShWhs),0)
end as V_ShippingAndWarehouse_KgL_Cost
,coalesce(Transport,0) as V_Transport_KgL_Cost
,coalesce(DistributionCosts,0) as V_DistributionCosts_KgL_Cost
,coalesce(Advertising,0) as V_Advertising_Perc_Cost
,coalesce(Promotion,0) as V_Promotion_Perc_Cost
,coalesce(PRSponsoring,0) as V_PrSponsoring_Perc_Cost
,coalesce(GeneralExpenses,0) as V_GeneralExpenses_Perc_Cost
,0
,0
from
qryMIS_ACS_Costs_ItemMax
I can't fully test any of this because I don't have
the specification for the original tables or any sample data
and I don't have query analyzer installed
to verify the IF and Case statements in the final part
At least it's enough to get things rolling.
PS: None of the queries beginning with "qryMIS_ACDM_" appear to be
used in the final calculations. That means about half of the
original SQL (everything after the three blank lines) could probably
be removed and it would still run the same.
|
Edited by - willpost on 08/20/2013 07:36:31 |
|
|
|
mysterystar
Starting Member
2 Posts |
Posted - 03/19/2008 : 01:32:38
|
Yeah I never understood why I made 50k as a database anaylst and later more as an administrator playing with select, update, insert, and delete.
This reminds me that the work is stressful and usually actually done by consultants.
I don't know if the helpful or humorous either after 12 years in the database industry, I have spent 5 mentally disabled.
btw, if you do use an update query always update the base tables and not your new views and once you have views you can grant security better too!
meanwhile i am going to trust my social security check.
cuz yeah you might get fired if you dont get that done unless of course
you take over security
harrybowyer@aol.com |
|
|
|
bbinsj
Starting Member
USA
1 Posts |
Posted - 03/19/2008 : 13:38:05
|
willpost - I'm extremely impressed with your response. I hope you are checking the message boards and will respond the next time I have a problem with SQL code!
ismailc - have you thought about creating a stored procedure and calling functions and views (which willpost helpfully defined)? I can see a few areas, such as the coalesce statements that might work well in a function - mostly from a readability standpoint. |
|
|
| |
Topic |
|
|
|
I need help on simplyfying this Update query!