[步骤]
发现 RB_Product_Log 未添加nolock 请补全好 改导出表中的 所有 nolock
[15:39:17] -------------------------------------------------------------
CODE:N1539171
Error:21_596_1_-2146232060
SQL:/*
[1] 修改人：蓝静怡 修改时间：20230421 修改逻辑：关联上采购下单相关数据取样品单下单、生单、销单时间（同一个sku多个采购、下单、销数据取最后那条数据）
[2] 修改人：蓝静怡 修改时间：20230718 修改逻辑：新增sheet明细表，包含非策略生成组合sku+非组合sku
*/
DROP TABLE IF EXISTS #TEMP_RB_Product_Development_Status,#temp_by_admin,#temp_diff,#temp_diff2,#temp_tb
--DECLARE @startdate DATETIME = '2024-02-01'
--DECLARE @enddate DATETIME = '2024-03-01'
--DECLARE @CustomerID INT = 1
-- 获取销售审核时间
SELECT
a.ProductDevelopmentId,
a.AddTime
INTO #TEMP_RB_Product_Development_Status
FROM(
SELECT
a.ProductDevelopmentId,
a.AddTime,
ROW_NUMBER() OVER(PARTITION BY a.ProductDevelopmentId, a.CustomerID ORDER BY a.AddTime DESC) [rank]
FROM dbo.RB_Product_Development_Status a (NOLOCK)
WHERE
a.CustomerID=1
AND a.Remarks='销售已审核'
) a
WHERE a.rank=1
IF OBJECT_ID('Tempdb..#temp_by_admin') IS NOT NULL
DROP TABLE #temp_by_admin
CREATE TABLE #temp_by_admin ([月份] VARCHAR(50),
[操作人员] VARCHAR(50),
[部门] VARCHAR(50),
[图片来源] VARCHAR(50),
[提报到销售审核] FLOAT,
[提报到添加] FLOAT,
分配到侵权回复 FLOAT,
分配到添加 FLOAT,
分配到开发 FLOAT,
[添加到开发] FLOAT,
添加到样品单生单 FLOAT,
样品单生单到下单 FLOAT,
样品单生单到销单 FLOAT,
[添加到开发审核] FLOAT,
[开发到开发审核] FLOAT,
[添加到知产] FLOAT,
[开发到风控] FLOAT,
[开发到拍照] FLOAT,
[登记到拍照] FLOAT,
[拍照到图片] FLOAT,
[开发到图片] FLOAT,
[图片到图片审核] FLOAT,
[图片到图文审核] FLOAT,
[图片审核到图文审核] FLOAT,
[开发到编辑] FLOAT,
[编辑到编辑审核] FLOAT,
[编辑到图片] FLOAT,
[图片到编辑审核] FLOAT,
[开发到上架] FLOAT,
[编辑上架] FLOAT,
[提报到上架] FLOAT);
--销售提报--
SELECT A.CustomerID,
ISNULL(ApplicationNumber, '') ApplicationNumber,
DevelopmentName,
DevelopmentTime,
B.SKU,
C.AddTime [销售审核时间],
D.SubmitTime 侵权提报时间,
D.RecoveryTime 侵权回复时间,
A.DevelopmentTime 分配开发时间
INTO #temp_tb
FROM RB_Product_Development A WITH (NOLOCK)
LEFT JOIN RB_ProductDevelopment_Related_Product B WITH (NOLOCK) ON A.CustomerID = B.CustomerID AND A.Id = B.ProductDevelopmentId
LEFT JOIN #TEMP_RB_Product_Development_Status C ON C.ProductDevelopmentId = B.ProductDevelopmentId
LEFT JOIN RB_Product_IntellectualProperty_Management D WITH (NOLOCK) ON D.CustomerID = A.CustomerID AND D.DevelopmentId = A.Id
WHERE
A.CustomerID = @CustomerID
AND CurrentStatus = 'Completed'
AND ISNULL(B.SKU, '') != ''
--SELECT * FROM #temp_tb
--WHERE 侵权提报时间 IS NOT NULL AND ApplicationNumber='0398198'
SELECT a.SKU,
CASE b.DevelopType
WHEN 1 THEN '自主开发'
WHEN 2 THEN '供应商后台'
WHEN 3 THEN '指定开发'
WHEN 4 THEN '智能选品'
WHEN 5 THEN '销售提报_FBA新品'
WHEN 6 THEN '智选FBA'
ELSE ''
END [开发类型],
CASE b.PictureSource
WHEN 1 THEN '采集图'
WHEN 2 THEN '供应商提供图'
WHEN 3 THEN '自己拍照图'
ELSE '' END [图片来源],
ISNULL(ISNULL(t.ApplicationNumber, ''), spp.ApplicationNo) tbnode,
(CASE
WHEN ISNULL(pb.ID, 0) = 0 THEN '否'
ELSE '是' END) [是否品牌],
ISNULL(a.AddAdminName, '') AddAdminName,
ISNULL(a.AddAdminID, 0) AddAdminID,
ISNULL(a.DevelopAdminName, '') DevelopAdminName,
ISNULL(a.DevelopAdminID, 0) DevelopAdminID,
ISNULL(a.IntellectualPropertyRightsAdminName, '') IntellectualPropertyRightsAdminName,
ISNULL(a.IntellectualPropertyRightsAdminID, 0) IntellectualPropertyRightsAdminID,
ISNULL(a.RiskControlEnquiryAdminName, '') RiskControlEnquiryAdminName,
ISNULL(a.RiskControlEnquiryAdminId, 0) RiskControlEnquiryAdminId,
ISNULL(a.PhotographerName, '') PhotographerName,
ISNULL(a.PhotographerId, 0) PhotographerId,
ISNULL(a.ImageAdminName, '') ImageAdminName,
ISNULL(a.ImageAdminID, 0) ImageAdminID,
ISNULL(a.EditAdminName, '') EditAdminName,
ISNULL(a.EditAdminID, 0) EditAdminID,
ISNULL(a.DevelopmentAuditOperatorName, '') DevelopmentAuditOperatorName,
ISNULL(a.DevelopmentAuditOperatorId, 0) DevelopmentAuditOperatorId,
ISNULL(a.PictureAuditOperatorName, '') PictureAuditOperatorName,
ISNULL(a.PictureAuditOperatorId, 0) PictureAuditOperatorId,
ISNULL(a.EditAuditOperatorName, '') EditAuditOperatorName,
ISNULL(a.EditAuditOperatorId, 0) EditAuditOperatorId,
ISNULL(a.CheckAdminName, '') CheckAdminName,
ISNULL(a.CheckAdminID, 0) CheckAdminID,
ISNULL(pro.登记人员, '') 登记人员,
ISNULL(pro.登记人员ID, 0) 登记人员ID,
ISNULL(a.ProductPicAndTextAuditUserName, '') ProductPicAndTextAuditUserName,
ISNULL(a.ProductPicAndTextAuditAdminId, 0) ProductPicAndTextAuditAdminId,
a.ProductPicAndTextAuditTime,
pro.登记时间,
( SELECT TOP (1) CS.AddTime
FROM RB_ProductDevelopment_Related_Product BS WITH (NOLOCK)
LEFT JOIN RB_Product_Development_Status CS WITH (NOLOCK)
ON BS.CustomerID = CS.CustomerID
AND BS.ProductDevelopmentId = CS.ProductDevelopmentId
WHERE BS.CustomerID = @CustomerID
AND SKU = a.SKU
AND CS.Status = 'Committed'
ORDER BY CS.AddTime DESC) tbtime,
a.AddTime,
pro.样品单生单时间,
pro.样品单下单时间,
pro.样品单销单时间,
a.DevelopTime,
a.IntellectualPropertyRightsTime,
a.RiskControlEnquiryTime,
a.PhotographerTime,
a.ImageTime,
a.EditTime,
a.ProductDevelopmentAuditTime,
a.ProductPictureAuditTime,
a.ProductEditAuditTime,
a.OnlineTime,
t.销售审核时间,
t.侵权提报时间,
t.侵权回复时间,
--a.AssignDevelopTime,
t.分配开发时间,
ISNULL(pb.BrandName, '') BrandName,
ISNULL(DATEDIFF(MINUTE,
ISNULL(( SELECT TOP 1 CS.AddTime
FROM RB_ProductDevelopment_Related_Product BS WITH (NOLOCK)
LEFT JOIN RB_Product_Development_Status CS WITH (NOLOCK)
ON BS.CustomerID = CS.CustomerID
AND BS.ProductDevelopmentId = CS.ProductDevelopmentId
WHERE BS.CustomerID = @CustomerID
AND SKU = a.SKU
AND CS.Status = 'Committed'
ORDER BY CS.AddTime DESC),
t.DevelopmentTime),
a.AddTime) / 1440.0,
0) [提报到添加],
ISNULL(DATEDIFF(MINUTE,
ISNULL(( SELECT TOP 1 CS.AddTime
FROM RB_ProductDevelopment_Related_Product BS WITH (NOLOCK)
LEFT JOIN RB_Product_Development_Status CS WITH (NOLOCK)
ON BS.CustomerID = CS.CustomerID
AND BS.ProductDevelopmentId = CS.ProductDevelopmentId
WHERE BS.CustomerID = @CustomerID
AND SKU = a.SKU
AND CS.Status = 'Committed'
ORDER BY CS.AddTime DESC),
t.DevelopmentTime),
a.ProductDevelopmentAuditTime) / 1440.0,
0) [提报到审核],
ISNULL(DATEDIFF(MINUTE,
ISNULL(( SELECT TOP 1 CS.AddTime
FROM RB_ProductDevelopment_Related_Product BS WITH (NOLOCK)
LEFT JOIN RB_Product_Development_Status CS WITH (NOLOCK)
ON BS.CustomerID = CS.CustomerID
AND BS.ProductDevelopmentId = CS.ProductDevelopmentId
WHERE BS.CustomerID = @CustomerID
AND SKU = a.SKU
AND CS.Status = 'Committed'
ORDER BY CS.AddTime DESC),
t.DevelopmentTime),
t.销售审核时间) / 1440.0,
0) [提报到销售审核],
ISNULL(DATEDIFF(MINUTE, t.分配开发时间, t.侵权回复时间) / 1440.0, 0) [分配到侵权回复],
ISNULL(DATEDIFF(MINUTE, t.分配开发时间, a.AddTime) / 1440.0, 0) [分配到添加],
ISNULL(DATEDIFF(MINUTE, t.分配开发时间, a.DevelopTime) / 1440.0, 0) [分配到开发],
ISNULL(DATEDIFF(MINUTE, a.AddTime, a.DevelopTime) / 1440.0, 0) [添加到开发],
ISNULL(DATEDIFF(MINUTE, a.AddTime, a.ProductDevelopmentAuditTime) / 1440.0, 0) [添加到开发审核],
ISNULL(DATEDIFF(MINUTE, a.DevelopTime, a.ProductDevelopmentAuditTime) / 1440.0, 0) [开发到开发审核],
ISNULL(DATEDIFF(MINUTE, a.AddTime, a.IntellectualPropertyRightsTime) / 1440.0, 0) [添加到知产],
ISNULL(DATEDIFF(MINUTE, a.DevelopTime, a.RiskControlEnquiryTime) / 1440.0, 0) [开发到风控],
ISNULL(DATEDIFF(MINUTE, a.DevelopTime, a.PhotographerTime) / 1440.0, 0) [开发到拍照],
ISNULL(DATEDIFF(MINUTE, pro.摄影完成时间, pro.图片处理时间) / 1440.0, 0) [拍照到图片],
ISNULL(DATEDIFF(MINUTE, pro.登记时间, pro.图片审核时间) / 1440.0, 0) [开发到图片],
ISNULL(DATEDIFF(MINUTE, pro.图片处理时间, pro.图片审核时间) / 1440.0, 0) [图片到图片审核],
ISNULL(DATEDIFF(MINUTE, a.DevelopTime, a.EditTime) / 1440.0, 0) [开发到编辑],
ISNULL(DATEDIFF(MINUTE, a.EditTime, a.ProductEditAuditTime) / 1440.0, 0) [编辑到编辑审核],
ISNULL(DATEDIFF(MINUTE, a.EditTime, a.ImageTime) / 1440.0, 0) [编辑到图片],
ISNULL(DATEDIFF(MINUTE, a.ImageTime, a.ProductEditAuditTime) / 1440.0, 0) [图片到编辑审核],
ISNULL(DATEDIFF(MINUTE, a.EditTime, a.OnlineTime) / 1440.0, 0) [编辑上架],
ISNULL(DATEDIFF(MINUTE, a.DevelopTime, a.OnlineTime) / 1440.0, 0) [开发到上架],
ISNULL(DATEDIFF(MINUTE, a.AddTime, pro.样品单生单时间) / 1440.0, 0) [添加到样品单生单],
ISNULL(DATEDIFF(MINUTE, pro.样品单生单时间, pro.样品单下单时间) / 1440.0, 0) [样品单生单到下单],
ISNULL(DATEDIFF(MINUTE, pro.样品单生单时间, pro.样品单销单时间) / 1440.0, 0) [样品单生单到销单],
ISNULL(DATEDIFF(MINUTE, pro.登记时间, pro.摄影完成时间) / 1440.0, 0) [登记到拍照],
ISNULL(DATEDIFF(MINUTE, a.ImageTime, a.ProductPicAndTextAuditTime) / 1440.0, 0) [图片到图文审核],
ISNULL(DATEDIFF(MINUTE, a.ProductPictureAuditTime, a.ProductPicAndTextAuditTime) / 1440.0, 0) [图片审核到图文审核],
ISNULL(
DATEDIFF(
MINUTE,
ISNULL(( SELECT TOP 1 CS.AddTime
FROM RB_ProductDevelopment_Related_Product BS WITH (NOLOCK)
LEFT JOIN RB_Product_Development_Status CS WITH (NOLOCK)
ON BS.CustomerID = CS.CustomerID
AND BS.ProductDevelopmentId = CS.ProductDevelopmentId
WHERE BS.CustomerID = @CustomerID
AND SKU = a.SKU
AND CS.Status = 'Committed'
ORDER BY CS.AddTime DESC),
ISNULL(t.DevelopmentTime, a.AddTime)),
a.OnlineTime) / 1440.0,
0) [提报到上架],
CASE
WHEN ( a.OnlineTime IS NULL
OR ( ISNULL(( SELECT TOP 1 CS.AddTime
FROM RB_ProductDevelopment_Related_Product BS WITH (NOLOCK)
LEFT JOIN RB_Product_Development_Status CS WITH (NOLOCK)
ON BS.CustomerID = CS.CustomerID
AND BS.ProductDevelopmentId = CS.ProductDevelopmentId
WHERE BS.CustomerID = @CustomerID
AND SKU = a.SKU
AND CS.Status = 'Committed'
ORDER BY CS.AddTime DESC),
t.DevelopmentTime) < a.OnlineTime
AND a.AddTime < a.OnlineTime))
AND a.AddTime >= @startdate
AND a.AddTime < @enddate THEN 1
ELSE 0 END tb_tj,
-- --新增
CASE
WHEN ( a.OnlineTime IS NULL
OR ( a.AddTime < a.OnlineTime
AND pro.样品单生单时间 < a.OnlineTime))
AND pro.样品单生单时间 >= @startdate
AND pro.样品单生单时间 < @enddate THEN 1
ELSE 0 END tj_sd,
CASE
WHEN ( a.OnlineTime IS NULL
OR ( pro.样品单生单时间 < a.OnlineTime
AND pro.样品单下单时间 < a.OnlineTime))
AND pro.样品单下单时间 >= @startdate
AND pro.样品单下单时间 < @enddate THEN 1
ELSE 0 END sd_xiadan,
CASE
WHEN ( a.OnlineTime IS NULL
OR ( pro.样品单生单时间 < a.OnlineTime
AND pro.样品单销单时间 < a.OnlineTime))
AND pro.样品单销单时间 >= @startdate
AND pro.样品单销单时间 < @enddate THEN 1
ELSE 0 END sd_xiaodan,
CASE
WHEN ( a.OnlineTime IS NULL
OR ( pro.登记时间 < a.OnlineTime
AND a.PhotographerTime < a.OnlineTime))
AND a.PhotographerTime >= @startdate
AND a.PhotographerTime < @enddate THEN 1
ELSE 0 END dj_pz,
-- --
CASE
WHEN ( a.OnlineTime IS NULL
OR ( t.分配开发时间 < a.OnlineTime
AND t.侵权回复时间 < a.OnlineTime))
AND t.侵权回复时间 >= @startdate
AND t.侵权回复时间 < @enddate THEN 1
ELSE 0 END fp_qqhf,
CASE
WHEN ( a.OnlineTime IS NULL
OR ( t.分配开发时间 < a.OnlineTime
AND a.AddTime < a.OnlineTime))
AND a.AddTime >= @startdate
AND a.AddTime < @enddate THEN 1
ELSE 0 END fp_tj,
CASE
WHEN ( a.OnlineTime IS NULL
OR ( t.分配开发时间 < a.OnlineTime
AND a.DevelopTime < a.OnlineTime))
AND a.DevelopTime >= @startdate
AND a.DevelopTime < @enddate THEN 1
ELSE 0 END fp_kf,
CASE
WHEN ( a.OnlineTime IS NULL
OR ( a.AddTime < a.OnlineTime
AND a.DevelopTime < a.OnlineTime))
AND a.DevelopTime >= @startdate
AND a.DevelopTime < @enddate THEN 1
ELSE 0 END tj_kf,
CASE
WHEN ( a.OnlineTime IS NULL
OR ( a.AddTime < a.OnlineTime
AND a.ProductDevelopmentAuditTime < a.OnlineTime))
AND a.ProductDevelopmentAuditTime >= @startdate
AND a.ProductDevelopmentAuditTime < @enddate THEN 1
ELSE 0 END tj_kfsh,
CASE
WHEN ( a.OnlineTime IS NULL
OR ( a.DevelopTime < a.OnlineTime
AND a.ProductDevelopmentAuditTime < a.OnlineTime))
AND a.ProductDevelopmentAuditTime >= @startdate
AND a.ProductDevelopmentAuditTime < @enddate THEN 1
ELSE 0 END kf_kfsh,
CASE
WHEN ( a.OnlineTime IS NULL
OR ( a.AddTime < a.OnlineTime
AND a.IntellectualPropertyRightsTime < a.OnlineTime))
AND a.IntellectualPropertyRightsTime >= @startdate
AND a.IntellectualPropertyRightsTime < @enddate THEN 1
ELSE 0 END tj_zc,
CASE WHEN ( a.OnlineTime IS NULL
OR ( a.DevelopTime < a.OnlineTime
AND a.RiskControlEnquiryTime < a.OnlineTime))
AND a.RiskControlEnquiryTime >= @startdate
AND a.RiskControlEnquiryTime < @enddate THEN 1
ELSE 0 END kf_fk,
CASE
WHEN ( a.OnlineTime IS NULL
OR ( a.DevelopTime < a.OnlineTime
AND a.PhotographerTime < a.OnlineTime))
AND a.PhotographerTime >= @startdate
AND a.PhotographerTime < @enddate THEN 1
ELSE 0 END kf_pz,
CASE
WHEN ( a.OnlineTime IS NULL
OR ( a.PhotographerTime < a.OnlineTime
AND a.ImageTime < a.OnlineTime))
AND a.ImageTime >= @startdate
AND a.ImageTime < @enddate THEN 1
ELSE 0 END pz_tp,
CASE
WHEN ( a.OnlineTime IS NULL
OR ( a.DevelopTime < a.OnlineTime
AND a.ImageTime < a.OnlineTime))
AND a.ImageTime >= @startdate
AND a.ImageTime < @enddate THEN 1
ELSE 0 END kf_tp,
CASE
WHEN ( a.OnlineTime IS NULL
OR ( a.ImageTime < a.OnlineTime
AND a.ProductPictureAuditTime < a.OnlineTime))
AND a.ProductPictureAuditTime >= @startdate
AND a.ProductPictureAuditTime < @enddate THEN 1
ELSE 0 END tp_tpsh,
CASE
WHEN ( a.OnlineTime IS NULL
OR ( a.ImageTime < a.OnlineTime
AND a.ProductPicAndTextAuditTime < a.OnlineTime))
AND a.ProductPicAndTextAuditTime >= @startdate
AND a.ProductPicAndTextAuditTime < @enddate THEN 1
ELSE 0 END tp_twsh,
CASE
WHEN ( a.OnlineTime IS NULL
OR ( a.ProductPictureAuditTime < a.OnlineTime
AND a.ProductPicAndTextAuditTime < a.OnlineTime))
AND a.ProductPicAndTextAuditTime >= @startdate
AND a.ProductPicAndTextAuditTime < @enddate THEN 1
ELSE 0 END tpsh_twsh,
CASE
WHEN ( a.OnlineTime IS NULL
OR ( a.DevelopTime < a.OnlineTime
AND a.EditTime < a.OnlineTime))
AND a.EditTime >= @startdate
AND a.EditTime < @enddate THEN 1
ELSE 0 END kf_bj,
CASE
WHEN ( a.OnlineTime IS NULL OR ( a.EditTime < a.OnlineTime AND a.ProductEditAuditTime < a.OnlineTime))
AND a.ProductEditAuditTime >= @startdate
AND a.ProductEditAuditTime < @enddate THEN 1
ELSE 0 END bj_bjsh,
CASE
WHEN ( a.OnlineTime IS NULL
OR a.EditTime < a.OnlineTime)
AND a.OnlineTime >= @startdate
AND a.OnlineTime < @enddate THEN 1
ELSE 0 END bj_sj,
CASE
WHEN ( a.OnlineTime IS NULL
OR ( a.EditTime < a.OnlineTime
AND a.ImageTime < a.OnlineTime))
AND a.ImageTime >= @startdate
AND a.ImageTime < @enddate THEN 1
ELSE 0 END bj_tp,
CASE
WHEN ( a.OnlineTime IS NULL
OR ( a.ImageTime < a.OnlineTime
AND a.ProductEditAuditTime < a.OnlineTime))
AND a.ProductEditAuditTime >= @startdate
AND a.ProductEditAuditTime < @enddate THEN 1
ELSE 0 END tp_bjsh,
CASE
WHEN ( a.OnlineTime IS NULL
OR a.DevelopTime < a.OnlineTime)
AND a.OnlineTime >= @startdate
AND a.OnlineTime < @enddate THEN 1
ELSE 0 END kf_sj,
CASE
WHEN ( a.OnlineTime IS NULL
OR ISNULL(( SELECT TOP 1 CS.AddTime
FROM RB_ProductDevelopment_Related_Product BS WITH (NOLOCK)
LEFT JOIN RB_Product_Development_Status CS WITH (NOLOCK)
ON BS.CustomerID = CS.CustomerID
AND BS.ProductDevelopmentId = CS.ProductDevelopmentId
WHERE BS.CustomerID = @CustomerID
AND SKU = a.SKU
AND CS.Status = 'Committed'
ORDER BY CS.AddTime DESC),
ISNULL(t.DevelopmentTime, a.AddTime)) < a.OnlineTime)
AND a.OnlineTime >= @startdate
AND a.OnlineTime < @enddate THEN 1
ELSE 0 END tb_sj,b.IsGroup,b.GroupSkuID
--CASE WHEN (ISNULL(spp.AddTime,t.DevelopmentTime)>a.OnlineTime or a.AddTime>a.OnlineTime or a.DevelopTime>a.OnlineTime or a.IntellectualPropertyRightsTime>a.OnlineTime or a.PhotographerTime>a.OnlineTime or
--a.ImageTime>a.OnlineTime or a.EditTime>a.OnlineTime or a.ProductDevelopmentAuditTime>a.OnlineTime or a.ProductPictureAuditTime>a.OnlineTime or a.ProductEditAuditTime>a.OnlineTime)
--THEN 1 ELSE 0 END isdelete
INTO #temp_diff2
FROM dbo.RB_Product_Admin (NOLOCK) a
INNER JOIN dbo.RB_Product (NOLOCK) b
ON b.CustomerID = a.CustomerID
AND b.SKU = a.SKU
LEFT JOIN dbo.RB_Supplier_Product (NOLOCK) sp
ON sp.CustomerId = a.CustomerID
AND sp.ItemNo = a.SKU
LEFT JOIN dbo.RB_Supplier_Product_Parent (NOLOCK) spp
ON spp.CustomerID = sp.CustomerId
AND spp.ID = sp.ParentProductId
LEFT JOIN ( SELECT SKU,
MIN(ApplicationNumber) ApplicationNumber,
MIN(DevelopmentTime) DevelopmentTime,
MAX(销售审核时间) 销售审核时间,
MAX(侵权提报时间) 侵权提报时间,
MAX(侵权回复时间) 侵权回复时间,
MAX(分配开发时间) 分配开发时间
FROM #temp_tb
WHERE ApplicationNumber IS NOT NULL
GROUP BY SKU) t
ON t.SKU = a.SKU
LEFT JOIN dbo.RB_Product_Brand (NOLOCK) pb
ON pb.CustomerID = a.CustomerID
AND pb.ID = b.BrandID
LEFT JOIN(
-- 采购下单相关表
SELECT * FROM(
SELECT
pdl.SKU,
pl.AddTime 样品单生单时间,
pds.AddTime 样品单下单时间,
e.LastBuyTime 样品单销单时间,
ppt.EnterTime 登记时间,
t1.摄影完成时间,
t1.图片处理时间,
t1.图片审核时间,
ppt.enterUserName 登记人员,
ppt.enterAdminID 登记人员ID,
RANK() OVER(PARTITION BY pdl.SKU ORDER BY pl.AddTime DESC,pds.AddTime DESC,e.LastBuyTime DESC) r
FROM dbo.RB_Procurement_Detail pdl (NOLOCK)
-- ON pdl.SKU=b.SKU AND pdl.CustomerID = b.CustomerID
LEFT JOIN dbo.RB_Procurement_List pl (NOLOCK) ON pl.P_Code = pdl.P_Code AND pdl.CustomerID = pl.CustomerID
LEFT JOIN RB_Procurement_Detail_SingleList_Detail pdsd (NOLOCK) ON pdl.CustomerID= pdsd.CustomerID AND pdsd.DetailID = pdl.ID
LEFT JOIN RB_Procurement_Detail_SingleList pds (NOLOCK) ON pds.CustomerID = pl.CustomerID AND pdsd.ListID = pds.ID
LEFT JOIN RB_Procurement_Detail_BuyList_Detail e (NOLOCK) ON pdl.CustomerID = e.CustomerID AND pdl.P_Code = e.P_Code AND pdl.SKU = e.SKU AND pdl.ID = e.DetailID AND e.SingleDetailID = pdsd.ID
LEFT JOIN RB_Product_PhotoTask(NOLOCK) ppt ON ppt.CustomerID = pdsd.CustomerID AND ppt.SKU = pdsd.SKU
LEFT JOIN
(
SELECT
SKU,
MIN(摄影完成时间) 摄影完成时间,
MIN(图片处理时间) 图片处理时间,
MIN(图片审核时间) 图片审核时间
FROM
(
SELECT
SKU,AddTime,
CASE WHEN OperationTxt like '%完成摄影任务%' THEN AddTime ELSE NULL END 摄影完成时间,
CASE WHEN OperationTxt like '%批量_非精优_半覆盖上传产品图片%' OR
OperationTxt like '%批量_非精优_新增上传产品图片%' OR
OperationTxt like '%批量_非精优_全覆盖上传产品图片%'
THEN AddTime ELSE NULL END 图片处理时间,
CASE WHEN OperationTxt like '%已通过%' THEN AddTime ELSE NULL END 图片审核时间,
OperationTxt
FROM RB_Product_Log a
WHERE
CustomerID = 1
-- AND SKU = '22898362'
AND a.AddTime>= @startdate
AND a.AddTime< @enddate
)t
GROUP BY
SKU
) t1 on pdl.SKU = t1.SKU
WHERE pl.IsSample = 1
AND pl.AddTime >=@startdate
AND pl.CustomerID=1
) a WHERE a.r = 1
) pro ON pro.SKU=b.SKU
WHERE a.CustomerID = 1
AND ISNULL(b.PictureSource, 0) >= 0
AND ISNULL(b.DevelopType, 0) >= 0
AND (b.IsGroup=0 OR( b.IsGroup = 1 AND b.GroupSkuID IS NULL))
AND ( ( a.AddTime >= @startdate
AND a.AddTime < @enddate)
OR ( a.DevelopTime >= @startdate
AND a.DevelopTime < @enddate)
OR ( a.EditTime >= @startdate
AND a.EditTime < @enddate)
OR ( a.ImageTime >= @startdate
AND a.ImageTime < @enddate)
OR ( a.OnlineTime >= @startdate
AND a.OnlineTime < @enddate)
OR ( a.ProductDevelopmentAuditTime >= @startdate
AND a.ProductDevelopmentAuditTime < @enddate)
OR ( a.ProductEditAuditTime >= @startdate
AND a.ProductEditAuditTime < @enddate)
OR ( a.ProductPictureAuditTime >= @startdate
AND a.ProductPictureAuditTime < @enddate)
OR ( a.PhotographerTime >= @startdate
AND a.PhotographerTime < @enddate)
OR ( a.IntellectualPropertyRightsTime >= @startdate
AND a.IntellectualPropertyRightsTime < @enddate)
OR ( a.RiskControlEnquiryTime >= @startdate
AND a.RiskControlEnquiryTime < @enddate)
OR ( pro.登记时间 >= @startdate
AND pro.登记时间 < @enddate)
OR ( pro.摄影完成时间 >= @startdate
AND pro.摄影完成时间 < @enddate)
OR ( pro.图片处理时间 >= @startdate
AND pro.图片处理时间 < @enddate)
OR ( pro.图片审核时间 >= @startdate
AND pro.图片审核时间 < @enddate)
OR ( a.ProductPicAndTextAuditTime >= @startdate
AND a.ProductPicAndTextAuditTime < @enddate)
--OR ( t.分配开发时间 >= @startdate
--AND t.分配开发时间 < @enddate)
)
-- AND b.DevelopType = 2
DROP TABLE IF EXISTS #temp_diff
SELECT * INTO #temp_diff FROM #temp_diff2 d WHERE d.IsGroup=0
-- 按开发类型
SEL