how can i optimize query

Posted by Santosh4u under Sql Server on 9/26/2018 | Points: 10 | Views : 168 | Status : [Member] | Replies : 0
help me to optimize this query , its taking long time to get result. any better idea to replace like condition in case statment

Insert into tbl_Details
SELECT a.[COST],a.[Ch_Group] as 'CHGROUP',a.[PRODHIR],d.[MATERIAL],
a.[UCC],
CASE WHEN SUM(CAST(d.[Qty3] AS FLOAT))<='0.00000' THEN '0.00000'
ELSE
(
SUM(CAST(d.[Qty3] AS FLOAT))/
(SELECT (SUM(CAST(p.[Qty3] AS FLOAT))) AS 'SUM'
FROM [tbl_OP6] p WHERE CAST(p.[Qty3] as FLOAT)>0 AND p.[PROD_HIER] LIKE a.[PRODHIR]+'%'
AND CONCAT(p.[Qty2],p.[Qty3A],p.[Qty4A]) LIKE case when a.[Ch_Group] like 'PM60%' then '%' else a.[Ch_Group]+'%' end and p.[zsdistrib] like a.[UCC]+'%'
AND p.[PROD_HIER]LIKE a.[PRODHIR]+'%' AND CAST(p.[Qty3] AS FLOAT)>'0.00000')
)
end
AS 'GSV'
,'' as 'BUFCG'
FROM TEMP2_New a
INNER JOIN [tbl_OP6] d ON CONCAT(d.[Qty2],d.[Qty3A],d.[Qty4A]) LIKE case when a.[Ch_Group] like 'PM60%' then '%' else a.[Ch_Group]+'%' end and d.[zsdistrib] like a.[UCC]+'%'
AND d.[PROD_HIER]LIKE a.[PRODHIR]+'%' AND CAST(d.[Qty3] AS FLOAT)>'0.00000'
WHERE a.[RULE]='4'
GROUP BY a.[COST],a.[Ch_Group],a.[PRODHIR],d.[MATERIAL],a.[ORDERTYPE],
a.[VALUEFIELD],a.[UCC],
CONCAT(d.[Qty2],d.[Qty3A],d.[Qty4A]),a.[COORDER],a.[PLANT],a.[SEND_CCTR]




Responses

(No response found.)

Login to post response