Link to home
Start Free TrialLog in
Avatar of RUA Volunteer2?
RUA Volunteer2?Flag for United States of America

asked on

How do I convert this into a WHERE Clause so that the query is more efficient.

I have four Select statements that use this formula. I was told in order to speed up the query I should put this into the WHERE clause. That this would help performance tremendously.

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)

How do I put this in the WHERE clause properly.....Here is my best guess.....am I close...?

The COST formula should be
COST = SUM(COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)            

The Where should be
WHERE 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)

Will that get me a clean transfer of information from the SUM formula to the WHERE clause. Your help is grately appreciated. Thank you.
SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RUA Volunteer2?

ASKER

The Formula above COST is part of a Select here. I was told that the reason my SP was running so slow was do to the COST = SUM(??? in one of the 4 Selects for the RAMS Database. If I put the filtered results in the WHERE clause the SP would run much faster. I was trying to see if I was going in the right direction with my example I showed above.

In other words if I am trying to convert everything in the COST to an efficient WHERE clause. Is that better sorry..... sometimes I just do not kwow how to craft the question.


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

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.....?
please refer to questions at ID39592789, ID39592841, ID39592917
and suggestion, ID39593314
Here is what I did to fix the inefficiency.
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 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. I got what I got and moving forward the best I can with these questions. 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.
Thanks for all your help.