Aggregate functions T-SQL Code Using NULL or 0

Question

Aggregate functions T-SQL Code Using NULL or 0

GLLugo

Old Hand

Points: 397
More actions
January 22, 2008 at 2:49 pm

#185953

Hello, this is my first post and hopefully someone can provide some insight.
We are running this code that is essentially gathering data from a few different databases on SQL 2005 , and avg out some of the values. The weird thing is that everytime we run the query sum of the AVG_SALE_PRICE changes , the data we're testing with is static. There are no updates occurring on it by end users or anyone else the IT dept.
The aggregate functions I am assuming are causing the diff value , since AVG isn't taking NULLS into consideration so it may be skewing my results. If you could take a peek at my code it would be greatly appreciated for any ideas.
thanks
/* Step1. Create Master Table for Stock Detail */
USE [InventoryDM]
GO
/****** Object: Table [dbo].[AirframeV] Script Date: 01/10/2008 16:04:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE dbo.MasterStockDetail
DROP TABLE dbo.AirframeSalehistory_ARRP
DROP TABLE dbo.AirframeSalehistory_SVOH
DROP TABLE dbo.AirframeCustomerQuote_ARRP
DROP TABLE dbo.AirframeCustomerQuote_SVOH
DROP TABLE dbo.AirframeinAvgCalculationSalehistory_ARRP
DROP TABLE dbo.AirframeAVGSalehistory_ARRP_Upper_Lower
DROP TABLE dbo.AirframeFinal2ndAVGSalehistory_ARRP
DROP TABLE dbo.AirframeinAvgCalculationSalehistory_SVOH
DROP TABLE dbo.AirframeAVGSalehistory_SVOH_Upper_Lower
DROP TABLE dbo.AirframeFinal2ndAVGSalehistory_SVOH
DROP TABLE dbo.AirframeinAvgCalculationCustomerQuote_ARRP
DROP TABLE dbo.AirframeisGreaterThan3Quotes
DROP TABLE dbo.AirframeisLessThan3Quotes
DROP TABLE dbo.AirframeAVGCustomerQuote_ARRP_Upper_Lower
DROP TABLE dbo.AirframeFinal2ndAVGCustomerQuote_ARRP
DROP TABLE dbo.AirframeinAvgCalculationCustomerQuote_SVOH
DROP TABLE dbo.AirframeisGreaterThan3QuotesSVOH
DROP TABLE dbo.AirframeisLessThan3Quotes_SVOH
DROP TABLE dbo.AirframeAVGCustomerQuote_SVOH_Upper_Lower
DROP TABLE dbo.AirframeFinal2ndAVGCustomerQuote_SVOH
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MasterStockDetail]') )
drop table [dbo].[MasterStockDetail]
GO
CREATE TABLE [dbo].[MasterStockDetail]
(
[PN] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PNM_AUTO_KEY] [int] NOT NULL,
[DESCRIPTION] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CONDITION_CODE] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OVHL_COST] [numeric](19, 4) NULL,
[UNIT_COST] [numeric](19, 4) NULL,
[SERIAL_NUMBER] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[REC_DATE] [datetime] NULL,
[SI_NUMBER] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MFG_CODE] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[STOCK_CATEGORY_CODE] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Expr1] [numeric](21, 4) NULL,
[SHIP_DATE] [datetime] NULL,
[UNIT_PRICE] [numeric](19, 4) NULL,
[AVG_SALE_PRICE] [numeric](18, 0) NULL,
[SOURCE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[STOCK_LINE] [nvarchar] (50) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[MasterStockDetail]
SELECT DISTINCT a.*
FROM
(
SELECT STOCK.PN, PARTS_MASTER.PNM_AUTO_KEY, STOCK.DESCRIPTION, PART_CONDITION_CODES.CONDITION_CODE, ROUND(STOCK.OVHL_COST,2) AS OVHL_COST,
ROUND(STOCK.UNIT_COST,2) AS UNIT_COST, STOCK.SERIAL_NUMBER, STOCK.REC_DATE, WO_OPERATION.SI_NUMBER, MANUFACTURER.MFG_CODE,
STOCK_CATEGORY_CODES.STOCK_CATEGORY_CODE, ISNULL(ROUND(ISNULL(ROUND(RO_DETAIL.LABOR_COST,2), 0.0) + ISNULL(ROUND(RO_DETAIL.MISC_COST,2), 0.0) + ISNULL(ROUND(RO_DETAIL.PARTS_COST,2), 0.0),2), 0.0) AS EXPR1,
SO_DETAIL.SHIP_DATE, ROUND(SO_DETAIL.UNIT_PRICE,2) AS SO_UNIT_PRICE, NULL AS EXPR2, NULL AS EXPR3, STOCK.STOCK_LINE
FROM quantum.dbo.STOCK_RESERVATIONS STOCK_RESERVATIONS_1 INNER JOIN
quantum.dbo.SO_DETAIL ON STOCK_RESERVATIONS_1.SOD_AUTO_KEY = SO_DETAIL.SOD_AUTO_KEY RIGHT OUTER JOIN
quantum.dbo.RO_DETAIL RIGHT OUTER JOIN
quantum.dbo.WO_OPERATION WO_OPERATION INNER JOIN
quantum.dbo.STOCK_RESERVATIONS STOCK_RESERVATIONS ON WO_OPERATION.WOO_AUTO_KEY = STOCK_RESERVATIONS.WOO_AUTO_KEY INNER JOIN
quantum.dbo.STOCK STOCK_1 ON STOCK_RESERVATIONS.STM_AUTO_KEY = STOCK_1.STM_AUTO_KEY INNER JOIN
quantum.dbo.PARTS_MASTER PARTS_MASTER INNER JOIN
quantum.dbo.MANUFACTURER MANUFACTURER ON PARTS_MASTER.MFG_AUTO_KEY = MANUFACTURER.MFG_AUTO_KEY INNER JOIN
quantum.dbo.STOCK STOCK ON PARTS_MASTER.PNM_AUTO_KEY = STOCK.PNM_AUTO_KEY INNER JOIN
quantum.dbo.STOCK_CATEGORY_CODES STOCK_CATEGORY_CODES ON STOCK.STC_AUTO_KEY = STOCK_CATEGORY_CODES.STC_AUTO_KEY ON
STOCK_1.STM_AUTO_KEY = STOCK.STM_LOT INNER JOIN
quantum.dbo.PART_CONDITION_CODES PART_CONDITION_CODES ON STOCK.PCC_AUTO_KEY = PART_CONDITION_CODES.PCC_AUTO_KEY ON
RO_DETAIL.ROD_AUTO_KEY = STOCK.ROD_AUTO_KEY ON STOCK_RESERVATIONS_1.STM_AUTO_KEY = STOCK.STM_AUTO_KEY
WHERE (UPPER(STOCK_CATEGORY_CODES.DESCRIPTION) = 'AIRFRAME')
AND (PART_CONDITION_CODES.CONDITION_CODE IN ('OH', 'SV', 'AR', 'RP'))
AND (STOCK.UNIT_COST >= 0)
AND (STOCK.REC_DATE <= '09/30/2007')
AND (STOCK.REC_DATE >= '09/30/2004')
AND (SO_DETAIL.SHIP_DATE IS NULL OR SO_DETAIL.SHIP_DATE >= '10/01/2007')
AND (STOCK.QTY_OH >=1)
UNION
SELECT STOCK.PN, PARTS_MASTER.PNM_AUTO_KEY, STOCK.DESCRIPTION, PART_CONDITION_CODES.CONDITION_CODE, ROUND(STOCK.OVHL_COST,2) AS OVHL_COST,
ROUND(STOCK.UNIT_COST,2) AS UNIT_COST, STOCK.SERIAL_NUMBER, STOCK.REC_DATE, WO_OPERATION.SI_NUMBER, MANUFACTURER.MFG_CODE,
STOCK_CATEGORY_CODES.STOCK_CATEGORY_CODE, ISNULL(ROUND(ISNULL(ROUND(RO_DETAIL.LABOR_COST,2), 0.0) + ISNULL(ROUND(RO_DETAIL.MISC_COST,2), 0.0) + ISNULL(ROUND(RO_DETAIL.PARTS_COST,2), 0.0),2), 0.0) AS EXPR1,
SO_DETAIL.SHIP_DATE, ROUND(SO_DETAIL.UNIT_PRICE,2) AS SO_UNIT_PRICE, NULL AS EXPR2, NULL AS EXPR3, STOCK.STOCK_LINE
FROM quantum.dbo.STOCK_RESERVATIONS STOCK_RESERVATIONS_1 INNER JOIN
quantum.dbo.SO_DETAIL ON STOCK_RESERVATIONS_1.SOD_AUTO_KEY = SO_DETAIL.SOD_AUTO_KEY RIGHT OUTER JOIN
quantum.dbo.RO_DETAIL RIGHT OUTER JOIN
quantum.dbo.WO_OPERATION WO_OPERATION INNER JOIN
quantum.dbo.STOCK_RESERVATIONS STOCK_RESERVATIONS ON WO_OPERATION.WOO_AUTO_KEY = STOCK_RESERVATIONS.WOO_AUTO_KEY INNER JOIN
quantum.dbo.STOCK STOCK_1 ON STOCK_RESERVATIONS.STM_AUTO_KEY = STOCK_1.STM_AUTO_KEY INNER JOIN
quantum.dbo.PARTS_MASTER PARTS_MASTER INNER JOIN
quantum.dbo.MANUFACTURER MANUFACTURER ON PARTS_MASTER.MFG_AUTO_KEY = MANUFACTURER.MFG_AUTO_KEY INNER JOIN
quantum.dbo.STOCK STOCK ON PARTS_MASTER.PNM_AUTO_KEY = STOCK.PNM_AUTO_KEY INNER JOIN
quantum.dbo.STOCK_CATEGORY_CODES STOCK_CATEGORY_CODES ON STOCK.STC_AUTO_KEY = STOCK_CATEGORY_CODES.STC_AUTO_KEY ON
STOCK_1.STM_AUTO_KEY = STOCK.STM_LOT INNER JOIN
quantum.dbo.PART_CONDITION_CODES PART_CONDITION_CODES ON STOCK.PCC_AUTO_KEY = PART_CONDITION_CODES.PCC_AUTO_KEY ON
RO_DETAIL.ROD_AUTO_KEY = STOCK.ROD_AUTO_KEY ON STOCK_RESERVATIONS_1.STM_AUTO_KEY = STOCK.STM_AUTO_KEY
WHERE (UPPER(STOCK_CATEGORY_CODES.DESCRIPTION) = 'AIRFRAME')
AND (PART_CONDITION_CODES.CONDITION_CODE IN ('OH', 'SV', 'AR', 'RP'))
AND (STOCK.UNIT_COST >= 0)
AND (STOCK.REC_DATE <= '09/30/2007')
AND (STOCK.REC_DATE >= '09/30/2004')
AND (SO_DETAIL.SHIP_DATE >= '10/01/2007')
AND (STOCK.QTY_OH =0)
) a
ORDER BY PN, CONDITION_CODE
/**********************************************************************************/
/* Create Four summary result tables for Average Pricing */
/**********************************************************************************/
/* Step 2a. SalesHistory Summary for AR RP */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AirframeSalehistory_ARRP]') )
drop table [dbo].[AirframeSalehistory_ARRP]
GO
CREATE TABLE [dbo].[AirframeSalehistory_ARRP](
[PN] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CONDITION_CODE] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UNIT_PRICE] [numeric](19, 4) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[AirframeSalehistory_ARRP]
SELECT PN, CONDITION_CODE, ROUND(UNIT_PRICE,2) AS UNIT_PRICE
FROM MasterStockDetail
WHERE CONDITION_CODE in ('AR','RP')
/* Step 2b. SalesHistory Summary for SV OH */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AirframeSalehistory_SVOH]') )
drop table [dbo].[AirframeSalehistory_SVOH]
GO
CREATE TABLE [dbo].[AirframeSalehistory_SVOH](
[PN] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CONDITION_CODE] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UNIT_PRICE] [numeric](19, 4) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[AirframeSalehistory_SVOH]
SELECT PN, CONDITION_CODE, ROUND(UNIT_PRICE,2) AS UNIT_PRICE
FROM MasterStockDetail
WHERE CONDITION_CODE in ('SV','OH')
/* Step 2c. Customer Quote Summary for AR RP */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AirframeCustomerQuote_ARRP]') )
drop table [dbo].[AirframeCustomerQuote_ARRP]
GO
CREATE TABLE [dbo].[AirframeCustomerQuote_ARRP](
[PN] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PNM_AUTO_KEY] [int] NOT NULL,
[CONDITION_CODE] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UNIT_PRICE] [numeric](19, 4) NULL,
[CustQuote_number] [nvarchar](20)
) ON [PRIMARY]
INSERT INTO [dbo].[AirframeCustomerQuote_ARRP]
SELECT DISTINCT MASTERSTOCKDETAIL.PN
, MASTERSTOCKDETAIL.pnm_auto_key
, PART_CONDITION_CODES.CONDITION_CODE
, ROUND(CQ_DETAIL.UNIT_PRICE,2) AS UNIT_PRICE
, CQ_HEADER.CQ_NUMBER
FROM quantum.dbo.CQ_DETAIL INNER JOIN
quantum.dbo.CQ_HEADER ON CQ_DETAIL.CQH_AUTO_KEY = CQ_HEADER.CQH_AUTO_KEY INNER JOIN
MASTERSTOCKDETAIL ON CQ_DETAIL.PNM_AUTO_KEY = MASTERSTOCKDETAIL.PNM_AUTO_KEY INNER JOIN
quantum.dbo.PART_CONDITION_CODES ON CQ_DETAIL.PCC_AUTO_KEY = PART_CONDITION_CODES.PCC_AUTO_KEY
WHERE MASTERSTOCKDETAIL.pnm_auto_key = CQ_DETAIL.pnm_auto_key
AND PART_CONDITION_CODES.CONDITION_CODE in ('AR','RP')
AND CQ_DETAIL.QUOTE_DATE >= '09/30/2004'
/* Step 2d. Customer Quote Summary for SV OH */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AirframeCustomerQuote_SVOH]') )
drop table [dbo].[AirframeCustomerQuote_SVOH]
GO
CREATE TABLE [dbo].[AirframeCustomerQuote_SVOH](
[PN] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PNM_AUTO_KEY] [int] NOT NULL,
[CONDITION_CODE] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UNIT_PRICE] [numeric](19, 4) NULL,
[CustQuote_number] [nvarchar](20)
) ON [PRIMARY]
INSERT INTO [dbo].[AirframeCustomerQuote_SVOH]
SELECT DISTINCT MASTERSTOCKDETAIL.PN
, MASTERSTOCKDETAIL.pnm_auto_key
, PART_CONDITION_CODES.CONDITION_CODE
, ROUND(CQ_DETAIL.UNIT_PRICE,2) AS UNIT_PRICE
, CQ_HEADER.CQ_NUMBER
FROM quantum.dbo.CQ_DETAIL INNER JOIN
quantum.dbo.CQ_HEADER ON CQ_DETAIL.CQH_AUTO_KEY = CQ_HEADER.CQH_AUTO_KEY INNER JOIN
MASTERSTOCKDETAIL ON CQ_DETAIL.PNM_AUTO_KEY = MASTERSTOCKDETAIL.PNM_AUTO_KEY INNER JOIN
quantum.dbo.PART_CONDITION_CODES ON CQ_DETAIL.PCC_AUTO_KEY = PART_CONDITION_CODES.PCC_AUTO_KEY
WHERE MASTERSTOCKDETAIL.pnm_auto_key = CQ_DETAIL.pnm_auto_key
AND PART_CONDITION_CODES.CONDITION_CODE in ('SV','OH')
AND CQ_DETAIL.QUOTE_DATE >= '09/30/2004'
/**********************************************************************************/
/* Step 3, 4 & 5 Create Upper and Lower for 20% Average Pricing */
/**********************************************************************************/
/* Step 3a. Create Flaggable Prices for Sales History AR RP */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AirframeinAvgCalculationSalehistory_ARRP]') )
drop table [dbo].[AirframeinAvgCalculationSalehistory_ARRP]
GO
CREATE TABLE [dbo].[AirframeinAvgCalculationSalehistory_ARRP](
[PN] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UNIT_PRICE] [numeric](19, 4) NULL,
[inCalulation] [varchar](1)
) ON [PRIMARY]
INSERT INTO [dbo].[AirframeinAvgCalculationSalehistory_ARRP]
SELECT PN, ROUND(UNIT_PRICE,2) AS UNIT_PRICE, 'F' AS inCalculation
FROM dbo.MasterStockDetail
WHERE CONDITION_CODE in ('AR','RP')
/* Step 4a. SalesHistory AVG_UpperLowerSummary for AR RP */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AirframeAVGSalehistory_ARRP_Upper_Lower]') )
drop table [dbo].[AirframeAVGSalehistory_ARRP_Upper_Lower]
GO
CREATE TABLE [dbo].[AirframeAVGSalehistory_ARRP_Upper_Lower](
[PN] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AVG_SALE_PRICE] [numeric](19, 4) NULL,
[UPPER_AVG] [numeric](19, 4) NULL,
[LOWER_AVG] [numeric](19, 4) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[AirframeAVGSalehistory_ARRP_Upper_Lower]
SELECT PN
, ISNULL(ROUND(AVG(UNIT_PRICE),2),0) AS AVG_SALE_PRICE
, ISNULL(ROUND(ROUND(AVG(UNIT_PRICE),2) * 1.2,2),0) as Upper_AVG
, ISNULL(ROUND(ROUND(AVG(UNIT_PRICE),2)*.8,2),0) as Lower_Avg
FROM MasterStockDetail
WHERE CONDITION_CODE in ('AR','RP')
--AND UNIT_PRICE IS NOT NULL
GROUP BY PN
/*
To check valid data range
SELECT a.*, b.*
FROM AirframeinAvgCalculationSalehistory_ARRP a
, AirframeAVGSalehistory_ARRP_Upper_Lower b
WHERE a.pn = b.pn
AND a.unit_price <= b.upper_avg
AND a.unit_price >= b.lower_avg
*/
UPDATE dbo.AirframeinAvgCalculationSalehistory_ARRP
SET inCalulation = 'T'
FROM dbo.AirframeinAvgCalculationSalehistory_ARRP a
, dbo.AirframeAVGSalehistory_ARRP_Upper_Lower b
WHERE a.pn = b.pn
AND a.unit_price <= b.upper_avg
AND a.unit_price >= b.lower_avg
/*
Seeing what values got affected
SELECT *
FROM AirframeinAvgCalculationSalehistory_ARRP
WHERE inCalulation = 'T'
*/
/* Step 5a. Create Final 2nd Pass Average Table for AR RP */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AirframeFinal2ndAVGSalehistory_ARRP]') )
drop table [dbo].[AirframeFinal2ndAVGSalehistory_ARRP]
GO
CREATE TABLE [dbo].[AirframeFinal2ndAVGSalehistory_ARRP](
[PN] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AVG_SALE_PRICE] [numeric](19, 4) NULL,
) ON [PRIMARY]
INSERT INTO [dbo].[AirframeFinal2ndAVGSalehistory_ARRP]
SELECT PN,ROUND(AVG(UNIT_PRICE),2) AS AVG_SALE_PRICE
FROM dbo.AirframeinAvgCalculationSalehistory_ARRP
WHERE inCalulation = 'T'
GROUP BY PN
/******************************************************************************************************/
/* Step 3b. Create Flaggable Prices for Sales History SV OH */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AirframeinAvgCalculationSalehistory_SVOH]') )
drop table [dbo].[AirframeinAvgCalculationSalehistory_SVOH]
GO
CREATE TABLE [dbo].[AirframeinAvgCalculationSalehistory_SVOH](
[PN] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UNIT_PRICE] [numeric](19, 4) NULL,
[inCalulation] [varchar](1)
) ON [PRIMARY]
INSERT INTO [dbo].[AirframeinAvgCalculationSalehistory_SVOH]
SELECT PN, ROUND(UNIT_PRICE,2) AS UNIT_PRICE, 'F' AS inCalculation
FROM dbo.MasterStockDetail
WHERE CONDITION_CODE in ('SV','OH')
/* Step 4a. SalesHistory AVG_UpperLowerSummary for SV OH */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AirframeAVGSalehistory_SVOH_Upper_Lower]') )
drop table [dbo].[AirframeAVGSalehistory_SVOH_Upper_Lower]
GO
CREATE TABLE [dbo].[AirframeAVGSalehistory_SVOH_Upper_Lower](
[PN] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AVG_SALE_PRICE] [numeric](19, 4) NULL,
[UPPER_AVG] [numeric](19, 4) NULL,
[LOWER_AVG] [numeric](19, 4) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[AirframeAVGSalehistory_SVOH_Upper_Lower]
SELECT PN, ROUND(AVG(UNIT_PRICE),2) AS AVG_SALE_PRICE
, ROUND(ROUND(AVG(UNIT_PRICE),2) * 1.2,2) as Upper_AVG
, ROUND(ROUND(AVG(UNIT_PRICE),2) * 0.8,2) as Lower_Avg
FROM dbo.MasterStockDetail
WHERE CONDITION_CODE in ('SV','OH')
--AND UNIT_PRICE IS NOT NULL
GROUP BY PN
/*
To check valid data range
SELECT a.*, b.*
FROM AirframeinAvgCalculationSalehistory_SVOH a
, AirframeAVGSalehistory_SVOH_Upper_Lower b
WHERE a.pn = b.pn
AND a.unit_price <= b.upper_avg
AND a.unit_price >= b.lower_avg
*/
UPDATE dbo.AirframeinAvgCalculationSalehistory_SVOH
SET inCalulation = 'T'
FROM dbo.AirframeinAvgCalculationSalehistory_SVOH a
, AirframeAVGSalehistory_SVOH_Upper_Lower b
WHERE a.pn = b.pn
AND a.unit_price <= b.upper_avg
AND a.unit_price >= b.lower_avg
/*
Seeing what values got affected
SELECT *
FROM AirframeinAvgCalculationSalehistory_SVOH
WHERE inCalulation = 'T'
*/
/* Step 5a. Create Final 2nd Pass Average Table for SV OH */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AirframeFinal2ndAVGSalehistory_SVOH]') )
drop table [dbo].[AirframeFinal2ndAVGSalehistory_SVOH]
GO
CREATE TABLE [dbo].[AirframeFinal2ndAVGSalehistory_SVOH](
[PN] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AVG_SALE_PRICE] [numeric](19, 4) NULL,
) ON [PRIMARY]
INSERT INTO [dbo].[AirframeFinal2ndAVGSalehistory_SVOH]
SELECT PN,ROUND(AVG(UNIT_PRICE),2) AS AVG_SALE_PRICE
FROM dbo.AirframeinAvgCalculationSalehistory_SVOH
WHERE inCalulation = 'T'
GROUP BY PN
/******************************************************************************************************/
/* Step 3c. Create Flaggable Prices for Customer Quotes AR RP */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AirframeinAvgCalculationCustomerQuote_ARRP]') )
drop table [dbo].[AirframeinAvgCalculationCustomerQuote_ARRP]
GO
CREATE TABLE [dbo].[AirframeinAvgCalculationCustomerQuote_ARRP](
[PN] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UNIT_PRICE] [numeric](19, 4) NULL,
[inGreaterThan3QuoteFilter] [varchar](1),
[inLessThan3QuoteFilter] [varchar](1),
[inCalulation] [varchar](1)
) ON [PRIMARY]
INSERT INTO [dbo].[AirframeinAvgCalculationCustomerQuote_ARRP]
SELECT PN, ROUND(UNIT_PRICE,2) AS UNIT_PRICE, 'F' AS inGT3Filter, 'F' AS inLT3Filter,'F' AS inCalculation
FROM AirframeCustomerQuote_ARRP
WHERE CONDITION_CODE in ('AR','RP')
/* Create a flaggable table to indicate more than 3 Quotes */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AirframeisGreaterThan3Quotes]') )
drop table [dbo].[AirframeisGreaterThan3Quotes]
GO
CREATE TABLE [dbo].[AirframeisGreaterThan3Quotes](
[PN] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
) ON [PRIMARY]
INSERT INTO [dbo].[AirframeisGreaterThan3Quotes]
SELECT PN
FROM dbo.AirframeinAvgCalculationCustomerQuote_ARRP
GROUP BY PN
HAVING COUNT(PN) >= 3
/* Create a flaggable table to indicate less than 3 Quotes */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AirframeisLessThan3Quotes]') )
drop table [dbo].[AirframeisLessThan3Quotes]
GO
CREATE TABLE [dbo].[AirframeisLessThan3Quotes](
[PN] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
) ON [PRIMARY]
INSERT INTO [dbo].[AirframeisLessThan3Quotes]
SELECT PN
FROM dbo.AirframeinAvgCalculationCustomerQuote_ARRP
GROUP BY PN
HAVING COUNT(PN) < 3
/* Validate Flagging */
/*
SELECT a.*, b.*
FROM AirframeinAvgCalculationCustomerQuote_ARRP a
, AirframeisGreaterThan3Quotes b
WHERE a.pn = b.pn
SELECT a.*, b.*
FROM AirframeinAvgCalculationCustomerQuote_ARRP a
, AirframeisLessThan3Quotes b
WHERE a.pn = b.pn
*/
UPDATE dbo.AirframeinAvgCalculationCustomerQuote_ARRP
SET inGreaterThan3QuoteFilter = 'T'
FROM dbo.AirframeinAvgCalculationCustomerQuote_ARRP a
, dbo.AirframeisGreaterThan3Quotes b
WHERE a.pn = b.pn
UPDATE dbo.AirframeinAvgCalculationCustomerQuote_ARRP
SET inLessThan3QuoteFilter = 'T'
FROM dbo.AirframeinAvgCalculationCustomerQuote_ARRP a
, dbo.AirframeisLessThan3Quotes b
WHERE a.pn = b.pn
/* Step 4c. Customer Quote AVG_UpperLowerSummary for AR RP */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AirframeAVGCustomerQuote_ARRP_Upper_Lower]') )
drop table [dbo].[AirframeAVGCustomerQuote_ARRP_Upper_Lower]
GO
CREATE TABLE [dbo].[AirframeAVGCustomerQuote_ARRP_Upper_Lower](
[PN] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AVG_SALE_PRICE] [numeric](19, 4) NULL,
[UPPER_AVG] [numeric](19, 4) NULL,
[LOWER_AVG] [numeric](19, 4) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[AirframeAVGCustomerQuote_ARRP_Upper_Lower]
SELECT PN, ROUND(AVG(UNIT_PRICE),2) AS AVG_SALE_PRICE
, ROUND(ROUND(AVG(UNIT_PRICE),2) * 1.2,2) as Upper_AVG
, ROUND(ROUND(AVG(UNIT_PRICE),2) * 0.8,2) as Lower_Avg
FROM AirframeCustomerQuote_ARRP
WHERE CONDITION_CODE in ('AR','RP')
--AND UNIT_PRICE IS NOT NULL
GROUP BY PN
/*
To check valid data range
SELECT a.*, b.*
FROM AirframeinAvgCalculationCustomerQuote_ARRP a
, AirframeAVGCustomerQuote_ARRP_Upper_Lower b
WHERE a.pn = b.pn
AND a.unit_price <= b.upper_avg
AND a.unit_price >= b.lower_avg
*/
UPDATE dbo.AirframeinAvgCalculationCustomerQuote_ARRP
SET inCalulation = 'T'
FROM dbo.AirframeinAvgCalculationCustomerQuote_ARRP a
, dbo.AirframeAVGCustomerQuote_ARRP_Upper_Lower b
WHERE a.pn = b.pn
AND a.unit_price <= b.upper_avg
AND a.unit_price >= b.lower_avg
/*
Seeing what values got affected
SELECT *
FROM AirframeinAvgCalculationCustomerQuote_ARRP
WHERE inGreaterThan3QuoteFilter = 'T'
AND inCalulation = 'T'
*/
/* Step 5a. Create Final 2nd Pass Average Table for AR RP */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AirframeFinal2ndAVGCustomerQuote_ARRP]') )
drop table [dbo].[AirframeFinal2ndAVGCustomerQuote_ARRP]
GO
CREATE TABLE [dbo].[AirframeFinal2ndAVGCustomerQuote_ARRP](
[PN] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AVG_SALE_PRICE] [numeric](19, 4) NULL,
) ON [PRIMARY]
INSERT INTO [dbo].[AirframeFinal2ndAVGCustomerQuote_ARRP]
SELECT PN,ROUND(AVG(UNIT_PRICE),2) AS AVG_SALE_PRICE
FROM dbo.AirframeinAvgCalculationCustomerQuote_ARRP
WHERE inGreaterThan3QuoteFilter = 'T'
AND inCalulation = 'T'
GROUP BY PN
INSERT INTO [dbo].[AirframeFinal2ndAVGCustomerQuote_ARRP]
SELECT PN,0 AS AVG_SALE_PRICE
FROM dbo.AirframeisLessThan3Quotes
/*
Validate Customer Quote Avg Price 2nd Pass
SELECT *
FROM AirframeFinal2ndAVGCustomerQuote_ARRP
*/
/******************************************************************************************************/
/* Step 4d. Create Flaggable Prices for Customer Quotes SV OH */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AirframeinAvgCalculationCustomerQuote_SVOH]') )
drop table [dbo].[AirframeinAvgCalculationCustomerQuote_SVOH]
GO
CREATE TABLE [dbo].[AirframeinAvgCalculationCustomerQuote_SVOH](
[PN] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UNIT_PRICE] [numeric](19, 4) NULL,
[inGreaterThan3QuoteFilter] [varchar](1),
[inLessThan3QuoteFilter] [varchar](1),
[inCalulation] [varchar](1),
[CustQuote_number] [nvarchar](20)
) ON [PRIMARY]
INSERT INTO [dbo].[AirframeinAvgCalculationCustomerQuote_SVOH]
SELECT PN, ROUND(UNIT_PRICE,2) AS UNIT_PRICE, 'F' AS inGT3Filter, 'F' AS inLT3Filter,'F' AS inCalculation, CustQuote_number
FROM dbo.AirframeCustomerQuote_SVOH
WHERE CONDITION_CODE in ('SV','OH')
/* Create a flaggable table to indicate more than 3 Quotes */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AirframeisGreaterThan3QuotesSVOH]') )
drop table [dbo].[AirframeisGreaterThan3QuotesSVOH]
GO
CREATE TABLE [dbo].[AirframeisGreaterThan3QuotesSVOH](
[PN] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
) ON [PRIMARY]
INSERT INTO [dbo].[AirframeisGreaterThan3QuotesSVOH]
SELECT PN
FROM dbo.AirframeinAvgCalculationCustomerQuote_SVOH
GROUP BY PN
HAVING COUNT(PN) >= 3
/* Create a flaggable table to indicate less than 3 Quotes */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AirframeisLessThan3Quotes_SVOH]') )
drop table [dbo].[AirframeisLessThan3Quotes_SVOH]
GO
CREATE TABLE [dbo].[AirframeisLessThan3Quotes_SVOH](
[PN] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
) ON [PRIMARY]
INSERT INTO [dbo].[AirframeisLessThan3Quotes_SVOH]
SELECT PN
FROM dbo.AirframeinAvgCalculationCustomerQuote_SVOH
GROUP BY PN
HAVING COUNT(PN) < 3
/* Validate Flagging */
/*
SELECT a.*, b.*
FROM AirframeinAvgCalculationCustomerQuote_SVOH a
, AirframeisGreaterThan3QuotesSVOH b
WHERE a.pn = b.pn
SELECT a.*, b.*
FROM AirframeinAvgCalculationCustomerQuote_SVOH a
, AirframeisLessThan3QuotesSVOH b
WHERE a.pn = b.pn
*/
UPDATE dbo.AirframeinAvgCalculationCustomerQuote_SVOH
SET inGreaterThan3QuoteFilter = 'T'
FROM dbo.AirframeinAvgCalculationCustomerQuote_SVOH a
, dbo.AirframeisGreaterThan3Quotes b
WHERE a.pn = b.pn
UPDATE dbo.AirframeinAvgCalculationCustomerQuote_SVOH
SET inLessThan3QuoteFilter = 'T'
FROM dbo.AirframeinAvgCalculationCustomerQuote_SVOH a
, dbo.AirframeisLessThan3Quotes b
WHERE a.pn = b.pn
/* Step 5d. Customer Quote AVG_UpperLowerSummary for SV OH */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AirframeAVGCustomerQuote_SVOH_Upper_Lower]') )
drop table [dbo].[AirframeAVGCustomerQuote_SVOH_Upper_Lower]
GO
CREATE TABLE [dbo].[AirframeAVGCustomerQuote_SVOH_Upper_Lower](
[PN] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AVG_SALE_PRICE] [numeric](19, 4) NULL,
[UPPER_AVG] [numeric](19, 4) NULL,
[LOWER_AVG] [numeric](19, 4) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[AirframeAVGCustomerQuote_SVOH_Upper_Lower]
SELECT PN, ROUND(AVG(UNIT_PRICE),2) AS AVG_SALE_PRICE
, ROUND(ROUND(AVG(UNIT_PRICE),2) * 1.2,2) as Upper_AVG
, ROUND(ROUND(AVG(UNIT_PRICE),2) * 0.8,2) as Lower_Avg
FROM dbo.AirframeCustomerQuote_SVOH
WHERE CONDITION_CODE in ('SV','OH')
--AND UNIT_PRICE IS NOT NULL
GROUP BY PN
/*
To check valid data range
SELECT a.*, b.*
FROM AirframeinAvgCalculationCustomerQuote_SVOH a
, AirframeAVGCustomerQuote_SVOH_Upper_Lower b
WHERE a.pn = b.pn
AND a.unit_price <= b.upper_avg
AND a.unit_price >= b.lower_avg
*/
UPDATE dbo.AirframeinAvgCalculationCustomerQuote_SVOH
SET inCalulation = 'T'
FROM dbo.AirframeinAvgCalculationCustomerQuote_SVOH a
, dbo.AirframeAVGCustomerQuote_SVOH_Upper_Lower b
WHERE a.pn = b.pn
AND a.unit_price <= b.upper_avg
AND a.unit_price >= b.lower_avg
/*
Seeing what values got affected
SELECT *
FROM AirframeinAvgCalculationCustomerQuote_SVOH
WHERE inGreaterThan3QuoteFilter = 'T'
AND inCalulation = 'T'
*/
/* Step 6d. Create Final 2nd Pass Average Table for SV OH */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AirframeFinal2ndAVGCustomerQuote_SVOH]') )
drop table [dbo].[AirframeFinal2ndAVGCustomerQuote_SVOH]
GO
CREATE TABLE [dbo].[AirframeFinal2ndAVGCustomerQuote_SVOH](
[PN] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AVG_SALE_PRICE] [numeric](19, 4) NULL,
) ON [PRIMARY]
INSERT INTO [dbo].[AirframeFinal2ndAVGCustomerQuote_SVOH]
SELECT PN,ROUND(AVG(UNIT_PRICE),2) AS AVG_SALE_PRICE
FROM dbo.AirframeinAvgCalculationCustomerQuote_SVOH
WHERE inGreaterThan3QuoteFilter = 'T'
AND inCalulation = 'T'
GROUP BY PN
INSERT INTO [dbo].[AirframeFinal2ndAVGCustomerQuote_SVOH]
SELECT PN,0 AS AVG_SALE_PRICE
FROM dbo.AirframeisLessThan3Quotes_SVOH
/*
Validate Customer Quote Avg Price 2nd Pass
SELECT *
FROM AirframeFinal2ndAVGCustomerQuote_SVOH
*/
/******************************************************************************************************************/
/* Step 11. Update to Master Stock now that we have avg. tables */
/******************************************************************************************************************/
/*
FINAL PASS 1
Records that will be updated as a result of Sales History for AR RP records
SELECT *
FROM MasterStockDetail
SELECT *
FROM AirframeFinal2ndAVGSalehistory_ARRP
SELECT a.*, b.avg_sale_price, 'SALES HISTORY'
FROM MasterStockDetail a, AirframeFinal2ndAVGSalehistory_ARRP b
WHERE a.pn = b.pn
AND a.condition_code IN ('AR', 'RP')
*/
UPDATE dbo.MasterStockDetail
SET avg_sale_price = ROUND(b.avg_sale_price,2)
, source = 'SALES HISTORY'
FROM dbo.MasterStockDetail a, dbo.AirframeFinal2ndAVGSalehistory_ARRP b
WHERE a.pn = b.pn
AND a.condition_code IN ('AR', 'RP')
/*
Verify same results
SELECT *
FROM AirframeFinal2ndAVGSalehistory_ARRP
SELECT *
FROM MasterStockDetail
*/
/***************************/
/*
FINAL PASS 2
Records that will be updated as a result of Customer Quotes for AR & RP records and records must be NULL
SELECT pn
FROM MasterStockDetail
SELECT *
FROM AirframeFinal2ndAVGCustomerQuote_ARRP
SELECT a.*, b.avg_sale_price, 'CUSTOMER QUOTES'
FROM MasterStockDetail a, AirframeFinal2ndAVGCustomerQuote_ARRP b
WHERE a.pn = b.pn
AND a.condition_code IN ('AR', 'RP')
AND a.avg_sale_price IS NULL
*/
UPDATE dbo.MasterStockDetail
SET avg_sale_price = ROUND(b.avg_sale_price,2)
, source = 'CUSTOMER QUOTES'
FROM dbo.MasterStockDetail a, dbo.AirframeFinal2ndAVGCustomerQuote_ARRP b
WHERE a.pn = b.pn
AND a.condition_code IN ('AR', 'RP')
AND a.avg_sale_price IS NULL
/*
Verify same results
SELECT *
FROM AirframeFinal2ndAVGCustomerQuote_ARRP
SELECT *
FROM MasterStockDetail
*/
/************************/
/*
FINAL PASS 3
Records that will be updated as a result of Sales History for SV OH records
SELECT *
FROM MasterStockDetail
SELECT *
FROM AirframeFinal2ndAVGSalehistory_SVOH
SELECT a.*, b.avg_sale_price, 'SALES HISTORY'
FROM MasterStockDetail a, AirframeFinal2ndAVGSalehistory_SVOH b
WHERE a.pn = b.pn
AND a.condition_code IN ('SV', 'OH')
*/
UPDATE dbo.MasterStockDetail
SET avg_sale_price = ROUND(b.avg_sale_price,2)
, source = 'SALES HISTORY'
FROM dbo.MasterStockDetail a, dbo.AirframeFinal2ndAVGSalehistory_SVOH b
WHERE a.pn = b.pn
AND a.condition_code IN ('SV', 'OH')
/*
Verify same results
SELECT *
FROM AirframeFinal2ndAVGSalehistory_SVOH
SELECT *
FROM MasterStockDetail
*/
/***************************/
/*
FINAL PASS 4
Records that will be updated as a result of Customer Quotes for SV & OH records and records must be NULL
SELECT pn
FROM MasterStockDetail
SELECT *
FROM AirframeFinal2ndAVGCustomerQuote_SVOH
SELECT a.*, b.avg_sale_price, 'CUSTOMER QUOTES'
FROM MasterStockDetail a, AirframeFinal2ndAVGCustomerQuote_SVOH b
WHERE a.pn = b.pn
AND a.condition_code IN ('SV', 'OH')
AND a.avg_sale_price IS NULL
*/
UPDATE dbo.MasterStockDetail
SET avg_sale_price = ROUND(b.avg_sale_price,2)
, source = 'CUSTOMER QUOTES'
FROM dbo.MasterStockDetail a, dbo.AirframeFinal2ndAVGCustomerQuote_SVOH b
WHERE a.pn = b.pn
AND a.condition_code IN ('SV', 'OH')
AND a.avg_sale_price IS NULL
/*
Verify same results
SELECT *
FROM AirframeFinal2ndAVGCustomerQuote_SVOH
SELECT *
FROM MasterStockDetail
*/

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Matija Lah SSCertifiable Points: 6429 More actions · Answer 1

Please, post a simplified version of the repro. I think by simplifying this you can even discover the source of the problem without any outside help.

ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com

Ian Yates SSCoach Points: 19738 More actions · Answer 2

Hmm, I have to agree. That's a LOT to read and decipher.

Are you sure the source data's not changing? By how much are your summaries changing?

It seems that you have a number of stages. Could you dump the contents of your tables at each stage and then compare them after different runs? This could help you determine during which stage your algorithm is going awry.