[步骤]
select top 2000* from RB_CustomReportTask where id=190627
190627
1
979
供应链产品质量V2
NULL
2
NULL
{"@StartTime":"2025-11-01 00:00:00","@EndTime":"2025-11-30 23:59:59","@FinalEffectiveTime":"90"}
导出报表【979】失败。失败原因：在从服务器接收结果时发生传输级错误。 (provider: TCP Provider, error: 0 - 远程主机强迫关闭了一个现有的连接。)
NULL
0
2025-12-17 13:41:57.830
NULL
NULL
10482
李佳玲
2025-12-16 16:41:01.593
NULL
/* 报表名称：供应链产品质量 需求人：邓巧欢 修改人：余效檀 时间：20241206 */ -- DECLARE @StartTime datetime= '2025-03-01 00:00:00' ,@EndTime datetime= '2025-03-31 23:59:59' ,@CustomerId int= 1 ,@FinalEffectiveTime int= 18 DROP TABLE IF EXISTS #t CREATE TABLE #t ( CustomerID INT, T DATETIME) DECLARE @starttime1 DATETIME,@endtime1 DATETIME SET @starttime1 = DATEADD(MONTH,1,@StartTime) SET @endtime1 =DATEADD(MONTH,1,@EndTime) WHILE @starttime1 <= @endtime1 BEGIN INSERT #t VALUES(1, @starttime1) SET @starttime1 = DATEADD(MONTH, 1, @starttime1) END -- SELECT * FROM #t ------------ sku明细 DROP TABLE IF EXISTS #sku SELECT a.T, b.SKU, CASE WHEN c.IsGroup=1 THEN '是' ELSE '否' END 是否组合SKU, CASE WHEN LEN(br.BrowseNodeName)-LEN(REPLACE(br.BrowseNodeName,'>>',''))=0 THEN br.BrowseNodeName ELSE LEFT(br.BrowseNodeName,CHARINDEX('>>',br.BrowseNodeName)-1) END 平台一级类目, CASE WHEN LEN(br.BrowseNodeName)-LEN(REPLACE(br.BrowseNodeName,'>>',''))=0 THEN '' WHEN LEN(br.BrowseNodeName)-LEN(REPLACE(br.BrowseNodeName,'>>',''))=2 THEN RIGHT(br.BrowseNodeName,LEN(br.BrowseNodeName)-CHARINDEX('>>',br.BrowseNodeName)-1) ELSE SUBSTRING( br.BrowseNodeName, CHARINDEX('>>',br.BrowseNodeName)+2, CHARINDEX('>>',br.BrowseNodeName,CHARINDEX('>>',br.BrowseNodeName)+2)-CHARINDEX('>>',br.BrowseNodeName)-2) END 平台二级类目, CASE WHEN c.LastSupplierPrice > 0 THEN c.LastSupplierPrice ELSE c.LastBuyPrice END 采购价, c.MinSaleNum 起批量, c.GrossWeight 毛重, c.AccurateKeyword 中文精准关键词, c.PreciseCategoryWords 精准类目词, CASE c.DevelopType WHEN 1 THEN '自主开发' WHEN 2 THEN '供应商后台' WHEN 3 THEN '指定开发' WHEN 4 THEN '智能选品' WHEN 5 THEN '销售提报_FBA新品' END AS 开发类型, CASE c.PictureSource WHEN 1 THEN '采集图' WHEN 2 THEN '供应商提供图' WHEN 3 THEN '自己拍照图' END AS '图片来源', CASE c.ProductState WHEN 0 THEN '正常' WHEN 1 THEN '起批量' WHEN 2 THEN '停产' WHEN 3 THEN '锁定' WHEN 4 THEN '暂时缺货' WHEN 5 THEN '清库' END AS '产品状态', CASE WHEN b.HasImitationCountry=2 AND c.ProductProperty=2 THEN '部分国家仿牌' ELSE CASE c.ProductProperty WHEN 0 THEN '非仿牌' WHEN 1 THEN '非仿牌' WHEN 2 THEN '仿牌' WHEN 3 THEN '灰色产品' WHEN 4 THEN '疑似仿牌' END END AS '产品侵权风险', pl.LineName AS '产品线', b.DevelopAdminName AS '开发人员', f.DepartmentName AS '开发组别', b.IntellectualPropertyRightsAdminName AS '知产人员', f5.DepartmentName AS '知产小组', b.BuyerName AS '采购人员', b.ImageAdminName AS '图片处理人员', f3.DepartmentName AS '美工组别', b.PhotographerName AS '摄影人员', f4.DepartmentName AS '摄影组别', b.EditAdminName AS '产品编辑人员', f2.DepartmentName AS '编辑组别', b.OnlineTime AS '上架时间', b.DevelopAdminID, b.BuyerID INTO #sku FROM #t AS a LEFT JOIN dbo.RB_Product_Admin(NOLOCK) AS b ON a.CustomerID = b.CustomerID LEFT JOIN (SELECT SKU,ProductLineID FROM rb_product_extendinfo(NOLOCK) WHERE CustomerID=1) pe ON b.SKU=pe.SKU LEFT JOIN (SELECT ID,LineName FROM rb_product_productline(NOLOCK) WHERE CustomerID=1) pl ON pe.ProductLineID=pl.ID LEFT JOIN dbo.RB_Product(NOLOCK) AS c ON b.CustomerID = c.CustomerID AND b.SKU = c.SKU LEFT JOIN dbo.RB_Admin(NOLOCK) AS e ON b.CustomerID = e.CustomerID AND b.DevelopAdminID = e.ID LEFT JOIN (SELECT ID, CustomerID,UserName,DepartmentID,AddTime,EmpoyeeNumber,ROW_NUMBER() OVER(PARTITION BY UserName ORDER BY AddTime) n FROM dbo.RB_Admin WHERE CustomerID=1) a2 ON b.DevelopAdminName = a2.UserName AND a2.n=1 LEFT JOIN dbo.RB_Department(NOLOCK) f ON ISNULL(e.CustomerID,a2.CustomerID) = f.CustomerID AND ISNULL(e.DepartmentID,a2.DepartmentID) = f.ID LEFT JOIN dbo.RB_Admin(NOLOCK) AS e2 ON b.CustomerID = e2.CustomerID AND b.EditAdminID = e2.ID LEFT JOIN dbo.RB_Department(NOLOCK) AS f2 ON b.CustomerID = f2.CustomerID AND e2.DepartmentID = f2.ID LEFT JOIN dbo.RB_Admin(NOLOCK) AS e3 ON b.CustomerID = e3.CustomerID AND b.ImageAdminID = e3.ID LEFT JOIN dbo.RB_Department(NOLOCK) AS f3 ON b.CustomerID = f3.CustomerID AND e3.DepartmentID = f3.ID LEFT JOIN dbo.RB_Admin(NOLOCK) AS e4 ON b.CustomerID = e4.CustomerID AND b.PhotographerId = e4.ID LEFT JOIN dbo.RB_Department(NOLOCK) AS f4 ON b.CustomerID = f4.CustomerID AND e4.DepartmentID = f4.ID LEFT JOIN dbo.RB_Admin(NOLOCK) AS e5 ON b.CustomerID = e5.CustomerID AND b.IntellectualPropertyRightsAdminID = e5.ID LEFT JOIN dbo.RB_Department(NOLOCK) AS f5 ON b.CustomerID = f5.CustomerID AND e5.DepartmentID = f5.ID LEFT JOIN (SELECT SKU,BrowseNodeName,ROW_NUMBER() OVER(PARTITION BY SKU ORDER BY ID DESC) r FROM dbo.E_Product_Amazon_BrowseTree (NOLOCK) WHERE IsMain = 1 AND [IsDelete]=0 AND CustomerID=1) br ON br.SKU = c.SKU AND br.r=1 WHERE b.CustomerID = @CustomerID AND b.OnlineTime >= DATEADD(MONTH,-@FinalEffectiveTime,a.T) AND b.OnlineTime < a.T -- 日期前6个月上架的sku产品数据 DROP TABLE IF EXISTS #ros SELECT a.T, a.SKU, a.PayTime, a.ShipDate ShipTime, SUM(a.ProductNum) ProductNum, SUM(a.[ProdutNumUpdate]) [ProdutNumUpdate], SUM(ISNULL(a.SalesPrice,0)+ISNULL(a.TransportPay,0)+ISNULL(a.SalesRefunds,0)+ISNULL(a.PromotionRebates,0)+ISNULL(a.OtherCredits,0)+ISNULL(Tax,0)+ISNULL(MarketplaceFacilitatorTax,0)) Incomes, SUM(ISNULL(a.SalesPrice,0) +ISNULL(a.TransportPay,0) +ISNULL(a.SalesRefunds,0) +ISNULL(a.PromotionRebates,0) +ISNULL(a.OtherCredits,0) +ISNULL(Tax,0)+ISNULL(MarketplaceFacilitatorTax,0) +ISNULL(CommisionFee,0)+ISNULL(AffiliatePartnerCommissionFee,0) +ISNULL(a.FBATransactionFees,0) +ISNULL(a.OrderSourceOtherDebits,0) +ISNULL(a.CostOfAdvertising,0) +ISNULL(BuyPrice,0)+ISNULL(TaxRefundFee,0)+ISNULL(DistributionPrice,0) +ISNULL(a.PackagedCost,0) +ISNULL(a.SubsidyPrice,0) +ISNULL(a.PackageFees,0) +ISNULL(a.OverSeasShipFees,0) +ISNULL(a.FirstLegFees,0) +ISNULL(a.WareHouseManagementPrice,0) +ISNULL(a.SalesVatFee,0) +ISNULL(a.TariffFees,0) +ISNULL(a.ExchangeLost,0) +ISNULL(a.CustomStorageFee,0) +ISNULL(a.ImportFee,0) +ISNULL(a.BreakageFee,0) +ISNULL(a.FirstLegBreakageFee,0) ) Profits INTO #ros FROM (SELECT T,PayTime,OrderCode,CASE WHEN PromotionType = 1 AND OrderSourceType =1 THEN SettlementShortDate ELSE ShipDate END ShipDate,OrderSourceTypeName,OrderSourceName,SKU,ClientSKU,ProductNum ,[ProdutNumUpdate],Profits,Incomes,Expenses,SalesPrice,TransportPay,SalesRefunds,PromotionRebates,OtherCredits,CommisionFee,FBATransactionFees,OrderSourceOtherDebits ,CASE WHEN OrderSourceType<>1 THEN CostOfAdvertising END CostOfAdvertising,BuyPrice,SubsidyPrice,SalesVatFee,PackageFees,OverSeasShipFees,FirstLegFees,TariffFees,ExchangeLost ,PackagedCost,WareHouseManagementPrice,CustomFeeOtherCostPrice,Tax,MarketplaceFacilitatorTax,CustomStorageFee,ImportFee,BreakageFee,FirstLegBreakageFee,AffiliatePartnerCommissionFee ,TaxRefundFee,DistributionPrice FROM #t AS t LEFT JOIN RB_Report_OrderSettlement (nolock) os1 ON t.CustomerID = @CustomerID WHERE os1.CustomerID=1 AND (OrderSourceType <> 67 OR (OrderSourceType = 67 and OrderCode <> '') OR (OrderSourceType = 67 and OrderCode = '' AND CHARINDEX('其他成本', OrderDescription) = 0)) AND CASE WHEN PromotionType = 1 AND OrderSourceType =1 THEN SettlementShortDate ELSE ShipDate END>= DATEADD(MONTH,-1,t.T) AND CASE WHEN PromotionType = 1 AND OrderSourceType =1 THEN SettlementShortDate ELSE ShipDate END<t.T AND SKU <> '' AND OrderCode <>'' UNION ALL SELECT T,PayTime,OrderCode,CASE WHEN PromotionType = 1 AND OrderSourceType =1 THEN SettlementShortDate ELSE ShipDate END ShipDate,OrderSourceTypeName,OrderSourceName,SKU,ClientSKU,ProductNum,[ProdutNumUpdate] ,Profits,Incomes,Expenses,SalesPrice,TransportPay,SalesRefunds,PromotionRebates,OtherCredits,CommisionFee,FBATransactionFees,OrderSourceOtherDebits ,CASE WHEN OrderSourceType<>1 THEN CostOfAdvertising END CostOfAdvertising,BuyPrice,SubsidyPrice,SalesVatFee,PackageFees,OverSeasShipFees,FirstLegFees,TariffFees,ExchangeLost,PackagedCost ,WareHouseManagementPrice,CustomFeeOtherCostPrice,Tax,MarketplaceFacilitatorTax,CustomStorageFee,ImportFee,BreakageFee,FirstLegBreakageFee,AffiliatePartnerCommissionFee,TaxRefundFee ,DistributionPrice FROM #t AS t LEFT JOIN RB_Report_OrderSettlement (nolock) os2 ON t.CustomerID = @CustomerID WHERE os2.CustomerID=1 AND (OrderSourceType = 67 and OrderCode = '' AND CHARINDEX('其他成本', OrderDescription) > 0) AND CASE WHEN PromotionType = 1 AND OrderSourceType =1 THEN SettlementShortDate ELSE ShipDate END>=DATEADD(MONTH,-1,t.T) AND CASE WHEN PromotionType = 1 AND OrderSourceType =1 THEN SettlementShortDate ELSE ShipDate END<t.T AND SKU <> '' AND OrderCode <>'' UNION ALL SELECT T,NULL PayTime,'1' OrderCode,ShipDate,OrderSourceTypeName,OrderSourceName,ISNULL(b.SKU,a.SKU),NULL ClientSKU,0 ProductNum,0 [ProdutNumUpdate],CostOfAdvertising/CASE WHEN b.cnt IS NULL THEN 1 ELSE cnt END Profits ,0 Incomes,0 Expenses,0 SalesPrice,0 TransportPay,0 SalesRefunds,0 PromotionRebates,0 OtherCredits,0 CommisionFee,0 FBATransactionFees,0 OrderSourceOtherDebits,CostOfAdvertising/CASE WHEN b.cnt IS NULL THEN 1 ELSE cnt END CostOfAdvertising ,0 BuyPrice,0 SubsidyPrice,0 SalesVatFee,0 PackageFees,0 OverSeasShipFees,0 FirstLegFees,0 TariffFees,0 ExchangeLost,0 PackagedCost,0 WareHouseManagementPrice,0 CustomFeeOtherCostPrice,0 Tax ,0 MarketplaceFacilitatorTax,0 CustomStorageFee,0 ImportFee,0 BreakageFee,0 FirstLegBreakageFee,0 AffiliatePartnerCommissionFee,0 TaxRefundFee,0 DistributionPrice FROM (SELECT t.T,os3.CustomerID,SettlementDate ShipDate,OrderSourceTypeName,OrderSourceName,SKU,ExchangeRMB*AdCost CostOfAdvertising FROM #t AS t LEFT JOIN RB_Report_OrderSettlement_AdCost_Share (nolock) os3 ON t.CustomerID = @CustomerID WHERE os3.CustomerID=1 AND IsBuildProfit=1 AND SettlementDate>=DATEADD(MONTH,-1,t.T) AND SettlementDate<t.T AND SKU <> '' ) a LEFT JOIN (SELECT GroupSKU,SKU,COUNT(SKU) OVER(PARTITION BY GroupSKU) cnt FROM ( SELECT GroupSKU,SKU FROM rb_orderlist(nolock) WHERE CustomerID=1 AND ISNULL(GroupSKU,'')<>'' GROUP BY GroupSKU,SKU) A) b ON a.SKU=b.GroupSKU) a GROUP BY a.T ,a.SKU, a.PayTime ,a.ShipDate -- DROP TABLE IF EXISTS #ros -- SELECT -- t.T, -- a.SKU, -- -- a.DevelopAdminName, -- a.PayTime, -- a.ProductNum, -- a.[ProdutNumUpdate], -- a.Incomes, -- a.Profits -- INTO #ros -- FROM #t AS t -- LEFT JOIN #orderSettlement AS a ON t.CustomerID = @CustomerID -- WHERE -- a.ShipTime >= DATEADD(MONTH,-1,t.T) -- AND a.ShipTime < t.T -- 上一个月的销售情况，发货时间维度 -- AND a.SKU != '' SELECT a.T, a.SKU, -- a.DevelopAdminName '开发人员', MIN(a.PayTime) AS '最早订单付款时间', MAX(a.PayTime) AS '最后订单付款时间', SUM(a.ProductNum + a.[ProdutNumUpdate]) AS '发货订单总数', SUM(a.Incomes) AS '总销售额', SUM(a.Profits) AS '总利润' INTO #sale FROM #ros AS a INNER JOIN #sku AS e ON a.SKU = e.SKU AND e.T = a.T WHERE a.SKU != '' -- AND e.上架时间 >= DATEADD(MONTH,-6,a.T) -- AND e.上架时间 < a.T GROUP BY a.T, a.SKU -- a.DevelopAdminName DROP TABLE IF EXISTS #mx SELECT DATEADD(MONTH,-1,a.T) AS '日期', a.SKU, a.是否组合SKU, a.平台一级类目, a.平台二级类目, a.采购价, a.起批量, a.毛重, a.中文精准关键词, a.精准类目词, a.开发类型, a.图片来源, a.产品状态, a.产品侵权风险, a.开发人员, a.产品线, a.开发组别, a.知产人员, a.知产小组, a.采购人员, a.图片处理人员, a.美工组别, a.摄影人员, a.摄影组别, a.产品编辑人员, a.编辑组别, a.上架时间, b.最早订单付款时间, b.最后订单付款时间, b.发货订单总数, b.总销售额, b.总利润, c.默认供应商, c.账期类型 INTO #mx FROM #sale AS b RIGHT JOIN #sku AS a ON a.T = b.T AND a.SKU = b.SKU LEFT JOIN ( SELECT DISTINCT SKU,s.SupplierName 默认供应商,b.PaymentName 账期类型 FROM rb_product_supplierprice (NOLOCK) p INNER JOIN rb_product_supplier (NOLOCK) s ON p.SupplierID = s.ID AND p.CustomerID = s.CustomerID LEFT JOIN rb_product_supplier_paymentday(NOLOCK) AS b ON s.PaymentDayNew = b.ID WHERE p.CustomerID = 1 -- AND SKU = '20289229' AND IsNew = 1 AND IsDeleted=0 AND IsDefault = 1 ) c ON a.SKU = c.SKU WHERE a.T = DATEADD(DAY,-DAY(@endtime1)+1,CAST(@endtime1 AS DATE)) SELECT * FROM #mx ORDER BY 日期 SELECT DATEADD(MONTH,-1,a.T) AS '日期', a.开发人员, a.开发组别, SUM(b.总利润) AS '半年终审产品毛利', COUNT(DISTINCT a.SKU) AS '半年终审sku数', SUM(b.总利润)/COUNT(DISTINCT a.SKU) AS 'sku单位产出' FROM #sku AS a LEFT JOIN #sale AS b ON a.SKU = b.SKU AND a.T = b.T WHERE 是否组合SKU='否' GROUP BY DATEADD(MONTH,-1,a.T), a.开发人员, a.开发组别 SELECT DATEADD(MONTH,-1,a.T) AS '日期', a.产品编辑人员, a.编辑组别, SUM(b.总利润) AS '半年终审产品毛利', COUNT(DISTINCT a.SKU) AS '半年终审sku数', SUM(b.总利润)/COUNT(DISTINCT a.SKU) AS 'sku单位产出' FROM #sku AS a LEFT JOIN #sale AS b ON a.SKU = b.SKU AND a.T = b.T WHERE 是否组合SKU='否' GROUP BY DATEADD(MONTH,-1,a.T), a.产品编辑人员, a.编辑组别 SELECT DATEADD(MONTH,-1,a.T) AS '日期', a.图片处理人员, a.美工组别, SUM(b.总利润) AS '半年终审产品毛利', COUNT(DISTINCT a.SKU) AS '半年终审sku数', SUM(b.总利润)/COUNT(DISTINCT a.SKU) AS 'sku单位产出' FROM #sku AS a LEFT JOIN #sale AS b ON a.SKU = b.SKU AND a.T = b.T WHERE 是否组合SKU='否' GROUP BY DATEADD(MONTH,-1,a.T), a.图片处理人员, a.美工组别 SELECT DATEADD(MONTH,-1,a.T) AS '日期', a.摄影人员, a.摄影组别, SUM(b.总利润) AS '半年终审产品毛利', COUNT(DISTINCT a.SKU) AS '半年终审sku数', SUM(b.总利润)/COUNT(DISTINCT a.SKU) AS 'sku单位产出' FROM #sku AS a LEFT JOIN #sale AS b ON a.SKU = b.SKU AND a.T = b.T WHERE 是否组合SKU='否' GROUP BY DATEADD(MONTH,-1,a.T), a.摄影人员, a.摄影组别 SELECT DATEADD(MONTH,-1,a.T) AS '日期', a.知产人员, a.知产小组, SUM(b.总利润) AS '半年终审产品毛利', COUNT(DISTINCT a.SKU) AS '半年终审sku数', SUM(b.总利润)/COUNT(DISTINCT a.SKU) AS 'sku单位产出' FROM #sku AS a LEFT JOIN #sale AS b ON a.SKU = b.SKU AND a.T = b.T WHERE 是否组合SKU='否' GROUP BY DATEADD(MONTH,-1,a.T), a.知产人员, a.知产小组 SELECT DATEADD(MONTH,-1,a.T) AS '日期', a.平台一级类目, SUM(b.总利润) AS '半年终审产品毛利', COUNT(DISTINCT a.SKU) AS '半年终审sku数', SUM(b.总利润)/COUNT(DISTINCT a.SKU) AS 'sku单位产出' FROM #sku AS a LEFT JOIN #sale AS b ON a.SKU = b.SKU AND a.T = b.T WHERE {{ a.DevelopAdminID IN (@DevelopAdmindp) }} AND {{ a.BuyerID IN (@Buyerdp) }} AND 是否组合SKU='否' GROUP BY DATEADD(MONTH,-1,a.T), a.平台一级类目 --开发汇总（不限终审时间） SELECT DATEADD(MONTH,-1,a.T) AS '日期', e.UserName '开发人员', f.DepartmentName AS '开发组别', SUM(g.Profits) '所有产品终审毛利', SUM(g.Incomes) '所有产品终审销售额', COUNT(DISTINCT b.SKU) AS '终审sku数', SUM(g.Profits)/COUNT(DISTINCT b.SKU) AS 'sku单位产出', SUM(CASE WHEN b.OnlineTime>=DATEADD(MONTH,-12,a.T) THEN g.Profits END) '1年实际总利润', SUM(CASE WHEN b.OnlineTime>=DATEADD(MONTH,-12,a.T) THEN g.Profits END)/CASE WHEN SUM(g.Profits)<>0 THEN SUM(g.Profits) END '1年产品毛利比率', SUM(CASE WHEN b.OnlineTime>=DATEADD(MONTH,-12,a.T) THEN g.Incomes END) '1年实际总销售额', SUM(CASE WHEN b.OnlineTime>=DATEADD(MONTH,-12,a.T) THEN g.Incomes END)/CASE WHEN SUM(g.Incomes)<>0 THEN SUM(g.Incomes) END '1年产品销售额比率' FROM #t AS a LEFT JOIN dbo.RB_Product_Admin(NOLOCK) AS b ON a.CustomerID = b.CustomerID LEFT JOIN dbo.RB_Product(NOLOCK) AS c ON b.CustomerID = c.CustomerID AND b.SKU = c.SKU LEFT JOIN dbo.RB_Admin(NOLOCK) AS e ON b.CustomerID = e.CustomerID AND b.DevelopAdminID = e.ID LEFT JOIN dbo.RB_Department(NOLOCK) AS f ON b.CustomerID = f.CustomerID AND e.DepartmentID = f.ID LEFT JOIN (SELECT T,SUM(Profits) Profits,SKU,SUM(Incomes) Incomes FROM #ros GROUP BY T,SKU) AS g ON b.SKU = g.SKU AND a.T = g.T WHERE b.CustomerID = @CustomerID AND a.T > b.OnlineTime AND c.IsGroup != 1 -- 非组合产品 GROUP BY DATEADD(MONTH,-1,a.T),e.UserName,f.DepartmentName
NULL
0
[结果]
[期望]
优化查询 提升性能
这个是余效檀这边开发的报表