SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 I need help on simplyfying this Update query!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ismailc
Constraint Violating Yak Guru

South Africa
286 Posts

Posted - 02/21/2008 :  09:47:11  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
No. Do it yourself.
Go to Top of Page

willhaney
Starting Member

1 Posts

Posted - 03/18/2008 :  18:14:09  Show Profile  Reply with Quote
Made it to reddit:
http://reddit.com/
Go to Top of Page

dnask8
Starting Member

1 Posts

Posted - 03/18/2008 :  18:26:25  Show Profile  Reply with Quote
Get Jet Reports.
Go to Top of Page

spiderx
Starting Member

1 Posts

Posted - 03/18/2008 :  20:27:08  Show Profile  Reply with Quote
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?
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 03/18/2008 :  20:55:35  Show Profile  Reply with Quote
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
Go to Top of Page

willpost
Starting Member

4 Posts

Posted - 03/18/2008 :  21:00:47  Show Profile  Reply with Quote

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
Go to Top of Page

mysterystar
Starting Member

2 Posts

Posted - 03/19/2008 :  01:32:38  Show Profile  Reply with Quote
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
Go to Top of Page

bbinsj
Starting Member

USA
1 Posts

Posted - 03/19/2008 :  13:38:05  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000