ALTER PROCEDURE [dbo].[P_POPriceTracking]
@RecStartDate DATETIME,
@RecEndDate DATETIME,
@VendorType NVARCHAR(10) ASSELECT
A.PO,
A.PN,
A.INTENTNO,
--A.RECNUM,
CAST(A.RCSYSDAT+26234 AS DATETIME) AS ReceivedDate,
A.RCQTY AS ReceivedQty,
--A.ORDQTY AS OrderQty_Rec,
--ROUND(A.RCRFCUC,2) AS POUC_Rec,
A.PUOM,
B.BCODE,
B.OrderQty AS OrderQty_PO,
ROUND(B.PORFCUC,3) AS POUC_Current,
C.VNDCURR,ROUND((
SELECT TOP 1 RCRFCUC FROM dbo.V_ERCHSFIL
WHERE PN = B.PN
AND CAST(RCSYSDAT+26234 AS DATETIME) >= DATEADD(M,-1,CAST(A.RCSYSDAT+26234 AS DATETIME))
AND CAST(RCSYSDAT+26234 AS DATETIME) < CAST(A.RCSYSDAT+26234 AS DATETIME)
ORDER BY RCSYSDAT ASC
),3) AS POUC_Past1Month,ROUND((
SELECT TOP 1 RCRFCUC FROM dbo.V_ERCHSFIL
WHERE PN = B.PN
AND CAST(RCSYSDAT+26234 AS DATETIME) >= DATEADD(M,-2,CAST(A.RCSYSDAT+26234 AS DATETIME))
AND CAST(RCSYSDAT+26234 AS DATETIME) < DATEADD(M,-1,CAST(A.RCSYSDAT+26234 AS DATETIME))
ORDER BY RCSYSDAT ASC
),3) AS POUC_Past2Month,ROUND((
SELECT TOP 1 RCRFCUC FROM dbo.V_ERCHSFIL
WHERE PN = B.PN
AND CAST(RCSYSDAT+26234 AS DATETIME) >= DATEADD(M,-3,CAST(A.RCSYSDAT+26234 AS DATETIME))
AND CAST(RCSYSDAT+26234 AS DATETIME) < DATEADD(M,-2,CAST(A.RCSYSDAT+26234 AS DATETIME))
ORDER BY RCSYSDAT ASC
),3) AS POUC_Past3Month,ROUND((
SELECT TOP 1 RCRFCUC FROM dbo.V_ERCHSFIL
WHERE PN = B.PN
AND CAST(RCSYSDAT+26234 AS DATETIME) >= DATEADD(M,-4,CAST(A.RCSYSDAT+26234 AS DATETIME))
AND CAST(RCSYSDAT+26234 AS DATETIME) < DATEADD(M,-3,CAST(A.RCSYSDAT+26234 AS DATETIME))
ORDER BY RCSYSDAT ASC
),3) AS POUC_Past4Month,ROUND((
SELECT TOP 1 RCRFCUC FROM dbo.V_ERCHSFIL
WHERE PN = B.PN
AND CAST(RCSYSDAT+26234 AS DATETIME) >= DATEADD(M,-5,CAST(A.RCSYSDAT+26234 AS DATETIME))
AND CAST(RCSYSDAT+26234 AS DATETIME) < DATEADD(M,-4,CAST(A.RCSYSDAT+26234 AS DATETIME))
ORDER BY RCSYSDAT ASC
),3) AS POUC_Past5Month,ROUND((
SELECT TOP 1 RCRFCUC FROM dbo.V_ERCHSFIL
WHERE PN = B.PN
AND CAST(RCSYSDAT+26234 AS DATETIME) >= DATEADD(M,-6,CAST(A.RCSYSDAT+26234 AS DATETIME))
AND CAST(RCSYSDAT+26234 AS DATETIME) < DATEADD(M,-5,CAST(A.RCSYSDAT+26234 AS DATETIME))
ORDER BY RCSYSDAT ASC
),3) AS POUC_Past6Month,B.VCODE,
B.VNDNAMEFROM dbo.V_ERCHSFIL A
INNER JOIN dbo.V_POMasDetail B
ON (A.PO = B.PO AND A.INTENTNO = B.INTENTNO AND A.PN = B.PN)
LEFT JOIN SQLSRV.MMDATA.dbo.EVNDMAS C
ON B.VCODE = C.VC
WHERE CAST(A.RCSYSDAT+26234 AS DATETIME) >= @RecStartDate
AND CAST(A.RCSYSDAT+26234 AS DATETIME) <= @RecEndDate
AND A.RCCMD IN ('TR,400','TR,402')
AND B.VCODE LIKE '%'+@VendorType+'%'
AND B.VNDNAME NOT LIKE '%MEASUREMENT%' AND
(ROUND(B.PORFCUC,3) -
ROUND((
SELECT TOP 1 RCRFCUC FROM dbo.V_ERCHSFIL
WHERE PN = B.PN
AND CAST(RCSYSDAT+26234 AS DATETIME) >= DATEADD(M,-1,CAST(A.RCSYSDAT+26234 AS DATETIME))
AND CAST(RCSYSDAT+26234 AS DATETIME) < CAST(A.RCSYSDAT+26234 AS DATETIME)
ORDER BY RCSYSDAT ASC
),3)) > 0
1. 改写SQL,必要时添加临时表或修改表结构,把SELECT子句下的子查询去掉。
2. 类似“CAST(A.RCSYSDAT+26234 AS DATETIME) >= @RecStartDate AND CAST(A.RCSYSDAT+26234 AS DATETIME) <= @RecEndDate”的写法将不能使用索引。改写成SARG。
3. 合理设计索引。
AND B.VNDNAME NOT LIKE '%MEASUREMENT%' 这些去掉一个% 。是查不出数据。加上才行