How to make this Query more efficient
Hoe do I make this more efficient. I was told I could move the date requests from the Sums below and put it in the WHERE clause and remove the Distinct from the Select. I can remove the Distinct. Not sure why it improves the performance of the query but how do I fix the Gross Sales and Cost below by removing the date range from the Sums and putting it in the WHERE.... your support is appreciated. Thank you.
SELECT DISTINCT
'Macola',
'2 - MTD Prior',
Rpt_Territory.RAMSID as IDVALUE, -- IDVALUE
'0' AS ID_TITLE, -- ID_TITLE
@CurrWeekEndDate as CurrWeekEndDate,
@CurrToDate as CurrToDate,
@CurrToDateYTD as CurrToDateYTD,
@CurrentWeek AS CurrentWeek,
@NumberofWeeks AS NumberofWeeks,
@CurrToDate_Period AS CurrToDate_Period,
@CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
ARCUSFIL_SQL.Terr as Terr,
Rpt_Territory.Terr_Name as Terr_Name,
Rpt_Territory.Report_Sort as Report_Sort,
Case when Rpt_TerritoryAdopted.RAMSID is null then 'A'
Else 'B' end as BrandCode,
Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
Rpt_TerritoryAdopted.Terr_name AS TerrAdoptedName,
Rpt_Territory.Report_Sort as Adopted_Sort,
PR_ID.ID_DESC AS BRAND,
PD.PRD_R_BRANDNAME AS BRAND_ID,
OELINHST_SQL.Item_no,
Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDate AND @PriorToDate
THEN sls_amt ELSE 0 END),
Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDate AND @PriorToDate
THEN cost_amt ELSE 0 END)
FROM OELINHST_SQL
INNER JOIN OEHDRHST_SQL
ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type
AND OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no
AND OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
INNER JOIN ARCUSFIL_SQL
ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no
INNER JOIN Rpt_Territory ON Rpt_Territory.Terr_id = ARCUSFIL_SQL.Terr
/********************/
-- Here is where I pick up IDVALUE from Ross using Macola Item number
LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL
ON OELINHST_SQL.item_no = IMITMIDX_SQL.item_no
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD
ON PD.PRD_PRODUCT = '0' + IMITMIDX_SQL.Item_no
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID
ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
AND PR_ID.ID_TITLE = N'Product Brand Name'
/********************/--
LEFT JOIN Rpt_Territory AS Rpt_TerritoryAdopted
ON OELINHST_SQL.item_no BETWEEN Rpt_TerritoryAdopted.Start_Item_no
AND Rpt_TerritoryAdopted.End_Item_no
WHERE (@Cus_no_Start = '*' or (ARCUSFIL_SQL.Cus_no >= @Cus_no_Start
and ARCUSFIL_SQL.Cus_no <= @Cus_no_End))
GROUP BY
Rpt_Territory.RAMSID ,
ARCUSFIL_SQL.Terr,
Rpt_Territory.Terr_Name,
Rpt_Territory.Report_Sort,
Rpt_TerritoryAdopted.RAMSID ,
Rpt_TerritoryAdopted.Terr_id ,
Rpt_TerritoryAdopted.Terr_name ,
Rpt_Territory.Report_Sort,
PR_ID.ID_DESC,
PD.PRD_R_BRANDNAME,
OELINHST_SQL.Item_no SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Here is the whole SP in its entirety.
The question I have now is the coworkers suggest I take all the filtering out of the Sum Sections in the Macola and RAMS sections and put them in the WHERE clauses. It is possible i asked the question incorrectly. This may make more sense now. Here is the code. The SP runs over 42+ minutes. When the restrictions were removed from the SUMS of each Select. The SP ran in just over 6 minutes. Huge improvement.
Did I change the original code to work correctly in the new SUM sections (MACOLA Sections) and How do I fix the same in RAMS. How to do I move all SUMS filterin in RAMS that to the WHERE clause so the record return more efficiently.
The question I have now is the coworkers suggest I take all the filtering out of the Sum Sections in the Macola and RAMS sections and put them in the WHERE clauses. It is possible i asked the question incorrectly. This may make more sense now. Here is the code. The SP runs over 42+ minutes. When the restrictions were removed from the SUMS of each Select. The SP ran in just over 6 minutes. Huge improvement.
Did I change the original code to work correctly in the new SUM sections (MACOLA Sections) and How do I fix the same in RAMS. How to do I move all SUMS filterin in RAMS that to the WHERE clause so the record return more efficiently.
USE [DATA_04]
GO
/****** Object: StoredProcedure [dbo].[Rpt_SalesSummaryAdopted_sproc_RB] Script Date: 10/18/2013 14:55:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Rpt_SalesSummaryAdopted_sproc_RB]
@Cus_no_Start varchar(12),
@Cus_no_End varchar(12),
@DB_Include int -- 0 = Macola Only, 1 = RAMS Only, 2 = Macola and RAMS
AS
-- for testing
-- drop table #SalesSummary
-- declare @Cus_no_Start varchar(12), @Cus_no_End varchar(12)
-- set @Cus_no_Start = '0'
-- --set @Cus_no_Start = '1505'
-- set @Cus_no_End = '1999'
-- end testing
----------------------------------------------------
-- front zero fill customer numbers
----------------------------------------------------
If @Cus_no_Start <> '*'
BEGIN
set @Cus_no_Start = right('000000000000' + @Cus_no_Start,12)
set @Cus_no_End = right('000000000000' + @Cus_no_End,12)
END
-- print @Cus_no_Start
-- print @Cus_no_End
----------------------------------------------------
-- get static variables from date tables
----------------------------------------------------
declare @CurrFromDate int, @CurrToDate int, @CurrToDateYTD int, @CurrFromDateYTD int,
@PriorFromDateYTD int, @PriorToDateYTD int, @CurrWeekBeginDate int, @CurrWeekEndDate int,
@PriorWeekBeginDate int, @PriorWeekEndDate int, @PriorFromDate int, @PriorToDate int
select
@CurrWeekEndDate = CurrWeekEndDate,
@CurrToDateYTD = CurrToDateYTD,
@CurrFromDateYTD = CurrFromDateYTD,
@CurrFromDate = CurrFromDate,
@CurrToDate = CurrToDate,
@CurrToDateYTD = CurrToDateYTD,
@CurrWeekBeginDate = CurrWeekBeginDate,
@CurrWeekEndDate = CurrWeekEndDate,
@PriorWeekBeginDate = PriorWeekBeginDate,
@PriorWeekEndDate = PriorWeekEndDate,
@PriorFromDateYTD = PriorFromDateYTD,
@PriorToDateYTD = PriorToDateYTD,
@PriorFromDate = PriorFromDate,
@PriorToDate = PriorToDate
from dbo.Rpt_ReportingDates
declare @CurrentWeek decimal(8,0), @NumberOfWeeks int, @CurrToDate_Period tinyint, @CurrToDateYTD_FiscalYear smallint
select
@CurrentWeek = CurrentWeek,
@NumberOfWeeks = NumberOfWeeks,
@CurrToDate_Period = CurrToDate_Period,
@CurrToDateYTD_FiscalYear = CurrToDateYTD_FiscalYear
from dbo.vwFiscalDates
------------------------------------------------------------
-- create temp table for Sales Summary
------------------------------------------------------------
Create table #SalesSummary
(
[data_source] varchar(12),-- what database is the data coming from macola or rams?
PerType varchar(18) NULL,
[IDVALUE] Varchar (6) Null,
[ID_TITLE]Varchar (40) Null,
[CurrWeekEndDate] [int] NULL ,
[CurrToDate] [int] NULL ,
[CurrToDateYTD] [int] NULL ,
[CurrentWeek] [decimal](8, 0) NULL ,
[NumberOfWeeks] [int] NULL ,
[CurrToDate_Period] [tinyint] NULL ,
[CurrToDateYTD_FiscalYear] [smallint] NULL ,
[Terr] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Terr_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Report_Sort] [int] NULL ,
[BrandCode] Varchar (1) null,
[TerrAdopted] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TerrAdoptedName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Adopted_Sort] [int] NULL ,
[BRAND] Varchar (100) Null,
[BRAND_ID] Varchar (6) Null,
Item_no varchar(15),
Gross_Sales decimal(38,2) NULL,
Cost decimal(38,2) NULL
)
----------------------------------------------------
-- insert for MTD Current and Prior MACOLA
----------------------------------------------------
if (@DB_Include = 0 or @DB_Include = 2)
BEGIN
INSERT INTO #SalesSummary
SELECT
'Macola',
'2 - MTD Current',
Rpt_Territory.RAMSID as IDVALUE, -- IDVALUE
'0' AS ID_TITLE, -- ID_TITLE
@CurrWeekEndDate as CurrWeekEndDate,
@CurrToDate as CurrToDate,
@CurrToDateYTD as CurrToDateYTD,
@CurrentWeek AS CurrentWeek,
@NumberofWeeks AS NumberofWeeks,
@CurrToDate_Period AS CurrToDate_Period,
@CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
ARCUSFIL_SQL.Terr as Terr,
Rpt_Territory.Terr_Name as Terr_Name,
Rpt_Territory.Report_Sort as Report_Sort,
Case when Rpt_TerritoryAdopted.RAMSID is null then 'A'
Else 'B' end as BrandCode,
Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
Rpt_TerritoryAdopted.Terr_name AS TerrAdoptedName,
Rpt_Territory.Report_Sort as Adopted_Sort,
PR_ID.ID_DESC AS BRAND,
PD.PRD_R_BRANDNAME AS BRAND_ID,
OELINHST_SQL.Item_no,
--Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDate AND @CurrToDate
--THEN sls_amt ELSE 0 END),
Gross_Sales = SUM(sls_amt),
--Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDate AND @CurrToDate
--THEN cost_amt ELSE 0 END)
Cost = SUM(cost_amt)
FROM OELINHST_SQL
INNER JOIN OEHDRHST_SQL
ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type
AND OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no
AND OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
INNER JOIN ARCUSFIL_SQL
ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no
INNER JOIN Rpt_Territory ON Rpt_Territory.Terr_id = ARCUSFIL_SQL.Terr
/********************/
-- Here is where I pick up IDVALUE from Ross using Macola Item number
LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL
ON OELINHST_SQL.item_no = IMITMIDX_SQL.item_no
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD
ON PD.PRD_PRODUCT = '0' + IMITMIDX_SQL.Item_no
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID
ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
AND PR_ID.ID_TITLE = N'Product Brand Name'
/********************/--
LEFT JOIN Rpt_Territory AS Rpt_TerritoryAdopted
ON OELINHST_SQL.item_no BETWEEN Rpt_TerritoryAdopted.Start_Item_no
AND Rpt_TerritoryAdopted.End_Item_no
WHERE (@Cus_no_Start = '*' or ARCUSFIL_SQL.Cus_no between @Cus_no_Start and @Cus_no_End)
AND OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDate AND @CurrToDate
GROUP BY
Rpt_Territory.RAMSID ,
ARCUSFIL_SQL.Terr,
Rpt_Territory.Terr_Name,
Rpt_Territory.Report_Sort,
Rpt_TerritoryAdopted.RAMSID ,
Rpt_TerritoryAdopted.Terr_id ,
Rpt_TerritoryAdopted.Terr_name ,
Rpt_Territory.Report_Sort,
PR_ID.ID_DESC,
PD.PRD_R_BRANDNAME,
OELINHST_SQL.Item_no
------------------------------------------------------
---- insert for MTD Current and Prior MACOLA
------------------------------------------------------
INSERT INTO #SalesSummary
SELECT
'Macola',
'2 - MTD Prior',
Rpt_Territory.RAMSID as IDVALUE, -- IDVALUE
'0' AS ID_TITLE, -- ID_TITLE
@CurrWeekEndDate as CurrWeekEndDate,
@CurrToDate as CurrToDate,
@CurrToDateYTD as CurrToDateYTD,
@CurrentWeek AS CurrentWeek,
@NumberofWeeks AS NumberofWeeks,
@CurrToDate_Period AS CurrToDate_Period,
@CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
ARCUSFIL_SQL.Terr as Terr,
Rpt_Territory.Terr_Name as Terr_Name,
Rpt_Territory.Report_Sort as Report_Sort,
Case when Rpt_TerritoryAdopted.RAMSID is null then 'A'
Else 'B' end as BrandCode,
Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
Rpt_TerritoryAdopted.Terr_name AS TerrAdoptedName,
Rpt_Territory.Report_Sort as Adopted_Sort,
PR_ID.ID_DESC AS BRAND,
PD.PRD_R_BRANDNAME AS BRAND_ID,
OELINHST_SQL.Item_no,
--Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDate AND @CurrToDate
--THEN sls_amt ELSE 0 END),
Gross_Sales = SUM(sls_amt),
--Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDate AND @CurrToDate
--THEN cost_amt ELSE 0 END)
Cost = SUM(cost_amt)
FROM OELINHST_SQL
INNER JOIN OEHDRHST_SQL
ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type
AND OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no
AND OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
INNER JOIN ARCUSFIL_SQL
ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no
INNER JOIN Rpt_Territory ON Rpt_Territory.Terr_id = ARCUSFIL_SQL.Terr
/********************/
-- Here is where I pick up IDVALUE from Ross using Macola Item number
LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL
ON OELINHST_SQL.item_no = IMITMIDX_SQL.item_no
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD
ON PD.PRD_PRODUCT = '0' + IMITMIDX_SQL.Item_no
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID
ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
AND PR_ID.ID_TITLE = N'Product Brand Name'
/********************/--
LEFT JOIN Rpt_Territory AS Rpt_TerritoryAdopted
ON OELINHST_SQL.item_no BETWEEN Rpt_TerritoryAdopted.Start_Item_no
AND Rpt_TerritoryAdopted.End_Item_no
WHERE (@Cus_no_Start = '*' or ARCUSFIL_SQL.Cus_no between @Cus_no_Start and @Cus_no_End)
GROUP BY
Rpt_Territory.RAMSID ,
ARCUSFIL_SQL.Terr,
Rpt_Territory.Terr_Name,
Rpt_Territory.Report_Sort,
Rpt_TerritoryAdopted.RAMSID ,
Rpt_TerritoryAdopted.Terr_id ,
Rpt_TerritoryAdopted.Terr_name ,
Rpt_Territory.Report_Sort,
PR_ID.ID_DESC,
PD.PRD_R_BRANDNAME,
OELINHST_SQL.Item_no
------------------------------------------------------
---- insert for YTD Current MACOLA
------------------------------------------------------
INSERT INTO #SalesSummary
SELECT
'Macola',
'3 - YTD Current',
Rpt_Territory.RAMSID as IDVALUE, -- IDVALUE
'0' AS ID_TITLE, -- ID_TITLE
@CurrWeekEndDate as CurrWeekEndDate,
@CurrToDate as CurrToDate,
@CurrToDateYTD as CurrToDateYTD,
@CurrentWeek AS CurrentWeek,
@NumberofWeeks AS NumberofWeeks,
@CurrToDate_Period AS CurrToDate_Period,
@CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
ARCUSFIL_SQL.Terr as Terr,
Rpt_Territory.Terr_Name as Terr_Name,
Rpt_Territory.Report_Sort as Report_Sort,
Case when Rpt_TerritoryAdopted.RAMSID is null then 'A'
Else 'B' end as BrandCode,
Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
Rpt_TerritoryAdopted.Terr_name AS TerrAdoptedName,
Rpt_Territory.Report_Sort as Adopted_Sort,
PR_ID.ID_DESC AS BRAND,
PD.PRD_R_BRANDNAME AS BRAND_ID,
OELINHST_SQL.Item_no,
--Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDate AND @CurrToDate
--THEN sls_amt ELSE 0 END),
Gross_Sales = SUM(sls_amt),
--Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDate AND @CurrToDate
--THEN cost_amt ELSE 0 END)
Cost = SUM(cost_amt)
FROM OELINHST_SQL
INNER JOIN OEHDRHST_SQL
ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type
AND OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no
AND OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
INNER JOIN ARCUSFIL_SQL
ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no
INNER JOIN Rpt_Territory ON Rpt_Territory.Terr_id = ARCUSFIL_SQL.Terr
/********************/
-- Here is where I pick up IDVALUE from Ross using Macola Item number
LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL
ON OELINHST_SQL.item_no = IMITMIDX_SQL.item_no
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD
ON PD.PRD_PRODUCT = '0' + IMITMIDX_SQL.Item_no
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID
ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
AND PR_ID.ID_TITLE = N'Product Brand Name'
/********************/--
LEFT JOIN Rpt_Territory AS Rpt_TerritoryAdopted
ON OELINHST_SQL.item_no BETWEEN Rpt_TerritoryAdopted.Start_Item_no
AND Rpt_TerritoryAdopted.End_Item_no
WHERE (@Cus_no_Start = '*' or ARCUSFIL_SQL.Cus_no between @Cus_no_Start and @Cus_no_End)
GROUP BY
Rpt_Territory.RAMSID ,
ARCUSFIL_SQL.Terr,
Rpt_Territory.Terr_Name,
Rpt_Territory.Report_Sort,
Rpt_TerritoryAdopted.RAMSID ,
Rpt_TerritoryAdopted.Terr_id ,
Rpt_TerritoryAdopted.Terr_name ,
Rpt_Territory.Report_Sort,
PR_ID.ID_DESC,
PD.PRD_R_BRANDNAME,
OELINHST_SQL.Item_no
------------------------------------------------------
---- insert for YTD Prior MACOLA
------------------------------------------------------
INSERT INTO #SalesSummary
SELECT
'Macola',
'3 - YTD Prior',
Rpt_Territory.RAMSID as IDVALUE, -- IDVALUE
'0' AS ID_TITLE, -- ID_TITLE
@CurrWeekEndDate as CurrWeekEndDate,
@CurrToDate as CurrToDate,
@CurrToDateYTD as CurrToDateYTD,
@CurrentWeek AS CurrentWeek,
@NumberofWeeks AS NumberofWeeks,
@CurrToDate_Period AS CurrToDate_Period,
@CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
ARCUSFIL_SQL.Terr as Terr,
Rpt_Territory.Terr_Name as Terr_Name,
Rpt_Territory.Report_Sort as Report_Sort,
Case when Rpt_TerritoryAdopted.RAMSID is null then 'A'
Else 'B' end as BrandCode,
Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
Rpt_TerritoryAdopted.Terr_name AS TerrAdoptedName,
Rpt_Territory.Report_Sort as Adopted_Sort,
PR_ID.ID_DESC AS BRAND,
PD.PRD_R_BRANDNAME AS BRAND_ID,
OELINHST_SQL.Item_no,
--Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDate AND @CurrToDate
--THEN sls_amt ELSE 0 END),
Gross_Sales = SUM(sls_amt),
--Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDate AND @CurrToDate
--THEN cost_amt ELSE 0 END)
Cost = SUM(cost_amt)
FROM OELINHST_SQL
INNER JOIN OEHDRHST_SQL
ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type
AND OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no
AND OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
INNER JOIN ARCUSFIL_SQL
ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no
INNER JOIN Rpt_Territory ON Rpt_Territory.Terr_id = ARCUSFIL_SQL.Terr
/********************/
-- Here is where I pick up IDVALUE from Ross using Macola Item number
LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL
ON OELINHST_SQL.item_no = IMITMIDX_SQL.item_no
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD
ON PD.PRD_PRODUCT = '0' + IMITMIDX_SQL.Item_no
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID
ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
AND PR_ID.ID_TITLE = N'Product Brand Name'
/********************/--
LEFT JOIN Rpt_Territory AS Rpt_TerritoryAdopted
ON OELINHST_SQL.item_no BETWEEN Rpt_TerritoryAdopted.Start_Item_no
AND Rpt_TerritoryAdopted.End_Item_no
WHERE (@Cus_no_Start = '*' or ARCUSFIL_SQL.Cus_no between @Cus_no_Start and @Cus_no_End)
GROUP BY
Rpt_Territory.RAMSID ,
ARCUSFIL_SQL.Terr,
Rpt_Territory.Terr_Name,
Rpt_Territory.Report_Sort,
Rpt_TerritoryAdopted.RAMSID ,
Rpt_TerritoryAdopted.Terr_id ,
Rpt_TerritoryAdopted.Terr_name ,
Rpt_Territory.Report_Sort,
PR_ID.ID_DESC,
PD.PRD_R_BRANDNAME,
OELINHST_SQL.Item_no
END
--------------------------------------------------------------------------------------------------
-- Here is where the Weekly, Monthly, Yearly RAMS data gets added to the #SalesSummary work table
--------------------------------------------------------------------------------------------------
----------------------------------------------------
-- insert for MTD Current RAMS
----------------------------------------------------
if (@DB_Include = 1 or @DB_Include = 2)
BEGIN
INSERT INTO #SalesSummary
SELECT DISTINCT
'RAMS',
'2 - MTD Current',
-- SubString(CUST_ID.ID_DESC,3,2) as CM_SELLZONE,
CUST_ID.IDVALUE,
CUST_ID.ID_TITLE,
@CurrWeekEndDate as CurrWeekEndDate,
@CurrToDate as CurrToDate,
@CurrToDateYTD as CurrToDateYTD,
@CurrentWeek AS CurrentWeek,
@NumberofWeeks AS NumberofWeeks,
@CurrToDate_Period AS CurrToDate_Period,
@CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
SubString(CUST_ID.ID_DESC,3,2) as Terr,
CUST_ID.ID_DESC as Terr_name,
Rpt_Territory.Report_Sort as Report_Sort,
left(PR_ID.ID_DESC,1) as BrandCode,
Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
Rpt_TerritoryAdopted.Terr_name AS TerrAdoptedName,
Rpt_TerritoryAdopted.Report_Sort as Adopted_Sort,
PR_ID.ID_DESC AS BRAND,
PD.PRD_R_BRANDNAME AS BRAND_ID,
OD.OD_PRODUCT as Item_no,
Gross_Sales = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003')
AND CM.CM_CUST_SUB_TYPE <> 3 -- SBT = 3 (Pay By Scan)
AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') -- Sale or BuyBack
AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrFromDate AND @CurrToDate)
THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
ELSE 0 END),
Cost = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003')
AND CM.CM_CUST_SUB_TYPE <> 3 AND OD.OD_RETURN_REASON_CODE IN ('000000','000001')
AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrFromDate AND @CurrToDate)
THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
ELSE 0 END)
FROM CHLROS05.RAMSDB.dbo.ROSS_SALESHDR AS OH
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD
ON OD.OD_TICKET_NUMBER = OH_TICKET_NUMBER
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM
ON OH.OH_CUSTOMER_NUMBER = CM.CM_CUSTKEY
AND CM_ACCT_GLPOSTS = '000004'
AND left(CM.CM_SELLZONE,1) = '4'
AND CM.CM_CUST_SUB_TYPE <> 3
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as CUST_ID
ON CM.CM_SELLZONE = CUST_ID.IDVALUE
AND CUST_ID.ID_TITLE = N'Sale Zone'
left outer join dbo.Rpt_Territory on
Rpt_Territory.RAMSID = CUST_ID.IDVALUE
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD
ON PD.PRD_PRODUCT = OD.OD_PRODUCT
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID
ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
AND PR_ID.ID_TITLE = N'Product Brand Name'
LEFT OUTER JOIN dbo.Rpt_Territory AS Rpt_TerritoryAdopted
ON Right(OD.OD_PRODUCT,7) BETWEEN Rpt_TerritoryAdopted.Start_Item_no
AND Rpt_TerritoryAdopted.End_Item_no
--WHERE OH.OH_SALE_TYPE = 'O'
-- AND (ID.ID_TITLE = N'Sale Zone')
GROUP BY
CUST_ID.IDVALUE,
CUST_ID.ID_TITLE,
SubString(CUST_ID.ID_DESC,3,2) ,
CUST_ID.ID_DESC ,
Rpt_Territory.Report_Sort,
left(PR_ID.ID_DESC,1),
Rpt_TerritoryAdopted.Terr_id,
Rpt_TerritoryAdopted.Terr_name,
Rpt_TerritoryAdopted.Report_Sort,
PR_ID.ID_DESC,
PD.PRD_R_BRANDNAME ,
OD.OD_PRODUCT
----------------------------------------------------
-- insert for MTD Prior RAMS
----------------------------------------------------
INSERT INTO #SalesSummary
SELECT DISTINCT
'RAMS',
'2 - MTD Prior',
-- SubString(CUST_ID.ID_DESC,3,2) as CM_SELLZONE,
CUST_ID.IDVALUE,
CUST_ID.ID_TITLE,
@CurrWeekEndDate as CurrWeekEndDate,
@CurrToDate as CurrToDate,
@CurrToDateYTD as CurrToDateYTD,
@CurrentWeek AS CurrentWeek,
@NumberofWeeks AS NumberofWeeks,
@CurrToDate_Period AS CurrToDate_Period,
@CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
SubString(CUST_ID.ID_DESC,3,2) as Terr,
CUST_ID.ID_DESC as Terr_name,
Rpt_Territory.Report_Sort as Report_Sort,
left(PR_ID.ID_DESC,1) as BrandCode,
Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
Rpt_TerritoryAdopted.Terr_name AS TerrAdoptedName,
Rpt_TerritoryAdopted.Report_Sort as Adopted_Sort,
PR_ID.ID_DESC AS BRAND,
PD.PRD_R_BRANDNAME AS BRAND_ID,
OD.OD_PRODUCT as Item_no,
Gross_Sales = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003')
AND CM.CM_CUST_SUB_TYPE <> 3 -- SBT = 3 (Pay By Scan)
AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') -- Sale or BuyBack
AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @PriorFromDate AND @PriorToDate)
THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
ELSE 0 END),
Cost = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003')
AND CM.CM_CUST_SUB_TYPE <> 3 AND OD.OD_RETURN_REASON_CODE IN ('000000','000001')
AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @PriorFromDate AND @PriorToDate)
THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
ELSE 0 END)
FROM CHLROS05.RAMSDB.dbo.ROSS_SALESHDR AS OH
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD
ON OD.OD_TICKET_NUMBER = OH_TICKET_NUMBER
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM
ON OH.OH_CUSTOMER_NUMBER = CM.CM_CUSTKEY
AND CM_ACCT_GLPOSTS = '000004'
AND left(CM.CM_SELLZONE,1) = '4'
AND CM.CM_CUST_SUB_TYPE <> 3
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as CUST_ID
ON CM.CM_SELLZONE = CUST_ID.IDVALUE
AND CUST_ID.ID_TITLE = N'Sale Zone'
left outer join dbo.Rpt_Territory on
Rpt_Territory.RAMSID = CUST_ID.IDVALUE
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD
ON PD.PRD_PRODUCT = OD.OD_PRODUCT
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID
ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
AND PR_ID.ID_TITLE = N'Product Brand Name'
LEFT OUTER JOIN dbo.Rpt_Territory AS Rpt_TerritoryAdopted
ON Right(OD.OD_PRODUCT,7) BETWEEN Rpt_TerritoryAdopted.Start_Item_no
AND Rpt_TerritoryAdopted.End_Item_no
--WHERE OH.OH_SALE_TYPE = 'O'
-- AND (ID.ID_TITLE = N'Sale Zone')
GROUP BY
CUST_ID.IDVALUE,
CUST_ID.ID_TITLE,
SubString(CUST_ID.ID_DESC,3,2) ,
CUST_ID.ID_DESC ,
Rpt_Territory.Report_Sort,
left(PR_ID.ID_DESC,1),
Rpt_TerritoryAdopted.Terr_id,
Rpt_TerritoryAdopted.Terr_name,
Rpt_TerritoryAdopted.Report_Sort,
PR_ID.ID_DESC,
PD.PRD_R_BRANDNAME ,
OD.OD_PRODUCT
----------------------------------------------------
-- insert for YTD Current RAMS
----------------------------------------------------
INSERT INTO #SalesSummary
SELECT DISTINCT
'RAMS',
'3 - YTD Current',
-- SubString(CUST_ID.ID_DESC,3,2) as CM_SELLZONE,
CUST_ID.IDVALUE,
CUST_ID.ID_TITLE,
@CurrWeekEndDate as CurrWeekEndDate,
@CurrToDate as CurrToDate,
@CurrToDateYTD as CurrToDateYTD,
@CurrentWeek AS CurrentWeek,
@NumberofWeeks AS NumberofWeeks,
@CurrToDate_Period AS CurrToDate_Period,
@CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
SubString(CUST_ID.ID_DESC,3,2) as Terr,
CUST_ID.ID_DESC as Terr_name,
Rpt_Territory.Report_Sort as Report_Sort,
left(PR_ID.ID_DESC,1) as BrandCode,
Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
Rpt_TerritoryAdopted.Terr_name AS TerrAdoptedName,
Rpt_TerritoryAdopted.Report_Sort as Adopted_Sort,
PR_ID.ID_DESC AS BRAND,
PD.PRD_R_BRANDNAME AS BRAND_ID,
OD.OD_PRODUCT as Item_no,
Gross_Sales = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003')
AND CM.CM_CUST_SUB_TYPE <> 3 -- SBT = 3 (Pay By Scan)
AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') -- Sale or BuyBack
AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrFromDateYTD AND @CurrToDateYTD)
THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
ELSE 0 END),
Cost = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003')
AND CM.CM_CUST_SUB_TYPE <> 3 AND OD.OD_RETURN_REASON_CODE IN ('000000','000001')
AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrFromDateYTD AND @CurrToDateYTD)
THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
ELSE 0 END)
FROM CHLROS05.RAMSDB.dbo.ROSS_SALESHDR AS OH
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD
ON OD.OD_TICKET_NUMBER = OH_TICKET_NUMBER
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM
ON OH.OH_CUSTOMER_NUMBER = CM.CM_CUSTKEY
AND CM_ACCT_GLPOSTS = '000004'
AND left(CM.CM_SELLZONE,1) = '4'
AND CM.CM_CUST_SUB_TYPE <> 3
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as CUST_ID
ON CM.CM_SELLZONE = CUST_ID.IDVALUE
AND CUST_ID.ID_TITLE = N'Sale Zone'
left outer join dbo.Rpt_Territory on
Rpt_Territory.RAMSID = CUST_ID.IDVALUE
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD
ON PD.PRD_PRODUCT = OD.OD_PRODUCT
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID
ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
AND PR_ID.ID_TITLE = N'Product Brand Name'
LEFT OUTER JOIN dbo.Rpt_Territory AS Rpt_TerritoryAdopted
ON Right(OD.OD_PRODUCT,7) BETWEEN Rpt_TerritoryAdopted.Start_Item_no
AND Rpt_TerritoryAdopted.End_Item_no
--WHERE OH.OH_SALE_TYPE = 'O'
-- AND (ID.ID_TITLE = N'Sale Zone')
GROUP BY
CUST_ID.IDVALUE,
CUST_ID.ID_TITLE,
SubString(CUST_ID.ID_DESC,3,2) ,
CUST_ID.ID_DESC ,
Rpt_Territory.Report_Sort,
left(PR_ID.ID_DESC,1),
Rpt_TerritoryAdopted.Terr_id,
Rpt_TerritoryAdopted.Terr_name,
Rpt_TerritoryAdopted.Report_Sort,
PR_ID.ID_DESC,
PD.PRD_R_BRANDNAME ,
OD.OD_PRODUCT
----------------------------------------------------
-- insert for YTD Prior RAMS
----------------------------------------------------
INSERT INTO #SalesSummary
SELECT DISTINCT
'RAMS',
'3 - YTD Prior',
-- SubString(CUST_ID.ID_DESC,3,2) as CM_SELLZONE,
CUST_ID.IDVALUE,
CUST_ID.ID_TITLE,
@CurrWeekEndDate as CurrWeekEndDate,
@CurrToDate as CurrToDate,
@CurrToDateYTD as CurrToDateYTD,
@CurrentWeek AS CurrentWeek,
@NumberofWeeks AS NumberofWeeks,
@CurrToDate_Period AS CurrToDate_Period,
@CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
SubString(CUST_ID.ID_DESC,3,2) as Terr,
CUST_ID.ID_DESC as Terr_name,
Rpt_Territory.Report_Sort as Report_Sort,
left(PR_ID.ID_DESC,1) as BrandCode,
Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
Rpt_TerritoryAdopted.Terr_name AS TerrAdoptedName,
Rpt_TerritoryAdopted.Report_Sort as Adopted_Sort,
PR_ID.ID_DESC AS BRAND,
PD.PRD_R_BRANDNAME AS BRAND_ID,
OD.OD_PRODUCT as Item_no,
Gross_Sales = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003')
AND CM.CM_CUST_SUB_TYPE <> 3 -- SBT = 3 (Pay By Scan)
AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') -- Sale or BuyBack
AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @PriorFromDateYTD AND @PriorToDateYTD)
THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
ELSE 0 END),
Cost = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003')
AND CM.CM_CUST_SUB_TYPE <> 3 AND OD.OD_RETURN_REASON_CODE IN ('000000','000001')
AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @PriorFromDateYTD AND @PriorToDateYTD)
THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
ELSE 0 END)
FROM CHLROS05.RAMSDB.dbo.ROSS_SALESHDR AS OH
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD
ON OD.OD_TICKET_NUMBER = OH_TICKET_NUMBER
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM
ON OH.OH_CUSTOMER_NUMBER = CM.CM_CUSTKEY
AND CM_ACCT_GLPOSTS = '000004'
AND left(CM.CM_SELLZONE,1) = '4'
AND CM.CM_CUST_SUB_TYPE <> 3
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as CUST_ID
ON CM.CM_SELLZONE = CUST_ID.IDVALUE
AND CUST_ID.ID_TITLE = N'Sale Zone'
left outer join dbo.Rpt_Territory on
Rpt_Territory.RAMSID = CUST_ID.IDVALUE
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD
ON PD.PRD_PRODUCT = OD.OD_PRODUCT
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID
ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
AND PR_ID.ID_TITLE = N'Product Brand Name'
LEFT OUTER JOIN dbo.Rpt_Territory AS Rpt_TerritoryAdopted
ON Right(OD.OD_PRODUCT,7) BETWEEN Rpt_TerritoryAdopted.Start_Item_no
AND Rpt_TerritoryAdopted.End_Item_no
--WHERE OH.OH_SALE_TYPE = 'O'
-- AND (ID.ID_TITLE = N'Sale Zone')
GROUP BY
CUST_ID.IDVALUE,
CUST_ID.ID_TITLE,
SubString(CUST_ID.ID_DESC,3,2) ,
CUST_ID.ID_DESC ,
Rpt_Territory.Report_Sort,
left(PR_ID.ID_DESC,1),
Rpt_TerritoryAdopted.Terr_id,
Rpt_TerritoryAdopted.Terr_name,
Rpt_TerritoryAdopted.Report_Sort,
PR_ID.ID_DESC,
PD.PRD_R_BRANDNAME ,
OD.OD_PRODUCT
END -- DB_Include = 1 or 2
----------------------------------------------------
-- final select
----------------------------------------------------
select * from #SalesSummarySOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
this is perilously close to a duplicate question I think.
certainly related to https://www.experts-exchange.com/questions/28272936/How-do-I-convert-this-into-a-WHERE-Clause-so-that-the-query-is-more-efficient.html
and the as yet unfinished
https://www.experts-exchange.com/questions/28264933/Aggregate-functions-and-Group-by-on-Varchar-values.html
ruavol2, it does not help us or yourself if the same topic gets "fractured" into several different questions.
At the outset, in Q_28264933, you asked if the stored proc followed best practice - it does not - and a suggested rethinking of how you do it was proposed - with no follow-up from you.
Subsequently you have asked about performance, in Q_28271096, and the first response to that was to revise your where clauses - and whilst you did do that you took the advice from a colleague instead.
Here you are concentrating of the where clause again, this isn't the correct focus.
Your fundamental problem is that you are conducting multiple select queries when you could be doing one. And add to that for each of the multiple queries you are also doing it via "select distinct".
If you are willing to listen to this advice I'm certain we can assist - but stick to a single question please.
certainly related to https://www.experts-exchange.com/questions/28272936/How-do-I-convert-this-into-a-WHERE-Clause-so-that-the-query-is-more-efficient.html
and the as yet unfinished
https://www.experts-exchange.com/questions/28264933/Aggregate-functions-and-Group-by-on-Varchar-values.html
ruavol2, it does not help us or yourself if the same topic gets "fractured" into several different questions.
At the outset, in Q_28264933, you asked if the stored proc followed best practice - it does not - and a suggested rethinking of how you do it was proposed - with no follow-up from you.
Subsequently you have asked about performance, in Q_28271096, and the first response to that was to revise your where clauses - and whilst you did do that you took the advice from a colleague instead.
Here you are concentrating of the where clause again, this isn't the correct focus.
Your fundamental problem is that you are conducting multiple select queries when you could be doing one. And add to that for each of the multiple queries you are also doing it via "select distinct".
If you are willing to listen to this advice I'm certain we can assist - but stick to a single question please.
ASKER
I may have fractured the question because I have thought on many occasions that either it should be fractured or others thought it should. I am not aware of the policy of such things. I do have a good friend in mlmcc and he knows I would not mess with you guys I just did a poor job of crafting the question and understand little of the fundamentals of the code. I am working with a few text books and am thankful for you.
PortletPaul
I did not see where you showed "a way to collapse all of the RAMS section into a single query". I did see the response abotu grouping but no more.
How can this be done with the queries being set to individual snapshots of date ranges in the database.....?
PortletPaul
I did not see where you showed "a way to collapse all of the RAMS section into a single query". I did see the response abotu grouping but no more.
How can this be done with the queries being set to individual snapshots of date ranges in the database.....?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Here is what I did to fix the inefficiency. PortletPaul ...... that represented a lot of work THANK YOU FOR THE EFFORT.....! It was appreciated.
I shortened the code in the Sum area and expanded the WHERE clause a bit
In all the Selects (4) for RAMS and (4) for MACOLA
Each section would have a slight change to the Date Ranges as some are Current Month or Current YTD or Prior Month and Prior YTD. This brought the code in line at 2 min 13 seconds of processing time. However now I have another problem hence my concerns about bringing it up here.
Porter Paul....I am greatful for all your support. I am not stupid and you did not imply that........just poor at conveying my thoughts at times when I barely understand the policy of this code or this forum sometimes. Plus unfortunately my mind or brain does not work like yours. I wish it did I would be alot better off. Trying to make sense of the best way to word a question and the most efficient. I am pretty sure I took yours and others advice thank you.
I shortened the code in the Sum area and expanded the WHERE clause a bit
In all the Selects (4) for RAMS and (4) for MACOLA
-- Macola Sections
Gross_Sales = SUM(sls_amt),
Cost = SUM(cost_amt)
"Code is shortened for viewing convenience"
"FROM clause stayed the same...."
WHERE
(@Cus_no_Start = '*' or ARCUSFIL_SQL.Cus_no between @Cus_no_Start and @Cus_no_End)
AND OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDate AND @CurrToDate
-- RAMS Sections
Gross_Sales = SUM(CASE WHEN OH.OH_SALE_TYPE = 'O'
AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003')
AND CM.CM_CUST_SUB_TYPE <> 3 -- SBT = 3 (Pay By Scan)
AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') -- Sale or BuyBack
THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
ELSE 0 END),
Cost = SUM(CASE WHEN OH.OH_SALE_TYPE = 'O'
AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003')
AND CM.CM_CUST_SUB_TYPE <> 3
AND OD.OD_RETURN_REASON_CODE IN ('000000','000001')
THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
ELSE 0 END)
"FROM.....stayed the same...."
WHERE CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrFromDate AND @CurrToDateEach section would have a slight change to the Date Ranges as some are Current Month or Current YTD or Prior Month and Prior YTD. This brought the code in line at 2 min 13 seconds of processing time. However now I have another problem hence my concerns about bringing it up here.
Porter Paul....I am greatful for all your support. I am not stupid and you did not imply that........just poor at conveying my thoughts at times when I barely understand the policy of this code or this forum sometimes. Plus unfortunately my mind or brain does not work like yours. I wish it did I would be alot better off. Trying to make sense of the best way to word a question and the most efficient. I am pretty sure I took yours and others advice thank you.
ruavol2
Let's say you have been asked to deliver some goods to a client. 4 large boxes.
The client is just down the street.
You can walk down to them one box at a time and in 4 trips the job gets done.
Or you could find a trolley, get the 4 boxes on it, and do it in one trip.
The choice is yours, but I would choose one trip every time.
If you would like to pursue this further this question remains open: Q_28264933
Good luck with your project and please ask away, we are here to help.
Regards,
Paul
Let's say you have been asked to deliver some goods to a client. 4 large boxes.
The client is just down the street.
You can walk down to them one box at a time and in 4 trips the job gets done.
Or you could find a trolley, get the 4 boxes on it, and do it in one trip.
The choice is yours, but I would choose one trip every time.
If you would like to pursue this further this question remains open: Q_28264933
Good luck with your project and please ask away, we are here to help.
Regards,
Paul
ASKER
I took the date ranges in the totals and put them in a WHERE clause at the end of each select and took the range out of the sums. Ran the stored procedure again and it went from 43+ minutes to 6 minutes and 19 seconds. I had also removed the DISTINCT from each select and it shortened the time by about 40 seconds with the same number of records being pulled.
what is the best way to do this and leave the formula for the sales intact and build a where clause they swear will shorten the run time....?