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 Select 'ITEM','NONE',rtrim(Grouped_Item),Run_Period,case when Item_Regrade = 'Y' then coalesce(V_RawMaterial_KgL_Cost_AvgCalc,0) --If Regrade then ClassAvgCostwhen Grouped_Item!=Item_Rollupthen coalesce(V_RawMaterial_KgL_Cost_Rollup,0) --If not Rollup then it gets the rollup costelse coalesce((V_Std.Milk+V_Std.Ingredients+V_Std.Transferred),0) --Else just gets the normal costend as V_RawMaterial_KgL_Cost,case when Item_Regrade = 'Y' then coalesce(V_Packaging_KgL_Cost_AvgCalc,0)when Grouped_Item!=Item_Rollupthen 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_Rollupthen 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_Rollupthen 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_Rollupthen 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_Rollupthen 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_Rollupthen 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,0from (Select Item as Grouped_Item from (Select Item from dbo.MIS_Actual_Cost_Staging_Avggroup by Itemunion allSelect Item from dbo.MIS_Actual_Cost_Staging_Stdgroup by Item)q1Group by Item)q2left join dbo.MIS_Actual_Cost_Staging_Std V_Std onV_Std.Item=Grouped_Itemleft join dbo.MIS_Actual_Cost_Staging_Avg V_Avg onV_Avg.Item=Grouped_Itemleft join dbo.MIS_Item_Attributes onItem_Code=Grouped_ItemLeft 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_AvgCalcfrom(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_Costfrom (Select Item as Grouped_Item from (Select Item from dbo.MIS_Actual_Cost_Staging_Avggroup by Itemunion allSelect Item from dbo.MIS_Actual_Cost_Staging_Stdgroup by Item)q1Group by Item)q2left join dbo.MIS_Actual_Cost_Staging_Std V_Std onV_Std.Item=Grouped_Itemleft join dbo.MIS_Actual_Cost_Staging_Avg V_Avg onV_Avg.Item=Grouped_Itemleft join dbo.MIS_Item_Attributes onItem_Code=Grouped_ItemWhere (V_Std.Milk+V_Std.Ingredients+V_Std.Transferred) is not null)q1Group by Item_Class)V_Class on V_Class.Item_Class_Avg=Item_Classleft 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_Rollupfrom dbo.MIS_Actual_Cost_Staging_Std V_Stdleft join dbo.MIS_Item_Attributes onItem_Code=Itemwhere Item=Item_Rollup)V_Rollup onCostRollupItem=Item_RollupLeft join dbo.MIS_System_Parameters onRun_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_Mainleft join (Select Item, max(Period) as V_Max_Periodfrom dbo.MIS_Actual_Cost_Daily_Measureswhere COSTTYPE='ITEM'and RawMaterial_KgL_Cost !=0Group by Item)q1 on q1.Item=V_Main.Itemand V_Max_Period=PeriodWhere V_Max_Period is not null)Q_RawMaterial_KgL_Cost on ItemMax=Grouped_Itemleft join(Select V_Main.Item as ItemMax,V_Main.Packaging_KgL_Cost as Cost from dbo.MIS_Actual_Cost_Daily_Measures V_Mainleft join (Select Item, max(Period) as V_Max_Periodfrom dbo.MIS_Actual_Cost_Daily_Measureswhere COSTTYPE='ITEM'and Packaging_KgL_Cost !=0Group by Item)q1 on q1.Item=V_Main.Itemand V_Max_Period=PeriodWhere 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_Mainleft join (Select Item, max(Period) as V_Max_Periodfrom dbo.MIS_Actual_Cost_Daily_Measureswhere COSTTYPE='ITEM'and DirectLabour_KgL_Cost !=0Group by Item)q1 on q1.Item=V_Main.Itemand V_Max_Period=PeriodWhere 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_Mainleft join (Select Item, max(Period) as V_Max_Periodfrom dbo.MIS_Actual_Cost_Daily_Measureswhere COSTTYPE='ITEM'and Energy_KgL_Cost !=0Group by Item)q1 on q1.Item=V_Main.Itemand V_Max_Period=PeriodWhere 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_Mainleft join (Select Item, max(Period) as V_Max_Periodfrom dbo.MIS_Actual_Cost_Daily_Measureswhere COSTTYPE='ITEM'and Transport_KgL_Cost !=0Group by Item)q1 on q1.Item=V_Main.Itemand V_Max_Period=PeriodWhere 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_Mainleft join (Select Item, max(Period) as V_Max_Periodfrom dbo.MIS_Actual_Cost_Daily_Measureswhere COSTTYPE='ITEM'and DistributionCosts_KgL_Cost !=0Group by Item)q1 on q1.Item=V_Main.Itemand V_Max_Period=PeriodWhere 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_Mainleft join (Select Item, max(Period) as V_Max_Periodfrom dbo.MIS_Actual_Cost_Daily_Measureswhere COSTTYPE='ITEM'and FactDepreciation_KgL_Cost !=0Group by Item)q1 on q1.Item=V_Main.Itemand V_Max_Period=PeriodWhere 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_Mainleft join (Select Item, max(Period) as V_Max_Periodfrom dbo.MIS_Actual_Cost_Daily_Measureswhere COSTTYPE='ITEM'and ProdFixedCosts_KgL_Cost !=0Group by Item)q1 on q1.Item=V_Main.Itemand V_Max_Period=PeriodWhere 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_Mainleft join (Select Item, max(Period) as V_Max_Periodfrom dbo.MIS_Actual_Cost_Daily_Measureswhere COSTTYPE='ITEM'and Advertising_Perc_Cost !=0Group by Item)q1 on q1.Item=V_Main.Itemand V_Max_Period=PeriodWhere 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_Mainleft join (Select Item, max(Period) as V_Max_Periodfrom dbo.MIS_Actual_Cost_Daily_Measureswhere COSTTYPE='ITEM'and Promotion_Perc_Cost !=0Group by Item)q1 on q1.Item=V_Main.Itemand V_Max_Period=PeriodWhere 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_Mainleft join (Select Item, max(Period) as V_Max_Periodfrom dbo.MIS_Actual_Cost_Daily_Measureswhere COSTTYPE='ITEM'and PrSponsoring_Perc_Cost !=0Group by Item)q1 on q1.Item=V_Main.Itemand V_Max_Period=PeriodWhere 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_Mainleft join (Select Item, max(Period) as V_Max_Periodfrom dbo.MIS_Actual_Cost_Daily_Measureswhere COSTTYPE='ITEM'and GeneralExpenses_Perc_Cost !=0Group by Item)q1 on q1.Item=V_Main.Itemand V_Max_Period=PeriodWhere V_Max_Period is not null)Q_GeneralExpenses_Perc_Cost on Q_GeneralExpenses_Perc_Cost.ItemMax=Grouped_Item Please Assist!Regards Hi, I need help on simplyfying this Update query!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 BakeryThe following is simplified by separating into SQL Server views.Based on your SQL, I made assumptions for the following tables:MIS_Actual_Cost_Daily_MeasuresItem varchar 10COSTTYPE varchar 10Period int 4GeneralExpenses_Perc_Cost money 8PrSponsoring_Perc_Cost money 8Promotion_Perc_Cost money 8Advertising_Perc_Cost money 8ProdFixedCosts_KgL_Cost money 8FactDepreciation_KgL_Cost money 8DistributionCosts_KgL_Cost money 8Transport_KgL_Cost money 8Energy_KgL_Cost money 8DirectLabour_KgL_Cost money 8Packaging_KgL_Cost money 8RawMaterial_KgL_Cost money 8MIS_Actual_Cost_Staging_AvgItem varchar 10Transport money 8DistributionCosts money 8Advertising money 8Promotion money 8PRSponsoring money 10GeneralExpenses money 8MIS_Actual_Cost_Staging_StdItem varchar 10Item_Rollup varchar 10Milk money 8Ingredients money 8Transferred money 8Packaging money 8Labour money 8Depreciation money 8Energy money 8FixedOH money 8FixedDirOH money 8ShWhs money 8MIS_Item_AttributesItem varchar 10Item_Class varchar 10Item_Regrade varchar 1MIS_System_ParametersRun_Type varchar 10Run_Period varchar 10First, save the following as views with the corresponding namesqryMIS_ACDM_ExpensesSELECT Item, MAX(Period) AS V_MAX_PeriodFROM dbo.MIS_Actual_Cost_Daily_MeasuresWHERE (COSTTYPE='ITEM') AND (GeneralExpenses_Perc_Cost <> 0)GROUP BY ItemqryMIS_ACDM_SponsoringSELECT Item, MAX(Period) AS V_MAX_PeriodFROM dbo.MIS_Actual_Cost_Daily_MeasuresWHERE (COSTTYPE='ITEM') AND (PrSponsoring_Perc_Cost <> 0)GROUP BY ItemqryMIS_ACDM_PromotionSELECT Item, MAX(Period) AS V_MAX_PeriodFROM dbo.MIS_Actual_Cost_Daily_MeasuresWHERE (COSTTYPE='ITEM') AND (Promotion_Perc_Cost <> 0)GROUP BY ItemqryMIS_ACDM_AdvertisingSELECT Item, MAX(Period) AS V_MAX_PeriodFROM dbo.MIS_Actual_Cost_Daily_MeasuresWHERE (COSTTYPE='ITEM') AND (Advertising_Perc_Cost <> 0)GROUP BY ItemqryMIS_ACDM_FixedCostsSELECT Item, MAX(Period) AS V_MAX_PeriodFROM dbo.MIS_Actual_Cost_Daily_MeasuresWHERE (COSTTYPE='ITEM') AND (ProdFixedCosts_KgL_Cost <> 0)GROUP BY ItemqryMIS_ACDM_DepreciationSELECT Item, MAX(Period) AS V_MAX_PeriodFROM dbo.MIS_Actual_Cost_Daily_MeasuresWHERE (COSTTYPE='ITEM') AND (FactDepreciation_KgL_Cost <> 0)GROUP BY ItemqryMIS_ACDM_DistributionSELECT Item, MAX(Period) AS V_MAX_PeriodFROM dbo.MIS_Actual_Cost_Daily_MeasuresWHERE (COSTTYPE='ITEM') AND (DistributionCosts_KgL_Cost <> 0)GROUP BY ItemqryMIS_ACDM_TransportSELECT Item, MAX(Period) AS V_MAX_PeriodFROM dbo.MIS_Actual_Cost_Daily_MeasuresWHERE (COSTTYPE='ITEM') AND (Transport_KgL_Cost <> 0)GROUP BY ItemqryMIS_ACDM_EnergySELECT Item, MAX(Period) AS V_MAX_PeriodFROM dbo.MIS_Actual_Cost_Daily_MeasuresWHERE (COSTTYPE='ITEM') AND (Energy_KgL_Cost <> 0)GROUP BY ItemqryMIS_ACDM_DirectLabourSELECT Item, MAX(Period) AS V_MAX_PeriodFROM dbo.MIS_Actual_Cost_Daily_MeasuresWHERE (COSTTYPE='ITEM') AND (DirectLabour_KgL_Cost <> 0)GROUP BY ItemqryMIS_ACDM_PackagingSELECT Item, MAX(Period) AS V_MAX_PeriodFROM dbo.MIS_Actual_Cost_Daily_MeasuresWHERE (COSTTYPE='ITEM') AND (Packaging_KgL_Cost <> 0)GROUP BY ItemqryMIS_ACDM_RawMaterialSELECT Item, MAX(Period) AS V_MAX_PeriodFROM dbo.MIS_Actual_Cost_Daily_MeasuresWHERE (COSTTYPE='ITEM') AND (RawMaterial_KgL_Cost <> 0)GROUP BY ItemqryMIS_ACS_AvgStdSELECT ItemFROM dbo.MIS_Actual_Cost_Staging_AvgGROUP BY ItemUNION ALLSELECT ItemFROM dbo.MIS_Actual_Cost_Staging_StdGROUP BY ItemSecond, save the following as views with the corresponding namesqryMIS_ACDM_Expenses_CostSELECT 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_PeriodFROM 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_PeriodWHERE (dbo.qryMIS_ACDM_Expenses.V_Max_Period IS NOT NULL)qryMIS_ACDM_Sponsoring_CostSELECT 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_PeriodFROM 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_PeriodWHERE (dbo.qryMIS_ACDM_Sponsoring.V_Max_Period IS NOT NULL)qryMIS_ACDM_Promotion_CostSELECT 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_PeriodFROM 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_PeriodWHERE (dbo.qryMIS_ACDM_Promotion.V_Max_Period IS NOT NULL)qryMIS_ACDM_Advertising_CostSELECT 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_PeriodFROM 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_PeriodWHERE (dbo.qryMIS_ACDM_Advertising.V_Max_Period IS NOT NULL)qryMIS_ACDM_FixedCosts_CostSELECT 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_PeriodFROM 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_PeriodWHERE (dbo.qryMIS_ACDM_FixedCosts.V_Max_Period IS NOT NULL)qryMIS_ACDM_Depreciation_CostSELECT 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_PeriodFROM 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_PeriodWHERE (dbo.qryMIS_ACDM_Depreciation.V_Max_Period IS NOT NULL)qryMIS_ACDM_Distribution_CostSELECT 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_PeriodFROM 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_PeriodWHERE (dbo.qryMIS_ACDM_Distribution.V_Max_Period IS NOT NULL)qryMIS_ACDM_Transport_CostSELECT 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_PeriodFROM 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_PeriodWHERE (dbo.qryMIS_ACDM_Transport.V_Max_Period IS NOT NULL)qryMIS_ACDM_Energy_CostSELECT 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_PeriodFROM 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_PeriodWHERE (dbo.qryMIS_ACDM_Energy.V_Max_Period IS NOT NULL)qryMIS_ACDM_DirectLabour_CostSELECT 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_PeriodFROM 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_PeriodWHERE (dbo.qryMIS_ACDM_DirectLabour.V_Max_Period IS NOT NULL)qryMIS_ACDM_Packaging_CostSELECT 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_PeriodFROM 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_PeriodWHERE (dbo.qryMIS_ACDM_Packaging.V_Max_Period IS NOT NULL)qryMIS_ACDM_RawMaterial_CostSELECT 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_PeriodFROM 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_PeriodWHERE (dbo.qryMIS_ACDM_RawMaterial.V_Max_Period IS NOT NULL)qryMIS_ACSSSELECT 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_RollupFROM 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_CodeqryMIS_ACS_AvgStd_GroupSELECT Item AS Grouped_ItemFROM dbo.qryMIS_ACS_AvgStdGROUP BY ItemThird, save the following as views with the corresponding namesqryMIS_ACS_CostsSELECT 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_CostFROM 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.ItemWHERE (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 namesqryMIS_ACS_Costs_AvgSELECT 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_AvgCalcFROM dbo.qryMIS_ACS_CostsGROUP BY Item_ClassFifth, save the following as views with the corresponding namesqryMIS_ACS_Costs_ItemMaxSELECT 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_PeriodFROM 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_ParametersWHERE (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 ClassAvgCostwhen Grouped_Item!=Item_Rollupthen coalesce(V_RawMaterial_KgL_Cost_Rollup,0) If not Rollup then it gets the rollup costelse coalesce((Milk+Ingredients+Transferred),0) Else just gets the normal costend as V_RawMaterial_KgL_Cost,case when Item_Regrade = 'Y' then coalesce(V_Packaging_KgL_Cost_AvgCalc,0)when Grouped_Item!=Item_Rollupthen 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_Rollupthen 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_Rollupthen 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_Rollupthen 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_Rollupthen 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_Rollupthen 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,0from qryMIS_ACS_Costs_ItemMaxI can't fully test any of this because I don't havethe specification for the original tables or any sample data and I don't have query analyzer installedto verify the IF and Case statements in the final partAt 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 courseyou take over securityharrybowyer@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.