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  

Open in new window

RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
In my opinion you can't move it however I don't think that's a performance issue if the data type from column matches the variables and there is an index on it.

I would use Query execution plan - under Query SSMS menu option use Include Actual Execution plan or at least Estimated plan - to see if there are any missing indexes and where the cost is high then decide what and how to change without altering business logic.

Also you could change the WHERE to

@Cus_no_Start = '*' or ARCUSFIL_SQL.Cus_no between @Cus_no_Start and @Cus_no_End

and same thing like above with data type and index
0
PortletPaulfreelancerCommented:
DISTINCT
there is absolutely zero value in using "select distinct" in a query that has already had it's rows made unique through a  group by clause.

I think your group by needs that case expression on Rpt_TerritoryAdopted.RAMSID i.e.
GROUP BY
         CASE
                WHEN Rpt_TerritoryAdopted.RAMSID IS NULL THEN 'A'
                ELSE 'B'
                END

Otherwise just by looking at the query I can't make any real performance observations and agree with lcohan, your next logical step is to look at the execution plan.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
One of my coworkers said the following..........

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....?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
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.

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 #SalesSummary

Open in new window

0
PortletPaulfreelancerCommented:
by: PortletPaulPosted on 2013-10-12 at 04:58:58 ID: 39568020

>>Can anyone forsee any problems with doing it the way I did.
Yes, performance.
On that question I suggested a way to collapse all of the RAMS section into a single query.

Are you willing to to pursue this?
0
PortletPaulfreelancerCommented:
this is perilously close to a duplicate question I think.
certainly related to http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28272936.html

and the as yet unfinished
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28264933.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.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
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.....?
0
PortletPaulfreelancerCommented:
sorry, did not mean to imply fault on your behalf - those notes also help other experts know that a question is being addressed in various ways.

The core issue to alleviate the performance is to reduce the number of selects you are performing - so while improving the where clause is absolutely useful - it is not as effective as cutting your sp from ~8 big selects to 2 more efficient ones.

>>How can this be done with the queries being set to individual snapshots of date ranges in the database.....?
I demonstrated that too in the same query; by using case expressions.
0
PortletPaulfreelancerCommented:
I have just realized that your "date" parameters are in fact int values
But you use these to compare to date/datetime values

every time you use these int values against a different data type an implicit conversion is taking place - this consumes resources.

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

Open in new window

Could you please do the following:
a. provide a sample of the data held dbo.Rpt_ReportingDates
(I really need to 'see' what you are using for the dates)

b. run through your existing code and list all fields that are being compared to the variables @CurrFromDate @PriorFromDate  @CurrFromDateYTD @PriorFromDateYTD (or the matching 'ToDate' values)

c. what are the actual data types of those fields? (e.g. datetime, smalldatetime etc)
0
PortletPaulfreelancerCommented:
please look at this, and tell me if this query - by itself - works
        /* previously these variables were declared as int
           and assuming dbo.Rpt_ReportingDates fields are int
           and assuming the values held look like this: 20131231 (i.e. YYYYMMDD as int)
           and assuming these variables will be compared to date/datetimes later
           then, convert these variables to datetime
           to minimize implicit conversions
           and to simplify all subsequent date/time operations
           */

        DECLARE @CurrFromDate       AS datetime
              , @CurrToDate         AS datetime
              , @CurrToDateYTD      AS datetime
              , @CurrFromDateYTD    AS datetime
              , @PriorFromDateYTD   AS datetime
              , @PriorToDateYTD     AS datetime
              , @CurrWeekBeginDate  AS datetime
              , @CurrWeekEndDate    AS datetime
              , @PriorWeekBeginDate AS datetime
              , @PriorWeekEndDate   AS datetime
              , @PriorFromDate      AS datetime
              , @PriorToDate        AS datetime
        SELECT
                @CurrWeekEndDate    = convert(datetime,convert(varchar,CurrWeekEndDate),112)
              , @CurrToDateYTD      = convert(datetime,convert(varchar,CurrToDateYTD),112)
              , @CurrFromDateYTD    = convert(datetime,convert(varchar,CurrFromDateYTD),112)
              , @CurrFromDate       = convert(datetime,convert(varchar,CurrFromDate),112)
              , @CurrToDate         = convert(datetime,convert(varchar,CurrToDate),112)
              , @CurrToDateYTD      = convert(datetime,convert(varchar,CurrToDateYTD),112)
              , @CurrWeekBeginDate  = convert(datetime,convert(varchar,CurrWeekBeginDate),112)
              , @CurrWeekEndDate    = convert(datetime,convert(varchar,CurrWeekEndDate),112)
              , @PriorWeekBeginDate = convert(datetime,convert(varchar,PriorWeekBeginDate),112)
              , @PriorWeekEndDate   = convert(datetime,convert(varchar,PriorWeekEndDate),112)
              , @PriorFromDateYTD   = convert(datetime,convert(varchar,PriorFromDateYTD),112)
              , @PriorToDateYTD     = convert(datetime,convert(varchar,PriorToDateYTD),112)
              , @PriorFromDate      = convert(datetime,convert(varchar,PriorFromDate),112)
              , @PriorToDate        = convert(datetime,convert(varchar,PriorToDate),112)
        FROM dbo.Rpt_ReportingDates

        SELECT
                @CurrWeekEndDate    AS CurrWeekEndDate
              , @CurrToDateYTD      AS CurrToDateYTD
              , @CurrFromDateYTD    AS CurrFromDateYTD
              , @CurrFromDate       AS CurrFromDate
              , @CurrToDate         AS CurrToDate
              , @CurrToDateYTD      AS CurrToDateYTD
              , @CurrWeekBeginDate  AS CurrWeekBeginDate
              , @CurrWeekEndDate    AS CurrWeekEndDate
              , @PriorWeekBeginDate AS PriorWeekBeginDate
              , @PriorWeekEndDate   AS PriorWeekEndDate
              , @PriorFromDateYTD   AS PriorFromDateYTD
              , @PriorToDateYTD     AS PriorToDateYTD
              , @PriorFromDate      AS PriorFromDate
              , @PriorToDate        AS PriorToDate
 

Open in new window

{+ edit } and provide the result back here please
The reason for this is I need to understand how you actually define the date boundaries because this will affect the efficiency and accuracy of all subsequent queries.
0
PortletPaulfreelancerCommented:
Next question.

What would be the impact to you if that temp table had additional columns
but (a lot) fewer rows? i.e.
------------------------------------------------------------
-- 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)


    /* previously only these 2 columns were used
	, Gross_Sales decimal(38,2) NULL
	, Cost decimal(38,2) NULL
    */

    /* instead we use more columns and therefore perform fewer queries */
    , CurrentGross_Sales  decimal(38,2) NULL
    , CurrentCost decimal(38,2) NULL
    , PriorGross_Sales decimal(38,2) NULL
    , PriorCost decimal(38,2) NULL
    , CurrentYTDGross_Sales decimal(38,2) NULL
    , CurrentYTDCost decimal(38,2) NULL
    , PriorYTDGross_Sales decimal(38,2) NULL
    , PriorYTDCost decimal(38,2) NULL
   )

Open in new window

0
PortletPaulfreelancerCommented:
I was hoping to get answers to questions along the way - but as I may not have time over the next while to assist, this is the direction I was hoping you would adopt. A single query, that utilizes a set of case expressions to align the data into appropriate columns, and each of these is simplified by moving several conditions into the where clause. Plus a set of changes that relate to how you perform date range selection (this however is based on quite a number of assumptions that do need answers from you). Also the temp table has changed in structure to match the columns of the query.

Overall this should be way faster than performing essentially the same query 4 times.

I cannot guarantee this will run without error as I cannot test it, but here it is.
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

        /* previously these variables were declared as int
           and assuming dbo.Rpt_ReportingDates fields are int
           and assuming the values held look like this: 20131231 (i.e. YYYYMMDD as int)
           and assuming these variables will be compared to date/datetimes later
           then, convert these variables to datetime
           to minimize implicit conversions
           and to simplify all subsequent date/time operations
           */

        DECLARE @CurrFromDate       AS datetime
              , @CurrToDate         AS datetime
              , @CurrToDateYTD      AS datetime
              , @CurrFromDateYTD    AS datetime
              , @PriorFromDateYTD   AS datetime
              , @PriorToDateYTD     AS datetime
              , @CurrWeekBeginDate  AS datetime
              , @CurrWeekEndDate    AS datetime
              , @PriorWeekBeginDate AS datetime
              , @PriorWeekEndDate   AS datetime
              , @PriorFromDate      AS datetime
              , @PriorToDate        AS datetime
        SELECT
                @CurrWeekEndDate    = convert(datetime,convert(varchar,CurrWeekEndDate),112)
              , @CurrToDateYTD      = convert(datetime,convert(varchar,CurrToDateYTD),112)
              , @CurrFromDateYTD    = convert(datetime,convert(varchar,CurrFromDateYTD),112)
              , @CurrFromDate       = convert(datetime,convert(varchar,CurrFromDate),112)
              , @CurrToDate         = convert(datetime,convert(varchar,CurrToDate),112)
              , @CurrToDateYTD      = convert(datetime,convert(varchar,CurrToDateYTD),112)
              , @CurrWeekBeginDate  = convert(datetime,convert(varchar,CurrWeekBeginDate),112)
              , @CurrWeekEndDate    = convert(datetime,convert(varchar,CurrWeekEndDate),112)
              , @PriorWeekBeginDate = convert(datetime,convert(varchar,PriorWeekBeginDate),112)
              , @PriorWeekEndDate   = convert(datetime,convert(varchar,PriorWeekEndDate),112)
              , @PriorFromDateYTD   = convert(datetime,convert(varchar,PriorFromDateYTD),112)
              , @PriorToDateYTD     = convert(datetime,convert(varchar,PriorToDateYTD),112)
              , @PriorFromDate      = convert(datetime,convert(varchar,PriorFromDate),112)
              , @PriorToDate        = convert(datetime,convert(varchar,PriorToDate),112)
        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] [datetime] NULL -- << changes in data type
        , [CurrToDate] [datetime] NULL 
        , [CurrToDateYTD] [datetime] 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)

        /* previously only these 2 columns were used
        , Gross_Sales decimal(38,2) NULL
        , Cost decimal(38,2) NULL
        */

        /* instead we use more columns and therefore perform fewer queries */
        , CurrentGross_Sales  decimal(38,2) NULL
        , CurrentCost decimal(38,2) NULL
        , PriorGross_Sales decimal(38,2) NULL
        , PriorCost decimal(38,2) NULL
        , CurrentYTDGross_Sales decimal(38,2) NULL
        , CurrentYTDCost decimal(38,2) NULL
        , PriorYTDGross_Sales decimal(38,2) NULL
        , PriorYTDCost decimal(38,2) NULL
       ) 

----------------------------------------------------
-- insert for MTD Current and Prior MACOLA
----------------------------------------------------
if (@DB_Include = 0 or @DB_Include = 2) 
BEGIN

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(sls_amt),   
    --Cost = SUM(cost_amt)
	            -- '2 - MTD Current'
                CurrentGross_Sales = SUM(CASE WHEN ( OEHDRHST_SQL.Inv_dt >= @CurrFromDate         AND OEHDRHST_SQL.Inv_dt <= dateadd(day,1,@CurrToDate) )
                                       THEN sls_amt
                                       ELSE 0 END),
                CurrentCost = SUM(CASE WHEN ( OEHDRHST_SQL.Inv_dt >= @CurrFromDate                AND OEHDRHST_SQL.Inv_dt <= dateadd(day,1,@CurrToDate) )
                                       THEN	sls_amt
                                       ELSE 0 END),

                --'2 - MTD Prior'
                PriorGross_Sales = SUM(CASE WHEN ( OEHDRHST_SQL.Inv_dt >= @PriorFromDate          AND OEHDRHST_SQL.Inv_dt <= dateadd(day,1,@PriorToDate) )
                                       THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
                                       ELSE 0 END),
                PriorCost = SUM(CASE WHEN ( OEHDRHST_SQL.Inv_dt >= @PriorFromDate                 AND OEHDRHST_SQL.Inv_dt <= dateadd(day,1,@PriorToDate) )
                                       THEN	sls_amt
                                       ELSE 0 END),

                --'3 - YTD Current'
                CurrentYTDGross_Sales = SUM(CASE WHEN ( OEHDRHST_SQL.Inv_dt >= @CurrFromDateYTD  AND OEHDRHST_SQL.Inv_dt <= dateadd(day,1,@CurrToDateYTD) )
                                       THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
                                       ELSE 0 END),
                CurrentYTDCost = SUM(CASE WHEN ( OEHDRHST_SQL.Inv_dt >= @CurrFromDateYTD         AND OEHDRHST_SQL.Inv_dt <= dateadd(day,1,@CurrToDateYTD) )
                                       THEN	sls_amt
                                       ELSE 0 END),
                
                --'3 - YTD Prior'
                PriorYTDGross_Sales = SUM(CASE WHEN ( OEHDRHST_SQL.Inv_dt >= @PriorFromDateYTD  AND OEHDRHST_SQL.Inv_dt <= dateadd(day,1,@PriorToDateYTD) )
                                       THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
                                       ELSE 0 END),
                PriorYTDCost = SUM(CASE WHEN ( OEHDRHST_SQL.Inv_dt >= @PriorFromDateYTD         AND OEHDRHST_SQL.Inv_dt <= dateadd(day,1,@PriorToDateYTD) )
                                       THEN	sls_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  (
                  ( OEHDRHST_SQL.Inv_dt >= @CurrFromDate     AND OEHDRHST_SQL.Inv_dt <= dateadd(day,1,@CurrToDate) )
                  OR 
                  ( OEHDRHST_SQL.Inv_dt >= @PriorFromDate    AND OEHDRHST_SQL.Inv_dt <= dateadd(day,1,@PriorToDate) )
                  OR 
                  ( OEHDRHST_SQL.Inv_dt >= @CurrFromDateYTD  AND OEHDRHST_SQL.Inv_dt <= dateadd(day,1,@CurrToDateYTD) )
                  OR 
                  ( OEHDRHST_SQL.Inv_dt >= @PriorFromDateYTD AND OEHDRHST_SQL.Inv_dt <= dateadd(day,1,@PriorToDateYTD) )
               )
	
AND  	(@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  

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
                'RAMS',
                CM.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,
                PR_ID.ID_DESC AS BRAND,
                PD.PRD_R_BRANDNAME AS BRAND_ID,
                OD.OD_PRODUCT as Item_no,
                
                --'2 - MTD Current',
                CurrentGross_Sales = SUM(CASE WHEN ( OH.OH_DELIVERY_DATE >= @CurrFromDate     AND OH.OH_DELIVERY_DATE <= dateadd(day,1,@CurrToDate) )
                                       THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
                                       ELSE 0 END),
                CurrentCost = SUM(CASE WHEN ( OH.OH_DELIVERY_DATE >= @CurrFromDate     AND OH.OH_DELIVERY_DATE <= dateadd(day,1,@CurrToDate) )
                                       THEN	COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
                                       ELSE 0 END)

                --'2 - MTD Prior',
                PriorGross_Sales = SUM(CASE WHEN ( OH.OH_DELIVERY_DATE >= @PriorFromDate    AND OH.OH_DELIVERY_DATE <= dateadd(day,1,@PriorToDate) )
                                       THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
                                       ELSE 0 END),
                PriorCost = SUM(CASE WHEN ( OH.OH_DELIVERY_DATE >= @PriorFromDate    AND OH.OH_DELIVERY_DATE <= dateadd(day,1,@PriorToDate) )
                                       THEN	COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
                                       ELSE 0 END)

                --'3 - YTD Current',
                CurrentYTDGross_Sales = SUM(CASE WHEN ( OH.OH_DELIVERY_DATE >= @CurrFromDateYTD  AND OH.OH_DELIVERY_DATE <= dateadd(day,1,@CurrToDateYTD) )
                                       THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
                                       ELSE 0 END),
                CurrentYTDCost = SUM(CASE WHEN ( OH.OH_DELIVERY_DATE >= @CurrFromDateYTD  AND OH.OH_DELIVERY_DATE <= dateadd(day,1,@CurrToDateYTD) )
                                       THEN	COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
                                       ELSE 0 END)
                
                --'3 - YTD Prior',
                PriorYTDGross_Sales = SUM(CASE WHEN ( OH.OH_DELIVERY_DATE >= @PriorFromDateYTD AND OH.OH_DELIVERY_DATE <= dateadd(day,1,@PriorToDateYTD) )
                                       THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
                                       ELSE 0 END),
                PriorYTDCost = SUM(CASE WHEN ( OH.OH_DELIVERY_DATE >= @PriorFromDateYTD AND OH.OH_DELIVERY_DATE <= dateadd(day,1,@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_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'
                    INNER JOIN  CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD         
                                ON OD.OD_TICKET_NUMBER = OH_TICKET_NUMBER                                           
                    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'

        WHERE  (
                  ( OH.OH_DELIVERY_DATE >= @CurrFromDate     AND OH.OH_DELIVERY_DATE <= dateadd(day,1,@CurrToDate) )
                  OR 
                  ( OH.OH_DELIVERY_DATE >= @PriorFromDate    AND OH.OH_DELIVERY_DATE <= dateadd(day,1,@PriorToDate) )
                  OR 
                  ( OH.OH_DELIVERY_DATE >= @CurrFromDateYTD  AND OH.OH_DELIVERY_DATE <= dateadd(day,1,@CurrToDateYTD) )
                  OR 
                  ( OH.OH_DELIVERY_DATE >= @PriorFromDateYTD AND OH.OH_DELIVERY_DATE <= dateadd(day,1,@PriorToDateYTD) )
               )
        AND     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') -- Sale or BuyBack
        --          AND (ID.ID_TITLE = N'Sale Zone')

        GROUP BY    PR_ID.ID_DESC,
                    OD.OD_PRODUCT,
                    CM.CM_SELLZONE,
                    CUST_ID.IDVALUE,
                    CUST_ID.ID_TITLE,
                    PD.PRD_R_BRANDNAME

END -- DB_Include = 1 or 2
			
----------------------------------------------------
-- final select
----------------------------------------------------


select * from #SalesSummary
    
    

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
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
-- 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 @CurrToDate

Open in new window


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.
0
PortletPaulfreelancerCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.