[步骤]
--DECLARE @CustomerID INT = 1
--DECLARE @StartTime DATETIME = '2023-09-01'
--DECLARE @EndTime DATETIME = GETDATE() -- '2023-09-30 23:59:59'
declare @StartTime datetime= '2024-04-01' ,@EndTime datetime= '2024-04-16 23:59:59' ,@CustomerId int= 1
DROP TABLE IF EXISTS #temp
SELECT -- TOP 1
CONVERT(VARCHAR(10), @StartTime, 126) AS '开始时间',
CONVERT(VARCHAR(10),@EndTime, 126) AS '结束时间',
pdbd.SKU AS 'SKU',
pdbd.ListNo 销单号,
pl.P_Code 采购单号,
CASE ISNULL(pd.NeedToPackType,p.NeedToPackType)
WHEN 0 THEN '不需打包'
WHEN 1 THEN '需供应商打包'
WHEN 2 THEN '加工打包'
WHEN 3 THEN '销单打包'
END 采购单打包状态,
b.Single_NO AS '下单号',
CASE WHEN pl.IsSample = 1 THEN '是' ELSE '否' END AS '是否样品单',
CASE p.DevelopType
WHEN 1 THEN '自主开发'
WHEN 2 THEN '供应商后台'
WHEN 3 THEN '指定开发'
WHEN 4 THEN '智能选品'
WHEN 5 THEN '销售提报_FBA新品'
WHEN 6 THEN '智选转FBA提报'
END AS 开发类型,
wh.WareHouseName AS '仓库名称',
pdbd.SupplierName AS '供应商名称',
ad.UserName AS '采购员',
dp.DepartmentName AS '组别',
ad2.UserName AS '开发员',
dp2.DepartmentName AS '小组',
CASE WHEN ISNULL(pdbd.NeedToPackType,p.NeedToPackType) IN (2,3) THEN '是' ELSE '否' END '是否打包',
CASE ISNULL(pdbd.NeedToPackType,p.NeedToPackType)
WHEN 0 THEN '不需打包'
WHEN 1 THEN '需供应商打包'
WHEN 2 THEN '加工打包'
WHEN 3 THEN '销单打包'
END 销单打包状态,
pa.OnlineTime AS '上架时间',
pdbd.LastBuyTime AS '销单时间',
DATEDIFF(d,pa.OnlineTime,pdbd.LastBuyTime) AS '上架天数',
CASE WHEN DATEDIFF(d,pa.OnlineTime,pdbd.LastBuyTime) <=365 THEN '是' ELSE '否' END AS '是否一年内终审',
CASE WHEN ISNULL(pdbd.NeedToPackType,p.NeedToPackType) IN (2,3) THEN pdbd.ActualBuyNum ELSE 0 END AS '打包产品数',
pdbd.ActualBuyNum AS '销单产品数',
psa.SpecIdQuantity '1688链接匹配数量',
CAST((CASE WHEN ISNULL(pdbd.NeedToPackType,p.NeedToPackType) IN (2,3) THEN pdbd.ActualBuyNum ELSE 0 END) AS FLOAT)*CAST(ISNULL(psa.SpecIdQuantity,1) AS float) AS '打包产品数(匹配链接数量)',
CAST(pdbd.ActualBuyNum AS FLOAT)*CAST(ISNULL(psa.SpecIdQuantity,1) AS FLOAT) AS '销单产品数(匹配链接数量)',
(CASE WHEN ISNULL(pdbd.NeedToPackType,p.NeedToPackType) IN (2,3) THEN pdbd.ActualBuyNum ELSE 0 END)*1.0 / pdbd.ActualBuyNum as '产品数量占比',
CONVERT(VARCHAR(7),pdbd.LastBuyTime,20) AS '月份'
INTO #temp
FROM RB_Procurement_Detail_BuyList_Detail (nolock) pdbd
INNER JOIN RB_Product (nolock) p on pdbd.CustomerID = p.CustomerID and pdbd.SKU = p.SKU
LEFT JOIN RB_Procurement_Detail_SingleList_Detail(NOLOCK) AS a ON pdbd.CustomerID = a.CustomerID AND pdbd.SingleDetailID = a.ID
LEFT JOIN RB_Procurement_Detail_SingleList(NOLOCK) AS b ON pdbd.CustomerID = b.CustomerID AND a.ListID = b.ID
INNER JOIN RB_Procurement_List (nolock) pl on pdbd.CustomerID = pl.CustomerID and pdbd.P_Code = pl.P_Code
LEFT JOIN dbo.RB_Procurement_Detail(NOLOCK) pd ON pd.CustomerID = pl.CustomerID and pd.P_Code = pl.P_Code AND a.DetailID=pd.ID
LEFT JOIN RB_WareHouse (nolock) wh on pl.CustomerID = wh.CustomerID AND pl.WareHouseID = wh.ID
--LEFT JOIN RB_Procurement_PackageList (nolock) ppl on pdbd.CustomerID = ppl.CustomerID and pdbd.ListNo = ppl.ListNo and pdbd.SKU = ppl.SKU
LEFT JOIN RB_Product_Admin (nolock) pa ON p.CustomerID = pa.CustomerID AND pa.SKU = p.SKU
LEFT JOIN RB_Admin (NOLOCK)ad ON pl.BuyerID = ad.ID AND ad.CustomerID = pl.CustomerID
LEFT JOIN dbo.RB_Department (NOLOCK)dp ON dp.ID = ad.DepartmentID AND dp.CustomerID = ad.CustomerID
LEFT JOIN RB_Admin (NOLOCK)ad2 ON pa.DevelopAdminID = ad2.ID AND ad2.CustomerID = pa.CustomerID
LEFT JOIN dbo.RB_Department (NOLOCK)dp2 ON dp2.ID = ad2.DepartmentID AND dp2.CustomerID = ad2.CustomerID
LEFT JOIN dbo.RB_Product_SupplierPrice(NOLOCK) ps ON ps.SKU = p.SKU AND ps.CustomerID = p.CustomerID AND ps.IsDefault = 1
LEFT JOIN dbo.RB_Product_Supplier_1688_ProductSkuSpecId (NOLOCK)psa ON psa.CustomerId = ps.CustomerID AND psa.SupplierId = ps.SupplierID AND psa.SKU = ps.SKU
WHERE
pdbd.CustomerID = @CustomerID
and pdbd.LastBuyTime >= @StartTime
and pdbd.LastBuyTime <= @EndTime
AND ISNULL(p.ProductType, 0) = 0
--AND b.Single_NO='12307589'
--SELECT * FROM #temp
DROP TABLE IF EXISTS #temp_detail
SELECT
t.开始时间,
t.结束时间,
t.月份,
t.SKU,
t.销单号,
t.采购单号,
t.采购单打包状态,
t.下单号,
t.是否样品单,
t.开发类型,
t.仓库名称,
t.供应商名称,
SUM(t.[1688链接匹配数量]) '1688链接匹配数量',
t.采购员,
t.组别,
t.开发员,
t.小组,
t.是否打包,
t.销单打包状态,
t.上架时间,
t.销单时间,
t.上架天数,
t.是否一年内终审,
SUM(t.[打包产品数(匹配链接数量)]) '打包产品数(匹配链接数量)',
SUM(t.[销单产品数(匹配链接数量)]) '销单产品数(匹配链接数量)',
t.产品数量占比,
t.打包产品数,
t.销单产品数
INTO #temp_detail
FROM #temp AS t
GROUP BY
t.开始时间,
t.结束时间,
t.月份,
t.SKU,
t.销单号,
t.采购单号,
t.采购单打包状态,
t.下单号,
t.是否样品单,
t.开发类型,
t.仓库名称,
t.供应商名称,
t.采购员,
t.组别,
t.开发员,
t.小组,
t.是否打包,
t.销单打包状态,
t.上架时间,
t.销单时间,
t.上架天数,
t.是否一年内终审,
t.产品数量占比,
t.打包产品数,
t.销单产品数
SELECT * FROM #temp_detail
-- 开发人员匹配链接数量汇总表
SELECT
t.月份,
t.开发员,
t.小组 AS '组别',
SUM(t.[打包产品数(匹配链接数量)]) AS '打包产品数',
SUM(t.[销单产品数(匹配链接数量)]) AS '销单产品数',
CONCAT(CONVERT(VARCHAR(10),CONVERT(DECIMAL(18,2),SUM(t.[打包产品数(匹配链接数量)])*1.0/SUM(t.[销单产品数(匹配链接数量)])*100)),'%') 产品数量占比
FROM #temp_detail AS t
WHERE t.上架天数 <= 365 AND t.是否样品单 = '否'
group BY
t.月份,
t.开发员,
t.小组;
-- 采购人员匹配链接数量汇总表
SELECT
t.月份,
t.采购员,
t.组别,
SUM(t.[打包产品数(匹配链接数量)]) AS '打包产品数',
SUM(t.[销单产品数(匹配链接数量)]) AS '销单产品数',
CONCAT(CONVERT(VARCHAR(10),CONVERT(DECIMAL(18,2),SUM(t.[打包产品数(匹配链接数量)])*1.0/SUM(t.[销单产品数(匹配链接数量)])*100)),'%') 产品数量占比
from #temp_detail AS t WHERE t.是否样品单 = '否'
group BY
t.月份,
t.采购员,
t.组别;
-- 开发人员汇总
SELECT
t.月份,
t.开发员,
t.小组 AS '组别',
SUM(t.打包产品数) AS '打包产品数',
SUM(t.销单产品数) AS '销单产品数',
CONCAT(CONVERT(VARCHAR(10),CONVERT(DECIMAL(18,2),SUM(t.打包产品数)*1.0/SUM(t.销单产品数)*100)),'%') 产品数量占比
FROM #temp_detail AS t
WHERE t.上架天数 <= 365 AND t.是否样品单 = '否'
group BY
t.月份,
t.开发员,
t.小组;
-- 采购人员汇总
SELECT
t.月份,
t.采购员,
t.组别,
SUM(t.打包产品数) AS '打包产品数',
SUM(t.销单产品数) AS '销单产品数',
CONCAT(CONVERT(VARCHAR(10),CONVERT(DECIMAL(18,2),SUM(t.打包产品数)*1.0/SUM(t.销单产品数)*100)),'%') 产品数量占比
from #temp_detail AS t WHERE t.是否样品单 = '否'
group BY
t.月份,
t.采购员,
t.组别;
[结果]
[期望]
修复 异常
