Solved

Error at the THEN clause

Posted on 2013-10-22
7
225 Views
Last Modified: 2013-10-23
I have the Query thanks to members of EE down to 2:13 seconds down from 43 minutes.
I noticed that the data I was getting looked wrong. So I assumed I cut too much. However I get the following error. for the code below.

Msg 156, Level 15, State 1, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 449
Incorrect syntax near the keyword 'THEN'.
Msg 156, Level 15, State 1, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 532
Incorrect syntax near the keyword 'THEN'.
Msg 156, Level 15, State 1, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 618
Incorrect syntax near the keyword 'THEN'.
Msg 156, Level 15, State 1, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 708
Incorrect syntax near the keyword 'THEN'.

I have tried everything but nothing works. I tried a parenthesis in front of the THEN and many other things. Is there something obvious Please advise thank you.

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)
		AND OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDate AND @PriorToDate
		
		 
		
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 @CurrFromDateYTD AND @CurrToDateYTD 
         --THEN sls_amt ELSE 0 END),
    Gross_Sales = SUM(sls_amt),   
	--Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDateYTD AND @CurrToDateYTD 
         --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 @CurrFromDateYTD AND @CurrToDateYTD
		
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 @PriorFromDateYTD AND @PriorToDate YTD
         --THEN sls_amt ELSE 0 END),
    Gross_Sales = SUM(sls_amt),   
	--Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDateYTD AND @PriorToDate YTD 
         --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 @PriorFromDateYTD AND @PriorToDateYTD

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),
--		Gross_Sales = SUM(COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)),
		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)
--		Cost = SUM(COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0))

		
						   
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 CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrFromDate AND @CurrToDate
			--AND 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

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),
--		Gross_Sales = SUM(COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)),
		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)
--		Cost = SUM(COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0))
		
						   
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 CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @PriorFromDate AND @PriorToDate
			--AND 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

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),
--		Gross_Sales = SUM(COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)),
		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)
--		Cost = SUM(COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0))

		
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 CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrFromDateYTD AND @CurrToDateYTD
			--AND 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

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),
--		Gross_Sales = SUM(COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)),
		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)
--		Cost = SUM(COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0))

		
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 CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @PriorFromDateYTD AND @PriorToDateYTD
			--AND 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

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
Comment
Question by:ruavol2
7 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
The below line, why are you converting a date column to a char value (apple), if you are then going to comapare it to two date variables (oranges)?
AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrFromDate AND @CurrToDate)

Open in new window

0
 
LVL 11

Assisted Solution

by:Simone B
Simone B earned 200 total points
Comment Utility
The code below will parse successfully, without syntax errors. I removed the first bracket in all of the CASEs, as it doesn't having a matching bracket to finish off with.

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)
		AND OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDate AND @PriorToDate
		
		 
		
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 @CurrFromDateYTD AND @CurrToDateYTD 
         --THEN sls_amt ELSE 0 END),
    Gross_Sales = SUM(sls_amt),   
	--Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDateYTD AND @CurrToDateYTD 
         --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 @CurrFromDateYTD AND @CurrToDateYTD
		
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 @PriorFromDateYTD AND @PriorToDate YTD
         --THEN sls_amt ELSE 0 END),
    Gross_Sales = SUM(sls_amt),   
	--Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDateYTD AND @PriorToDate YTD 
         --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 @PriorFromDateYTD AND @PriorToDateYTD

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),
--		Gross_Sales = SUM(COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)),
		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)
--		Cost = SUM(COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0))

		
						   
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 CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrFromDate AND @CurrToDate
			--AND 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

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),
--		Gross_Sales = SUM(COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)),
		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)
--		Cost = SUM(COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0))
		
						   
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 CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @PriorFromDate AND @PriorToDate
			--AND 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

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),
--		Gross_Sales = SUM(COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)),
		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)
--		Cost = SUM(COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0))

		
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 CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrFromDateYTD AND @CurrToDateYTD
			--AND 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

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),
--		Gross_Sales = SUM(COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)),
		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)
--		Cost = SUM(COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0))

		
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 CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @PriorFromDateYTD AND @PriorToDateYTD
			--AND 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

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
 
LVL 9

Accepted Solution

by:
COANetwork earned 200 total points
Comment Utility
Line 449 (and apparently the rest of your error lines) are commented out in your code.  but those lines end on a closing ")" - that is the parenthesis that is opened on this line here: "WHEN (OH.OH_SALE_TYPE".  It needs to be closed.  If you put a closing ")" BEFORE the "THEN" it should work.  If it does not - try formatting your code with each opening and closing parenthesis on a separate line, properly indented to line up vertically.  It sounds like you have a set of mismatched parenthesis somewhere.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 total points
Comment Utility
ruavol2

if you do not want my assistance would you please let me know.
this issue, and many others related to it are subject to at least 3 other questions that are on-going.

Others may want to visit these:
(10/11/13) http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28264933.html
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28271096.html#a39591362

there are several issues to resolve, starting with:
a. the integer variables (that look like they should be datetime)
b. reducing the number of queries executed
c. possible re-design of the temp table
d. the where clauses (including improving the reliability of date ranges)
f. implicit conversions
g. removing functions such as "CONVERT(char(8),OH.OH_DELIVERY_DATE,112)"
more perhaps

While I have been waiting for feedback, you open more questions.
Does that mean you are not interested in my suggestions?
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 total points
Comment Utility
and COANetwork is quite right, those errors are caused by removing lines that have a required closing parentheses....

but, every one of those case expressions needs simplification anyway:
- by a better where clause
- by how one filters for a date range

If I could just gain your undivided attention for a while.....
on other questions I have outstanding questions for you.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
please refer to questions at ID39592789, ID39592841, ID39592917
and suggestion, ID39593314
0
 

Author Closing Comment

by:ruavol2
Comment Utility
Thank you for your help. I kept trying to close the parenthesis on one of those lines and it never would work. Never thought about taking the open parenthesis out.....altogether.

PortletPaul......man thanks for what you have done. I have been stuck in a mess. I know about 75% of the applications the client needs me to know SAP Crystal Reports and and Crystal Enterprise. Then there is some SQL obviously which I am dangerous. The situation I am in is a mess but I am doing my best to get this done. You guys are amazing. Thanks for your help.

Mr. Horn......that code was given to me by a self proclaimed novice coder who said they got it to work so I have been going with stuff like that just to keep up the pace. This project was to fix 24 reports......then I found out what was involved with the Stored Procedures. They thought this stuff should be done in 4 weeks. Should have been 4 months project. Thanks for bearing with me.

It is funny clients think they can just find a person that is a brain surgeon world class chef, an interior designer and fighter pilot all in the same person and that person can perform miracles like Moses parting the Red Sea....yeah those guys live on every street corner....!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now