Doubling of value when source has two lines

• Ralph L. Wahlert

  SSC-Addicted

  Points: 430

  More actions

  October 26, 2011 at 2:43 pm

  #246948

  I have a query when run will return an incorrect value for the "Gross Profit" field. This is happening because the source data will have multiple lines. I am using aggregate functions to sum different values from the data, but since the Gross Profit is on every line the values is correct in the final query. I cannot use an aggregate function on the Gross Profit field because there are aggregates used within the calculation.

  The query is show below:

  SELECT

  isnull(InvoiceNumber,'') InvoiceNumber,

  CustNumber,

  --Customer,

  InvoiceDate,

  OrderNumber,

  itemnmbr,

  lnitmseq,

  ITMCLSCD,

  UOFM,

  PUOFM,

  LOCNCODE,

  isnull([ICO Marks],'') [ICO Marks],

  PRSTADCD,

  Price_Per_Pricing_U_of_M as Price_Per_Pricing_U_of_M,

  BaseUomWeightInPriceUom as BaseUomWeightInPriceUom,

  AVG([Landed Cost]) AS [Landed Cost],

  AVG([Unit Price]) AS [Unit Price],

  sum(Quantity) AS Quantity,

  AVG([Volume/Kilos]) as [VolumeKilos],

  AVG([Volume/Lbs]) as [VolumeLbs],

  sum(Add_Trucking) AS Act_Trucking,

  sum(Add_WH_Chgs) AS Act_WH_Chgs,

  sum(Customs_Exam) AS Act_Customs_Exam,

  sum(Misc) AS Act_Misc,

  AVG(SOTRXLINE_1_Est_FOB) AS Est_FOB,

  AVG(SOTRXLINE_2_Est_Transportation) AS Est_Transportation,

  AVG(SOTRXLINE_3_Add_Trucking) AS Est_Trucking,

  AVG(SOTRXLINE_4_Add_WH_Chgs) AS Est_WH_Chgs,

  AVG(SOTRXLINE_5_Customs_Exams) AS Est_Customs_Exams,

  AVG(SOTRXLINE_6_Misc) AS Est_Misc,

  sum(Add_Trucking) + sum(Add_WH_Chgs) + sum(Customs_Exam) + sum(Misc) as Total_Add_Expenses,

  (sum(Add_Trucking) + sum(Add_WH_Chgs) + sum(Customs_Exam) + sum(Misc)) / avg([Volume/Kilos]) as ExpensesPerKilo,

  AVG(subtotal)AS SaleAmnt, --sum(Quantity) * AVG([Unit Price]) AS SaleAmnt,

  CASE

  WHEN InvoiceNumber IS NULL THEN sum(Quantity) * (AVG([Unit Price]) - AVG(SOTRXLINE_1_Est_FOB) - AVG(SOTRXLINE_2_Est_Transportation)) - AVG(SOTRXLINE_3_Add_Trucking) - AVG(SOTRXLINE_4_Add_WH_Chgs)

  - AVG(SOTRXLINE_5_Customs_Exams) - AVG(SOTRXLINE_6_Misc)

  ELSE (sum(Quantity) * (avg([Unit Price]) - AVG([Landed Cost])))

  - (CASE

  WHEN SUM([Add_Trucking]) = 0 THEN AVG(SOTRXLINE_3_Add_Trucking)

  ELSE SUM([Add_Trucking])

  END)

  - (CASE

  WHEN SUM([Add_WH_Chgs]) = 0 THEN AVG(SOTRXLINE_4_Add_WH_Chgs)

  ELSE SUM([Add_WH_Chgs])

  END)

  - (CASE

  WHEN SUM([Customs_Exam]) = 0 THEN AVG(SOTRXLINE_5_Customs_Exams)

  ELSE SUM([Customs_Exam])

  END)

  - (CASE

  WHEN SUM([Misc]) = 0 THEN AVG(SOTRXLINE_6_Misc)

  ELSE SUM([Misc])

  END)

  END as GrossProfit,

  case

  when sum(Quantity) * AVG([Unit Price]) = 0 then 0

  else

  CASE

  WHEN InvoiceNumber IS NULL THEN sum(Quantity) * (AVG([Unit Price]) - AVG(SOTRXLINE_1_Est_FOB) - AVG(SOTRXLINE_2_Est_Transportation)) - AVG(SOTRXLINE_3_Add_Trucking) - AVG(SOTRXLINE_4_Add_WH_Chgs)

  - AVG(SOTRXLINE_5_Customs_Exams) - AVG(SOTRXLINE_6_Misc)

  ELSE sum(Quantity) * (avg([Unit Price]) - AVG([Landed Cost]))

  - (CASE

  WHEN SUM([Add_Trucking]) = 0 THEN AVG(SOTRXLINE_3_Add_Trucking)

  ELSE SUM([Add_Trucking])

  END)

  - (CASE

  WHEN SUM([Add_WH_Chgs]) = 0 THEN AVG(SOTRXLINE_4_Add_WH_Chgs)

  ELSE SUM([Add_WH_Chgs])

  END)

  - (CASE

  WHEN SUM([Customs_Exam]) = 0 THEN AVG(SOTRXLINE_5_Customs_Exams)

  ELSE SUM([Customs_Exam])

  END)

  - (CASE

  WHEN SUM([Misc]) = 0 THEN AVG(SOTRXLINE_6_Misc)

  ELSE SUM([Misc])

  END)

  END / (sum(Quantity) * AVG([Unit Price])) * 100

  end as 'Margin'

  FROM _TempTable

  GROUP BY InvoiceNumber, CustNumber, InvoiceDate, OrderNumber, itemnmbr, ITMCLSCD, UOFM, LOCNCODE, PRSTADCD, lnitmseq, PUOFM, [ICO Marks],Price_Per_Pricing_U_of_M, BaseUomWeightInPriceUom

  The table definition is below:

  /****** Object: Table [dbo].[_TempTable] Script Date: 10/26/2011 14:36:21 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  SET ANSI_PADDING OFF

  GO

  CREATE TABLE [dbo].[_TempTable](

  [InvoiceNumber] [char](21) NULL,

  [CustNumber] [char](15) NOT NULL,

  [Customer] [char](65) NOT NULL,

  [InvoiceDate] [datetime] NOT NULL,

  [OrderNumber] [char](21) NOT NULL,

  [itemnmbr] [char](31) NULL,

  [lnitmseq] [int] NULL,

  [ITMCLSCD] [char](11) NULL,

  [LOCNCODE] [char](11) NULL,

  [PRSTADCD] [char](15) NULL,

  [xtndprce] [numeric](19, 5) NULL,

  [subtotal] [numeric](19, 5) NOT NULL,

  [Landed Cost] [numeric](19, 5) NULL,

  [Unit Price] [numeric](19, 5) NULL,

  [ICO Marks] [char](21) NULL,

  [Price_Per_Pricing_U_of_M] [numeric](19, 5) NULL,

  [BaseUomWeightInPriceUom] [numeric](19, 5) NULL,

  [BaseUomWeightInPriceUom2] [numeric](19, 5) NULL,

  [Quantity] [numeric](38, 5) NULL,

  [UOFM] [char](9) NULL,

  [Volume/Kilos] [numeric](38, 6) NULL,

  [Volume/Lbs] [numeric](38, 6) NULL

  ) ON [PRIMARY]

  SET ANSI_PADDING ON

  ALTER TABLE [dbo].[_TempTable] ADD [PUOFM] [char](9) NULL

  ALTER TABLE [dbo].[_TempTable] ADD [PUOFM2] [char](9) NULL

  ALTER TABLE [dbo].[_TempTable] ADD [Add_Trucking] [numeric](19, 5) NULL

  ALTER TABLE [dbo].[_TempTable] ADD [Add_WH_Chgs] [numeric](19, 5) NULL

  ALTER TABLE [dbo].[_TempTable] ADD [Customs_Exam] [numeric](19, 5) NULL

  ALTER TABLE [dbo].[_TempTable] ADD [Misc] [numeric](19, 5) NULL

  ALTER TABLE [dbo].[_TempTable] ADD [SOTRXLINE_1_Est_FOB] [numeric](19, 5) NOT NULL

  ALTER TABLE [dbo].[_TempTable] ADD [SOTRXLINE_2_Est_Transportation] [numeric](19, 5) NOT NULL

  ALTER TABLE [dbo].[_TempTable] ADD [SOTRXLINE_3_Add_Trucking] [numeric](19, 5) NOT NULL

  ALTER TABLE [dbo].[_TempTable] ADD [SOTRXLINE_4_Add_WH_Chgs] [numeric](19, 5) NOT NULL

  ALTER TABLE [dbo].[_TempTable] ADD [SOTRXLINE_5_Customs_Exams] [numeric](19, 5) NOT NULL

  ALTER TABLE [dbo].[_TempTable] ADD [SOTRXLINE_6_Misc] [numeric](19, 5) NOT NULL

  GO

  SET ANSI_PADDING OFF

  The sample data insert statement is below:

  --===== All Inserts into the IDENTITY column

  --SET IDENTITY_INSERT _TempTable ON

  --===== Insert the test data into the test table

  INSERT INTO _TempTable

  (

  InvoiceNumber,

  CustNumber,

  Customer,

  InvoiceDate,

  OrderNumber,

  itemnmbr,

  lnitmseq,

  ITMCLSCD,

  LOCNCODE,

  PRSTADCD,

  xtndprce,

  subtotal,

  [Landed Cost],

  [Unit Price],

  [ICO Marks],

  Price_Per_Pricing_U_of_M,

  BaseUomWeightInPriceUom,

  BaseUomWeightInPriceUom2,

  Quantity,

  UOFM,

  [Volume/Kilos],

  [Volume/Lbs],

  PUOFM,

  PUOFM2,

  Add_Trucking,

  Add_WH_Chgs,

  Customs_Exam,

  Misc,

  SOTRXLINE_1_Est_FOB,

  SOTRXLINE_2_Est_Transportation,

  SOTRXLINE_3_Add_Trucking,

  SOTRXLINE_4_Add_WH_Chgs,

  SOTRXLINE_5_Customs_Exams,

  SOTRXLINE_6_Misc

  )

  SELECT'13659','GHIRARDELLI ','Ghirardelli Chocolate Company ','Sep 14 2011 12:00AM','7022 ','ST1 (35-KG)','98304','SPRAYDRIED','LA','SHIP','90001.80000','90001.80000','452.60000','500.01000','03-26-0199','6.48000','77.16170','35.00000','180.00000','Ctn(s)','6300.000000','13889.106000','lbs ','kgs ','0.00000','20.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000' Union All

  SELECT'13659','GHIRARDELLI ','Ghirardelli Chocolate Company ','Sep 14 2011 12:00AM','7022 ','ST1 (35-KG)','98304','SPRAYDRIED','LA','SHIP','90001.80000','90001.80000','452.60000','500.01000','03-26-0199','6.48000','77.16170','35.00000','180.00000','Ctn(s)','6300.000000','13889.106000','lbs ','kgs ','848.97000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000' Union All

  SELECT'13660','COMPACT ','Compact Industries, Inc. ','Sep 12 2011 12:00AM','7327 ','ESFF (30-KG)','16384','SPRAYDRIED','WOW','SHIPPING','6646.95000','6646.95000','385.33000','443.13000','3-26-0693','6.70000','66.13860','30.00000','15.00000','Ctn(s)','450.000000','992.079000','lbs ','kgs ','125.00000','0.00000','0.00000','0.00000','373.50000','8.40000','250.00000','2.50000','0.00000','0.00000' Union All

  SELECT'13661','STARBUCKS ','Starbucks Coffee Company ','Sep 8 2011 12:00AM','7320 ','SKU 11009530','65536','SPRAYDRIED','IN-TR COL','NDC','48701.12000','48701.12000','32.18820','32.57600','3-26-0564','2.03600','16.00000','0.42240','1495.00000','Ctn(s)','631.488000','1392.191075','SKU ','kgs ','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000' Union All

  SELECT'13662','STARBUCKS ','Starbucks Coffee Company ','Sep 8 2011 12:00AM','7320 ','SKU 11009529','294912','SPRAYDRIED','IN-TR COL','NDC','67653.12000','67653.12000','36.38680','36.76800','3-26-0564','2.29800','16.00000','0.42240','1840.00000','Ctn(s)','777.216000','1713.465938','SKU ','kgs ','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000' Union All

  SELECT'13663','STARBUCKS ','Starbucks Coffee Company ','Sep 8 2011 12:00AM','7320 ','SKU 11009768','81920','SPRAYDRIED','IN-TR COL','NDC','31093.20000','31093.20000','204.67400','207.28800','3-26-0564','2.87900','72.00000','2.85120','150.00000','Ctn(s)','427.680000','942.871882','SKU ','kgs ','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000' Union All

  SELECT'13707','INGREDIENTSINTL','Batory Foods - Chicago Sweeteners Division ','Sep 20 2011 12:00AM','7338 ','CCL-SD/FC/1 (33-KG)','16384','SPRAYDRIED','LA','GARLAND, TX','5596.08000','5596.08000','270.73000','466.34000','14-420-613','6.41000','72.75246','33.00000','12.00000','Ctn(s)','396.000000','873.029520','lbs ','kgs ','0.00000','12.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000' Union All

  SELECT'13707','INGREDIENTSINTL','Batory Foods - Chicago Sweeteners Division ','Sep 20 2011 12:00AM','7338 ','CCL-SD/FC/1 (33-KG)','16384','SPRAYDRIED','LA','GARLAND, TX','5596.08000','5596.08000','270.73000','466.34000','14-420-613','6.41000','72.75246','33.00000','12.00000','Ctn(s)','396.000000','873.029520','lbs ','kgs ','227.94000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000','0.00000' Union All

  SELECT'13783','LYONS-MAGNUS ','Lyons Magnus ','Oct 1 2011 12:00AM','7311 ','SEDR (35-KG)','16384','SPRAYDRIED','DROP','SHIP','134168.40000','134168.40000','1093.09000','1118.07000','3-26-0574','14.49000','77.16170','35.00000','120.00000','Ctn(s)','4200.000000','9259.404000','lbs ','kgs ','0.00000','0.00000','222.00000','0.00000','1062.95000','30.15000','0.00000','0.00000','1355.60000','158.32000' Union All

  SELECT'13783','LYONS-MAGNUS ','Lyons Magnus ','Oct 1 2011 12:00AM','7311 ','SEDR (35-KG)','16384','SPRAYDRIED','DROP','SHIP','134168.40000','134168.40000','1093.09000','1118.07000','3-26-0574','14.49000','77.16170','35.00000','120.00000','Ctn(s)','4200.000000','9259.404000','lbs ','kgs ','0.00000','0.00000','1133.60000','0.00000','1062.95000','30.15000','0.00000','0.00000','1355.60000','158.32000'

  --set identity_insert _TempTable ON

  I'm not sure how I can change my query so that I get the correct gross profit amount. Some of the test data returns the correct amount whereas three invoices, 13783, 13707, and 13659 return incorrect values.

  Any thoughts would be appreciated.

  Ralph

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  October 26, 2011 at 3:22 pm

  #1399933

  2 options

  #1 Pre aggregate your data in a derived table and left join the main query to that.

  #2 (My current favorite) is to use an outer apply.

  In my environement the perf was close enough in all my test to generalise the code to outer applies only (allows to put the code into a function and reuse all over the place, very useful in a reporting env.).