Cannot identify why I do not see the result sets

The code I have been working with produces results for the Second Query For RAMS data but not the first Query for MACOLA. Is there something obvious that would return no results for the first query.....I commented out the where clause and tried different date ranges for the Date requirements. No luck today...?

USE [DATA_04]
Declare @Cus_no_Start varchar(12),
	 @Cus_no_End varchar(12), 
	 @DB_Include int -- 0 = Macola Only, 1 = RAMS Only, 2 = Macola and RAMS 

----------------------------------------------------
-- 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 DISTINCT

	'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 @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 = 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 



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














		


			
----------------------------------------------------
-- final select
----------------------------------------------------


 --  	select * from #SalesSummary

Open in new window

DualQueries-MACOLA-RAMS.png
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.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I cannot see anything else than the join as the culprit:
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

Open in new window

Is ArcusFil_SQL.Terr really a territory ID?
For test, you can use left outer joins instead, and then successive set back to inner joins until you do not get any result, or much less as you expert.
0
Brian CroweDatabase AdministratorCommented:
Replace your select with a "SELECT COUNT(*)" and then just progressively include one join at a time until you see your count go to zero then you will know which join is causing the issue.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
That is the coolest trick.....thanks for that . I do not know if other Experts have told me to do that with other questons I have asked......but this is the first time I understood how it was meant to be used.....I do not know I just was not getting it until now.

Here is the code BOLD that broke (went to a zero count)....I assume I need to check the links to make sure the records match. I was given this code and told it works. However the source could have been wrong....hence my issues. The left outer join works but the inner join after it goes to zero. Let me know if I am on the right path or any new tips appreciated. Thank you.

/********************/-- 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 = IMITMIDX_SQL.Item_no  
0
Brian CroweDatabase AdministratorCommented:
Yes, that means that you are joining on the wrong field, your data is wrong or there simply aren't any records that match your criteria.
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:
Great stuff to know. I was given the wrong field. I looked at the data and it had the wrong field therefore the wrong data assigned to it. That Select Count was cool and very helpful. Never saw that one before.
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.