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.
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>get me a clean transfer of information from the SUM formula to the WHERE clause.
Explain for me in standard English terms a six-year old would understand exactly what you are trying to do here.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
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

0
mlmccCommented:
I assume if you got rid of the cost and gross_sales columns from the query, it will run quickly.

If you put the restrictions on the sum into the where clause you will only get the records that meet that criteria rather than all the records you are getting now which I assume include records where the cost/gross_sales don't meet the criteria of the case.  

Do you want to restrict the records to only those that meet the cost and sales criteria?
If so then just move this to the where clause

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)

Open in new window


Gross_Sales = SUM(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE)

Similarly for Cost

mlmcc
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PortletPaulEE Topic AdvisorCommented:
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_28271096.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

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:
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
PortletPaulEE Topic AdvisorCommented:
please refer to questions at ID39592789, ID39592841, ID39592917
and suggestion, ID39593314
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
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.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Thanks for all your help.
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.