set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[tradezone_review_plan]
AS
BEGIN
DECLARE @tempRowId NVARCHAR(32);
--DECLARE @reviewTime NVARCHAR(10);
--DECLARE @reviewYear NVARCHAR(4);
--DECLARE @reviewMonth NVARCHAR(2);
--DECLARE @tempReviewTime NVARCHAR(10);
SET @tempRowId = REPLACE(NEWID(),'-','');
WITH D(
ROW_ID,
BRAND,
REVIEW_DATE
)AS(
SELECT A.ROW_ID,A.BRAND,B.REVIEW_DATE
FROM T_NWP_TRADEZONE_INFO A
LEFT JOIN T_NWP_TRADEZONE_REVIEW B
ON A.ROW_ID = B.FK_ROW_ID
WHERE B.REVIEW_DATE IS NULL
),
X AS(
SELECT E.ROW_ID,E.BRAND,F.REVIEW_DATE FROM T_NWP_TRADEZONE_INFO E
JOIN T_NWP_TRADEZONE_REVIEW F
ON E.ROW_ID = F.FK_ROW_ID
WHERE F.ROW_ID = (
SELECT TOP 1 G.ROW_ID FROM T_NWP_TRADEZONE_REVIEW G WHERE E.ROW_ID = G.FK_ROW_ID ORDER BY G.REVIEW_DATE DESC
)
UNION ALL
SELECT D.ROW_ID,D.BRAND,CONVERT(NVARCHAR(10), H.CREATE_TIME,20) AS REVIEW_DATE FROM D
JOIN H_NWP_TRADE_PLAN_REPORT H
ON D.ROW_ID = H.FK_ROW_ID
WHERE H.ROW_ID = (
SELECT TOP 1 I.ROW_ID FROM H_NWP_TRADE_PLAN_REPORT I WHERE I.FK_ROW_ID = D.ROW_ID ORDER BY I.CREATE_TIME
)
)
INSERT INTO T_NWP_TRADEZONE_REVIEW_TEST_PLAN
(
ROW_ID,
TRADEZONE_ID,
GROUP_ID,
SORT_ID,
PLAN_TYPE,
BRAND,
YEAR,
MONTH,
TEST_PLAN_DATE,
YEAR_PLAN_FLAG,
MONTH_PLAN_FLAG,
NO_REVIEW_FLAG,
NO_REVIEW_REASON,
AUDIT_FLAG,
CHANGE_FLAG,
CREATOR_ID,
CREATE_TIME,
UPDATOR_ID,
UPDATE_TIME,
DELETE_FLAG,
TEST_PLAN_BAK_DATE
)SELECT REPLACE(NEWID(),'-',''),
X.ROW_ID,
REPLACE(NEWID(),'-',''),
'0',
'M1121',
X.BRAND,
CASE WHEN (CONVERT(NVARCHAR(4), GETDATE(),20) - CAST(CONVERT(NVARCHAR(4),X.REVIEW_DATE) AS INT)) > 0
THEN (CONVERT(NVARCHAR(4), GETDATE(),20) + 1 )
ELSE (CONVERT(NVARCHAR(4), X.REVIEW_DATE,20) + 1 )
END,
CASE WHEN (CONVERT(NVARCHAR(4), GETDATE(),20) - CAST(CONVERT(NVARCHAR(4),X.REVIEW_DATE) AS INT)) > 0
THEN '01'
ELSE SUBSTRING(X.REVIEW_DATE,6,2)
END,
CASE WHEN (CONVERT(NVARCHAR(4), GETDATE(),20) - CAST(CONVERT(NVARCHAR(4),X.REVIEW_DATE) AS INT)) > 0
THEN CAST((CONVERT(NVARCHAR(4), GETDATE(),20) + 1 ) AS NVARCHAR(4))+ '-01-01'
ELSE REPLACE(X.REVIEW_DATE,SUBSTRING(X.REVIEW_DATE,1,4),SUBSTRING(X.REVIEW_DATE,1,4)+1)
END,
'M0009',
'M0009',
'M0817',
'M1170',
'',
'',
'admin',
GETDATE(),
'admin',
GETDATE(),
'1',
CASE WHEN (CONVERT(NVARCHAR(4), GETDATE(),20) - CAST(CONVERT(NVARCHAR(4),X.REVIEW_DATE) AS INT)) > 0
THEN CAST((CONVERT(NVARCHAR(4), GETDATE(),20) + 1 ) AS NVARCHAR(4))+ '-01-01'
ELSE REPLACE(X.REVIEW_DATE,SUBSTRING(X.REVIEW_DATE,1,4),SUBSTRING(X.REVIEW_DATE,1,4)+1)
END
FROM X
END
对应昨天的游标改成子表查询
最新推荐文章于 2018-05-19 08:14:57 发布