RUA Volunteer2?
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_DELI VERY_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_D ELIV_QUANT ITY * 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_DELI VERY_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.
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_DELI
AND @CurrToDate)
THEN
COALESCE(OD.OD_TOTAL_DELIV
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_D
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_DELI
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.....?
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.....?
ASKER
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
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.
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
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.
ASKER
Thanks for all your help.
ASKER
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.
Open in new window