I need help on simplyfying this Update query!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

I need help on simplyfying this Update query!

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-02-21 : 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 Post

Posted - 2008-03-18 : 17:32:21

No. Do it yourself.

willhaney
Starting Member

1 Post

Posted - 2008-03-18 : 18:14:09

Made it to reddit:
http://reddit.com/

dnask8
Starting Member

1 Post

Posted - 2008-03-18 : 18:26:25

Get Jet Reports.

spiderx
Starting Member

1 Post

Posted - 2008-03-18 : 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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-18 : 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 - 2008-03-18 : 21:00:47

[code]
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.
[/code]

mysterystar
Starting Member

2 Posts

Posted - 2008-03-19 : 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

1 Post

Posted - 2008-03-19 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources