[步骤]
DROP TABLE IF EXISTS #temp_OrderSourceType SELECT a.OrderSourceType, a.OrderSourceTypeName INTO #temp_OrderSourceType FROM( SELECT 1 OrderSourceType , '亚马逊平台' OrderSourceTypeName UNION SELECT 2 OrderSourceType , 'Ebay平台' OrderSourceTypeName UNION SELECT 3 OrderSourceType , '团购平台' OrderSourceTypeName UNION SELECT 4 OrderSourceType , 'B2C网站平台' OrderSourceTypeName UNION SELECT 5 OrderSourceType , '赠送订单' OrderSourceTypeName UNION SELECT 6 OrderSourceType , '枪手订单' OrderSourceTypeName UNION SELECT 7 OrderSourceType , '问题订单重发' OrderSourceTypeName UNION SELECT 8 OrderSourceType , '问题订单补发' OrderSourceTypeName UNION SELECT 9 OrderSourceType , '代发货渠道' OrderSourceTypeName UNION SELECT 10 OrderSourceType , '亚马逊FBA' OrderSourceTypeName UNION SELECT 19 OrderSourceType , '其他第三方渠道' OrderSourceTypeName UNION SELECT 20 OrderSourceType , '本地仓库备库订单' OrderSourceTypeName UNION SELECT 21 OrderSourceType , '海外仓库备库订单' OrderSourceTypeName UNION SELECT 22 OrderSourceType , '亚马逊仓库备库订单' OrderSourceTypeName UNION SELECT 23 OrderSourceType , 'Wish平台' OrderSourceTypeName UNION SELECT 24 OrderSourceType , '共享平台' OrderSourceTypeName UNION SELECT 25 OrderSourceType , '速卖通平台' OrderSourceTypeName UNION SELECT 26 OrderSourceType , '麦进斗平台' OrderSourceTypeName UNION SELECT 27 OrderSourceType , '敦煌平台' OrderSourceTypeName UNION SELECT 28 OrderSourceType , 'PayPal交易平台' OrderSourceTypeName UNION SELECT 29 OrderSourceType , '新蛋平台' OrderSourceTypeName UNION SELECT 30 OrderSourceType , 'LetsEcig平台' OrderSourceTypeName UNION SELECT 31 OrderSourceType , 'Meetgift平台' OrderSourceTypeName UNION SELECT 32 OrderSourceType , 'Qoo10平台' OrderSourceTypeName UNION SELECT 33 OrderSourceType , 'Lazada平台' OrderSourceTypeName UNION SELECT 34 OrderSourceType , 'Alibaba平台' OrderSourceTypeName UNION SELECT 35 OrderSourceType , 'Cdiscount平台' OrderSourceTypeName UNION SELECT 36 OrderSourceType , 'Linio平台' OrderSourceTypeName UNION SELECT 37 OrderSourceType , 'Bellabuy平台' OrderSourceTypeName UNION SELECT 38 OrderSourceType , '京东平台' OrderSourceTypeName UNION SELECT 39 OrderSourceType , 'Tophatter平台' OrderSourceTypeName UNION SELECT 40 OrderSourceType , 'PriceMinister平台' OrderSourceTypeName UNION SELECT 41 OrderSourceType , 'Mercadolibre平台' OrderSourceTypeName UNION SELECT 42 OrderSourceType , 'Shopee平台' OrderSourceTypeName UNION SELECT 43 OrderSourceType , 'Kamvio平台' OrderSourceTypeName UNION SELECT 44 OrderSourceType , 'Amazonvendorexpress平台' OrderSourceTypeName UNION SELECT 45 OrderSourceType , 'Walmart平台' OrderSourceTypeName UNION SELECT 46 OrderSourceType , 'Deal平台' OrderSourceTypeName UNION SELECT 47 OrderSourceType , 'Jumia平台' OrderSourceTypeName UNION SELECT 48 OrderSourceType , 'Jet平台' OrderSourceTypeName UNION SELECT 49 OrderSourceType , 'Sears平台' OrderSourceTypeName UNION SELECT 50 OrderSourceType , 'Shopify平台' OrderSourceTypeName UNION SELECT 51 OrderSourceType , 'Joom平台' OrderSourceTypeName UNION SELECT 52 OrderSourceType , 'MyMall平台' OrderSourceTypeName UNION SELECT 53 OrderSourceType , 'FactoryMarket平台' OrderSourceTypeName UNION SELECT 54 OrderSourceType , 'Fyndiq平台' OrderSourceTypeName UNION SELECT 55 OrderSourceType , '京东国内平台' OrderSourceTypeName UNION SELECT 56 OrderSourceType , 'Oberlo平台' OrderSourceTypeName UNION SELECT 57 OrderSourceType , '速卖通V2平台' OrderSourceTypeName UNION SELECT 58 OrderSourceType , 'LazadaV2平台' OrderSourceTypeName UNION SELECT 59 OrderSourceType , 'Yandex平台' OrderSourceTypeName UNION SELECT 60 OrderSourceType , 'Paytm平台' OrderSourceTypeName UNION SELECT 61 OrderSourceType , '京东国际平台' OrderSourceTypeName UNION SELECT 62 OrderSourceType , 'Vova平台' OrderSourceTypeName UNION SELECT 63 OrderSourceType , '京东印尼平台' OrderSourceTypeName UNION SELECT 64 OrderSourceType , 'UMKA平台' OrderSourceTypeName UNION SELECT 65 OrderSourceType , 'Rakuten平台' OrderSourceTypeName UNION SELECT 66 OrderSourceType , '值点平台' OrderSourceTypeName UNION SELECT 67 OrderSourceType , 'Allegro平台' OrderSourceTypeName UNION SELECT 68 OrderSourceType , '亲库平台' OrderSourceTypeName UNION SELECT 69 OrderSourceType , 'ZoodMall' OrderSourceTypeName UNION SELECT 70 OrderSourceType , 'Mercadolibre国际平台' OrderSourceTypeName UNION SELECT 71 OrderSourceType , 'Gmarket平台' OrderSourceTypeName UNION SELECT 72 OrderSourceType , 'Auction平台' OrderSourceTypeName UNION SELECT 73 OrderSourceType , 'Coupang平台' OrderSourceTypeName UNION SELECT 74 OrderSourceType , 'FacebookMarketplace平台' OrderSourceTypeName UNION SELECT 75 OrderSourceType , 'KauflandDe平台' OrderSourceTypeName UNION SELECT 76 OrderSourceType , '阿里巴巴国际站平台' OrderSourceTypeName UNION SELECT 77 OrderSourceType , 'RatukenDE平台' OrderSourceTypeName UNION SELECT 78 OrderSourceType , 'Wayfair平台' OrderSourceTypeName UNION SELECT 79 OrderSourceType , '店匠平台' OrderSourceTypeName UNION SELECT 80 OrderSourceType , 'B2W平台' OrderSourceTypeName UNION SELECT 81 OrderSourceType , 'Kogan平台' OrderSourceTypeName UNION SELECT 82 OrderSourceType , 'Fruugo平台' OrderSourceTypeName UNION SELECT 83 OrderSourceType , 'Komplett平台' OrderSourceTypeName UNION SELECT 84 OrderSourceType , 'Digitect平台' OrderSourceTypeName UNION SELECT 85 OrderSourceType , 'Yahoo平台' OrderSourceTypeName UNION SELECT 86 OrderSourceType , 'Bukalapak平台' OrderSourceTypeName UNION SELECT 87 OrderSourceType , '沃尔玛DSV平台' OrderSourceTypeName UNION SELECT 88 OrderSourceType , 'Shopyy平台' OrderSourceTypeName UNION SELECT 89 OrderSourceType , 'L192平台' OrderSourceTypeName UNION SELECT 90 OrderSourceType , 'OTTO平台' OrderSourceTypeName UNION SELECT 91 OrderSourceType , 'CrossMall平台' OrderSourceTypeName UNION SELECT 92 OrderSourceType , 'Fnac平台' OrderSourceTypeName UNION SELECT 93 OrderSourceType , 'Darty平台' OrderSourceTypeName UNION SELECT 94 OrderSourceType , 'EPrice平台' OrderSourceTypeName UNION SELECT 95 OrderSourceType , 'Daraz平台' OrderSourceTypeName UNION SELECT 96 OrderSourceType , 'Americanas平台' OrderSourceTypeName UNION SELECT 97 OrderSourceType , 'ShopeeV2平台' OrderSourceTypeName UNION SELECT 98 OrderSourceType , 'Snapdeal平台' OrderSourceTypeName UNION SELECT 99 OrderSourceType , 'Submarino平台' OrderSourceTypeName UNION SELECT 100 OrderSourceType , 'Overstock平台' OrderSourceTypeName UNION SELECT 101 OrderSourceType , 'ManoMano平台' OrderSourceTypeName UNION SELECT 102 OrderSourceType , 'RueduCommerce平台' OrderSourceTypeName UNION SELECT 103 OrderSourceType , 'TradeMe平台' OrderSourceTypeName UNION SELECT 104 OrderSourceType , 'TikTok平台' OrderSourceTypeName UNION SELECT 105 OrderSourceType , 'Worten平台' OrderSourceTypeName UNION SELECT 106 OrderSourceType , 'PayPayMall平台' OrderSourceTypeName UNION SELECT 107 OrderSourceType , 'Target平台' OrderSourceTypeName UNION SELECT 108 OrderSourceType , 'Flipkart平台' OrderSourceTypeName UNION SELECT 109 OrderSourceType , 'BestBuy平台' OrderSourceTypeName UNION SELECT 110 OrderSourceType , 'BolCom平台' OrderSourceTypeName UNION SELECT 111 OrderSourceType , 'Tokopedia平台' OrderSourceTypeName UNION SELECT 112 OrderSourceType , 'SHOPLINE平台' OrderSourceTypeName UNION SELECT 114 OrderSourceType , 'Akulaku平台' OrderSourceTypeName UNION SELECT 115 OrderSourceType , 'Funpinpin平台' OrderSourceTypeName UNION SELECT 116 OrderSourceType , 'Fannoshop平台' OrderSourceTypeName UNION SELECT 117 OrderSourceType , 'OnBuy' OrderSourceTypeName UNION SELECT 118 OrderSourceType , 'HomeDepot' OrderSourceTypeName UNION SELECT 119 OrderSourceType , 'WooCommerce' OrderSourceTypeName UNION SELECT 120 OrderSourceType , 'Thisshop' OrderSourceTypeName UNION SELECT 121 OrderSourceType , 'XShoppy' OrderSourceTypeName UNION SELECT 124 OrderSourceType , 'Catch平台' OrderSourceTypeName UNION SELECT 125 OrderSourceType , '拼多多跨境' OrderSourceTypeName UNION SELECT 126 OrderSourceType , 'Blidz平台' OrderSourceTypeName UNION SELECT 127 OrderSourceType , 'Microsoft平台' OrderSourceTypeName )a; --158库运行程序--DECLARE @StartTime DATETIME --DECLARE @EndTime DATETIME --DECLARE @CustomerID INT --SET @StartTime = '2023-03-01' --SET @EndTime = '2023-03-01 23:59:59' --SET @CustomerID = 1
DROP TABLE IF EXISTS #temp DROP TABLE IF EXISTS #temp_firsttime DROP TABLE IF EXISTS #temp1 DROP TABLE IF EXISTS #temp_sh DROP TABLE IF EXISTS #temp_ph DROP TABLE IF EXISTS #temp_distribution DROP TABLE IF EXISTS #temp_sale DROP TABLE IF EXISTS #temp_cf DROP TABLE IF EXISTS #temp_sh DROP TABLE IF EXISTS #temp_detail DROP TABLE IF EXISTS #temp_summary_1 DROP TABLE IF EXISTS #temp_summary_2 DROP TABLE IF EXISTS #temp_summary_3 DROP TABLE IF EXISTS #temp_summary_4 DROP TABLE IF EXISTS #temp_summary_5 --判断sku前30天有无订单->是不是首单
SELECT DISTINCT ol.SKU, oi.PayTime 支付时间, ol.OrderCode INTO #temp FROM irobotbox.dbo.RB_OrderInfo oi --订单表
LEFT JOIN irobotbox.dbo.RB_OrderList ol ON ol.CustomerID = oi.CustomerID AND ol.OrderCode=oi.OrderCode --订单明细表
LEFT JOIN irobotbox.dbo.RB_WareHouse wh(NOLOCK) ON oi.WareHouseID=wh.ID AND oi.CustomerID=wh.CustomerID --仓库表
WHERE oi.PayTime>=DATEADD(DAY,-30,@StartTime) AND ol.CustomerID=1 AND wh.WareHouseType =1 --1是本地
AND oi.IsStockOrder = 0 AND ol.SKU IS NOT NULL AND oi.IsPay=1 --支付成功
SELECT ol.SKU, oi.PayTime 支付时间, SUM(CASE WHEN (a.支付时间>=DATEADD(DAY,-30,oi.PayTime) AND a.支付时间<oi.PayTime) THEN 1 ELSE 0 END) 订单支付前30天订单数 INTO #temp_firsttime FROM irobotbox.dbo.RB_OrderInfo oi --订单表 LEFT JOIN irobotbox.dbo.RB_OrderList ol ON ol.CustomerID = oi.CustomerID AND ol.OrderCode=oi.OrderCode --订单明细表 LEFT JOIN irobotbox.dbo.RB_WareHouse wh(NOLOCK) ON oi.WareHouseID=wh.ID AND oi.CustomerID=wh.CustomerID --仓库表 LEFT JOIN #temp a ON a.SKU=ol.SKU WHERE oi.PayTime>=@StartTime AND oi.PayTime<=@EndTime --AND oi.PayTime>=DATEADD(DAY,-30,GETDATE()) AND ol.CustomerID=1 AND wh.WareHouseType =1 --1是本地 AND oi.IsStockOrder = 0 AND ol.SKU IS NOT NULL AND oi.IsPay=1 --支付成功 GROUP BY ol.SKU,oi.PayTime --SELECT * FROM #temp_firsttime SELECT DISTINCT wh.WareHouseName 发货仓, oi.OrderCode 订单号, oi.ClientOrderCode 平台订单号, ol.SKU, (ProductNum+ProductNumUpdate) 产品数, p.ProductNameCN 中文名, ost.OrderSourceTypeName 平台, oi.PayTime 支付时间, oi.AddTime 添加时间, (CASE WHEN oi.ShipTime='2000-01-01 00:00:00.000' THEN NULL ELSE oi.ShipTime END) 发货时间, os.SaleAdminName 销售人员, dp2.DepartmentName 销售小组, (CASE WHEN ol.BuyerID=0 THEN pa.BuyerName ELSE ol.BuyerName END) 采购人员, (CASE WHEN ol.BuyerID=0 THEN pa.BuyerID ELSE ol.BuyerID END) 采购人员id, --dp.DepartmentName 采购组, (CASE WHEN ft.订单支付前30天订单数=0 THEN '是' ELSE '否' END) 近30天是否首单, (CASE oi.OrderStatus WHEN 0 THEN '正常' WHEN 1 THEN '待审核' WHEN 2 THEN '作废' WHEN 3 THEN '锁定' WHEN 4 THEN '只锁定发货' WHEN 5 THEN '已完成' WHEN 6 THEN '待确认' END) 订单状态, --(CASE p.ProductState -- WHEN 0 THEN '正常' -- WHEN 1 THEN '停产' -- WHEN 2 THEN '停产不可批发' -- WHEN 3 THEN '锁定' --END) 产品状态, (CASE p.ProductState WHEN 0 THEN '正常' WHEN 1 THEN '起批量' WHEN 2 THEN '停产' WHEN 3 THEN '锁定' WHEN 4 THEN '暂时缺货' WHEN 5 THEN '清库' END) 产品状态, CASE WHEN p.ProductState=2 THEN p.ProdutStopDate END 停产时间, rf.CauseID, rf.ParentID, rf2.CauseName 退款原因, CASE oi.OrderState WHEN 0 THEN '未发货' WHEN 1 THEN '部分发货' WHEN 2 THEN '全部发货' WHEN 3 THEN '妥投' END 订单发货状态, (CASE WHEN oi2.OrderCode IS NULL THEN oi.OrderCode ELSE oi2.OrderCode END) [合并订单号(主)] INTO #temp1 FROM irobotbox.dbo.RB_OrderInfo oi(NOLOCK) --订单表 LEFT JOIN irobotbox.dbo.RB_OrderList ol(NOLOCK) ON ol.CustomerID = oi.CustomerID AND ol.OrderCode=oi.OrderCode --订单明细表 LEFT JOIN irobotbox.dbo.RB_WareHouse wh(NOLOCK) ON oi.WareHouseID=wh.ID AND oi.CustomerID=wh.CustomerID --仓库表 LEFT JOIN irobotbox.dbo.RB_Product p(NOLOCK) ON p.CustomerID=ol.CustomerID AND p.SKU=ol.SKU --产品表 --LEFT JOIN irobotbox.dbo.RB_Procurement_Order_Package pop(NOLOCK) ON pop.CustomerID=oi.CustomerID AND pop.MergerPackageNo=oi.OrderCode --仓库包裹表 LEFT JOIN irobotbox.dbo.RB_OrderSource os(NOLOCK) ON os.CustomerID=ol.CustomerID AND os.id=oi.OrderSourceID LEFT JOIN #temp_OrderSourceType ost ON os.OrderSourceType=ost.OrderSourceType LEFT JOIN irobotbox.dbo.RB_Product_Admin pa(NOLOCK) ON pa.SKU=ol.SKU AND ol.CustomerID = pa.CustomerID --部分订单表上没有采购人员，使用对应sku默认采购人员 --LEFT JOIN irobotbox.dbo.RB_Admin ad(NOLOCK) ON ad.CustomerID=ol.customerID AND ol.BuyerID=ad.ID --LEFT JOIN irobotbox.dbo.RB_Department dp(NOLOCK) ON dp.CustomerID=ol.customerID AND ad.DepartmentID=dp.ID LEFT JOIN irobotbox.dbo.RB_Admin ad2(NOLOCK) ON ad2.CustomerID = ol.CustomerID AND os.SaleAdminID=ad2.ID LEFT JOIN irobotbox.dbo.RB_Department dp2(NOLOCK) ON dp2.CustomerID=ol.customerID AND ad2.DepartmentID=dp2.ID LEFT JOIN irobotbox.dbo.RB_OrderInfo_FundReceipt oifr(NOLOCK) ON oifr.CustomerID= oi.CustomerID AND oifr.OrderCode=oi.OrderCode LEFT JOIN irobotbox.dbo.RB_OrderInfo_RefundCause rf(NOLOCK) ON rf.CustomerID = oi.CustomerID AND oifr.ReasonTypes=rf.CauseID LEFT JOIN irobotbox.dbo.RB_OrderInfo_RefundCause rf2(NOLOCK) ON rf.CustomerID = rf2.CustomerID AND rf2.CauseID=rf.ParentID --为了关联一级原因 --AND rf.CauseLevel=1 LEFT JOIN #temp_firsttime ft ON ft.SKU = ol.SKU AND ft.支付时间=oi.PayTime LEFT JOIN irobotbox.dbo.RB_OrderInfo oi2(NOLOCK) ON oi.MergeOrderCode=oi2.ID AND oi2.CustomerID = oi.CustomerID --订单表 WHERE ol.CustomerID=@CustomerID AND oi.PayTime>=@StartTime AND oi.PayTime<=@EndTime AND wh.WareHouseType =1 --1是本地 AND oi.IsStockOrder = 0 AND (ProductNum+ProductNumUpdate)>0 AND p.ProductNameCN IS NOT NULL AND oi.IsPay=1 --支付成功 --SELECT * FROM #temp1 WHERE 订单号='202211120257129984397' --114库订单日志表数据 --SELECT -- oil.OrderCode, -- MIN(oil.AddTime) AddTime --INTO #temp_sh --FROM dbo.RB_OrderInfo_log oil(NOLOCK) --WHERE -- oil.AddTime>= (SELECT MIN(添加时间) FROM #temp1) -- AND oil.OrderLog LIKE '%订单%状态%设置为待发货%' --GROUP BY oil.OrderCode --SELECT -- oil.OrderCode, -- MIN(oil.AddTime) AddTime --INTO #temp_ph --FROM dbo.RB_OrderInfo_log oil(NOLOCK) --WHERE -- oil.AddTime>= (SELECT MIN(添加时间) FROM #temp1) -- AND oil.OrderLog LIKE '%已配货%' --GROUP BY oil.OrderCode --订单配货时间 SELECT a.MergerPackageNo, a.DistributionTime INTO #temp_distribution FROM ( SELECT pop.CustomerID, pop.MergerPackageNo, pop.DistributionTime, ROW_NUMBER() OVER(PARTITION BY pop.CustomerID,pop.MergerPackageNo ORDER BY pop.DistributionTime ASC) rk FROM irobotbox.dbo.RB_Procurement_Order_Package pop(NOLOCK) WHERE pop.CustomerID=1 AND pop.MergerPackageNo IN (SELECT DISTINCT t.[合并订单号(主)] FROM #temp1 t) ) a WHERE a.rk=1 --AND a.MergerPackageNo='202303020636138005415' --近30天销量数据 SELECT t1.SKU, t1.支付时间, SUM(CASE WHEN (oi.PayTime > DATEADD(DAY,-30,t1.支付时间) AND oi.PayTime <= t1.支付时间 ) THEN (ISNULL(ol.ProductNum,0)+ISNULL(ol.ProductNumUpdate,0)) ELSE 0 END) num INTO #temp_sale FROM #temp1 t1 LEFT JOIN dbo.RB_OrderList ol ON ol.CustomerID = 1 AND ol.SKU = t1.SKU LEFT JOIN dbo.RB_OrderInfo oi(NOLOCK) ON ol.CustomerID = oi.CustomerID AND ol.OrderCode = oi.OrderCode LEFT JOIN dbo.RB_WareHouse w(NOLOCK) ON w.CustomerID = oi.CustomerID AND w.ID=oi.WareHouseID WHERE w.WareHouseType=1 AND oi.IsStockOrder=0 AND ol.CustomerID=1 AND oi.OrderStatus!=2 --AND sku='14401430 ' GROUP BY t1.SKU,t1.支付时间 --拆分订单 SELECT DISTINCT t.订单号 INTO #temp_cf FROM ( SELECT t1.订单号, t1.平台订单号, t1.添加时间, RANK() OVER (PARTITION BY t1.平台订单号 ORDER BY t1.添加时间) r FROM #temp1 t1 ) t WHERE t.r>1 SELECT oc.OrderCode 订单号, MAX(oc.SetNormalTime) 审核时间 INTO #temp_sh FROM RB_OrderInfo_ClientExpand oc(NOLOCK) INNER JOIN #temp1 t ON t.订单号 = oc.OrderCode AND oc.CustomerID =1 GROUP BY oc.OrderCode -- 查询明细表 SELECT a.发货仓, a.订单号, a.SKU, a.产品数, s.num*1.00/30 本地仓近30天日均销量, a.中文名, a.平台, a.支付时间, a.添加时间, a.发货时间, sh.审核时间, d.DistributionTime 配货时间, CASE WHEN d.DistributionTime IS NOT NULL THEN DATEDIFF(MINUTE,a.支付时间,d.DistributionTime)*1.00/1440 ELSE DATEDIFF(MINUTE,a.支付时间,GETDATE())*1.00/1440 END 配货天数, a.销售人员, a.销售小组, a.采购人员, dp.DepartmentName 采购组, a.近30天是否首单, a.订单状态, a.产品状态, a.停产时间, a.退款原因, a.订单发货状态, CASE WHEN cf.订单号 IS NULL THEN '否' ELSE '是' END '是否拆分/重建单', CASE WHEN (a.近30天是否首单='否' AND NOT( (a.发货时间 IS NULL AND a.退款原因!='缺货(item is out of stock)' AND a.退款原因 IS NOT NULL) OR (a.发货时间 IS NULL AND a.订单状态='作废' AND a.退款原因 IS NULL) ) AND a.SKU!='3270453'--补差价专拍剔除 AND cf.订单号 IS NULL) THEN '否' ELSE '是' END 是否断货剔除 --'是' 是否断货剔除 --a.CauseID, --a.ParentID INTO #temp_detail FROM #temp1 a --LEFT JOIN #temp_sh b ON a.订单号=b.OrderCode --LEFT JOIN #temp_ph c ON a.订单号=c.OrderCode LEFT JOIN #temp_distribution d ON a.[合并订单号(主)]=d.MergerPackageNo LEFT JOIN irobotbox.dbo.RB_Admin ad(NOLOCK) ON ad.CustomerID=1 AND a.采购人员id=ad.ID LEFT JOIN irobotbox.dbo.RB_Department dp(NOLOCK) ON dp.CustomerID=1 AND ad.DepartmentID=dp.ID LEFT JOIN #temp_sale s ON s.SKU = a.SKU AND s.支付时间 = a.支付时间 LEFT JOIN #temp_cf cf ON cf.订单号 = a.订单号 LEFT JOIN #temp_sh sh ON sh.订单号 = a.订单号 --d --SET d.是否断货剔除='否' --FROM #temp_detail d --WHERE d.近30天是否首单='否' --AND NOT( -- (d.发货时间 IS NULL AND d.退款原因!='缺货(item is out of stock)' AND d.退款原因 IS NOT NULL) -- OR (d.发货时间 IS NULL AND d.订单状态='作废' AND d.退款原因 IS NULL) --) --AND d.SKU!='3270453'--补差价专拍剔除 --AND d.[是否拆分/重建单]='否' SELECT * FROM #temp_detail --WHERE 订单号='202303020636138005415' --WHERE 配货时间 IS NULL --SELECT a.* FROM( --SELECT *,count(订单号)OVER(PARTITION BY 订单号) ran FROM #temp_detail --)a WHERE a.ran!=1 --WHERE 平台='LazadaV2平台' --AND 发货时间 IS NOT NULL --剔除未发货的订单 --AND 近30天是否首单='否' --AND (退款原因<>'缺货(item is out of stock)' OR 退款原因 IS NULL)--非缺货退款原因 --中文名 IS NULL --近30天是否首单='是' --订单号='202211150046130183728' --SKU='10004392' --采购人员 IS NULL OR 采购组 IS NULL --订单号='202211120257129984397' --退款原因 LIKE '%item is out of stock%' --采购组 IS NOT NULL --AND 退款原因 IS NOT NULL --AND SKU IS NOT NULL AND SKU!='' SELECT CONVERT(VARCHAR(7),d.支付时间,126) 支付月份, d.采购人员, d.采购组, COUNT(DISTINCT CASE WHEN d.配货天数>1 THEN d.订单号 END) 断货订单数, COUNT(DISTINCT d.订单号) 订单总数, COUNT(DISTINCT CASE WHEN d.配货天数>1 AND d.本地仓近30天日均销量>=2 THEN d.订单号 END) 日均销量2个及以上断货订单数, COUNT(DISTINCT CASE WHEN d.本地仓近30天日均销量>=2 THEN d.订单号 END) 日均销量2个及以上订单总数, COUNT(DISTINCT CASE WHEN d.配货天数>1 AND d.本地仓近30天日均销量>=0.5 THEN d.订单号 END) [日均销量0.5及以上断货订单数], COUNT(DISTINCT CASE WHEN d.本地仓近30天日均销量>=0.5 THEN d.订单号 END) [日均销量0.5个及以上订单总数] --COUNT(DISTINCT CASE -- WHEN d.配货时间 IS NOT NULL -- THEN (CASE -- WHEN DATEDIFF(HOUR,d.支付时间,d.配货时间)>24 THEN d.订单号 -- ELSE NULL -- END) -- WHEN DATEDIFF(HOUR,d.支付时间,GETDATE())>24 THEN d.订单号 -- ELSE NULL --END) 断货订单数, INTO #temp_summary_1 FROM #temp_detail d WHERE d.是否断货剔除='否' --d.近30天是否首单='否' --AND NOT( -- (d.发货时间 IS NULL AND d.退款原因!='缺货(item is out of stock)' AND d.退款原因 IS NOT NULL) -- OR (d.发货时间 IS NULL AND d.订单状态='作废' AND d.退款原因 IS NULL) --) --AND d.SKU!='3270453'--补差价专拍剔除 --AND d.[是否拆分/重建单]='否' GROUP BY CONVERT(VARCHAR(7),d.支付时间,126), d.采购人员, d.采购组 --按支付月份/采购人员/采购组维度汇总 -- 支付月份 采购人员 采购组 断货订单数 订单总数 直发单断货率 SELECT a.*, CAST(CAST((CASE WHEN a.订单总数=0 THEN NULL ELSE CONVERT(DECIMAL(10,2),a.断货订单数)/a.订单总数 END)*100 AS NUMERIC(10,2)) AS VARCHAR(20))+'%' 直发单断货率, CAST(CAST((CASE WHEN a.日均销量2个及以上订单总数=0 THEN NULL ELSE CONVERT(DECIMAL(10,2),a.日均销量2个及以上断货订单数)/a.日均销量2个及以上订单总数 END)*100 AS NUMERIC(10,2)) AS VARCHAR(20))+'%' 日均销量2个及以上断货率, CAST(CAST((CASE WHEN a.[日均销量0.5个及以上订单总数]=0 THEN NULL ELSE CONVERT(DECIMAL(10,2),a.[日均销量0.5及以上断货订单数])/a.[日均销量0.5个及以上订单总数] END)*100 AS NUMERIC(10,2)) AS VARCHAR(20))+'%' [日均销量0.5个及以上断货率] FROM #temp_summary_1 a SELECT CONVERT(VARCHAR(7),d.支付时间,126) 支付月份, d.发货仓, COUNT(DISTINCT CASE WHEN d.配货天数>1 THEN d.订单号 END) 断货订单数, COUNT(DISTINCT d.订单号) 订单总数, COUNT(DISTINCT CASE WHEN d.配货天数>1 AND d.本地仓近30天日均销量>=2 THEN d.订单号 END) 日均销量2个及以上断货订单数, COUNT(DISTINCT CASE WHEN d.本地仓近30天日均销量>=2 THEN d.订单号 END) 日均销量2个及以上订单总数, COUNT(DISTINCT CASE WHEN d.配货天数>1 AND d.本地仓近30天日均销量>=0.5 THEN d.订单号 END) [日均销量0.5及以上断货订单数], COUNT(DISTINCT CASE WHEN d.本地仓近30天日均销量>=0.5 THEN d.订单号 END) [日均销量0.5个及以上订单总数] --COUNT(DISTINCT CASE -- WHEN d.配货时间 IS NOT NULL -- THEN (CASE -- WHEN DATEDIFF(HOUR,d.支付时间,d.配货时间)>24 THEN d.订单号 -- ELSE NULL -- END) -- WHEN DATEDIFF(HOUR,d.支付时间,GETDATE())>24 THEN d.订单号 -- ELSE NULL --END) 断货订单数, INTO #temp_summary_2 FROM #temp_detail d WHERE d.是否断货剔除='否' --d.近30天是否首单='否' --AND NOT( -- (d.发货时间 IS NULL AND d.退款原因!='缺货(item is out of stock)' AND d.退款原因 IS NOT NULL) -- OR (d.发货时间 IS NULL AND d.订单状态='作废' AND d.退款原因 IS NULL) --) --AND d.SKU!='3270453'--补差价专拍剔除 --AND d.[是否拆分/重建单]='否' GROUP BY CONVERT(VARCHAR(7),d.支付时间,126), d.发货仓 --按支付月份/发货仓维度汇总 -- 支付月份 发货仓 断货订单数 订单总数 直发单断货率 SELECT a.*, CAST(CAST((CASE WHEN a.订单总数=0 THEN NULL ELSE CONVERT(DECIMAL(10,2),a.断货订单数)/a.订单总数 END)*100 AS NUMERIC(10,2)) AS VARCHAR(20))+'%' 直发单断货率, CAST(CAST((CASE WHEN a.日均销量2个及以上订单总数=0 THEN NULL ELSE CONVERT(DECIMAL(10,2),a.日均销量2个及以上断货订单数)/a.日均销量2个及以上订单总数 END)*100 AS NUMERIC(10,2)) AS VARCHAR(20))+'%' 日均销量2个及以上断货率, CAST(CAST((CASE WHEN a.[日均销量0.5个及以上订单总数]=0 THEN NULL ELSE CONVERT(DECIMAL(10,2),a.[日均销量0.5及以上断货订单数])/a.[日均销量0.5个及以上订单总数] END)*100 AS NUMERIC(10,2)) AS VARCHAR(20))+'%' [日均销量0.5个及以上断货率] FROM #temp_summary_2 a SELECT CONVERT(VARCHAR(7),d.支付时间,126) 支付月份, d.平台, COUNT(DISTINCT CASE WHEN d.配货天数>1 THEN d.订单号 END) 断货订单数, COUNT(DISTINCT d.订单号) 订单总数, COUNT(DISTINCT CASE WHEN d.配货天数>1 AND d.本地仓近30天日均销量>=2 THEN d.订单号 END) 日均销量2个及以上断货订单数, COUNT(DISTINCT CASE WHEN d.本地仓近30天日均销量>=2 THEN d.订单号 END) 日均销量2个及以上订单总数, COUNT(DISTINCT CASE WHEN d.配货天数>1 AND d.本地仓近30天日均销量>=0.5 THEN d.订单号 END) [日均销量0.5及以上断货订单数], COUNT(DISTINCT CASE WHEN d.本地仓近30天日均销量>=0.5 THEN d.订单号 END) [日均销量0.5个及以上订单总数] --COUNT(DISTINCT CASE -- WHEN d.配货时间 IS NOT NULL -- THEN (CASE -- WHEN DATEDIFF(HOUR,d.支付时间,d.配货时间)>24 THEN d.订单号 -- ELSE NULL -- END) -- WHEN DATEDIFF(HOUR,d.支付时间,GETDATE())>24 THEN d.订单号 -- ELSE NULL --END) 断货订单数, INTO #temp_summary_3 FROM #temp_detail d WHERE d.是否断货剔除='否' --d.近30天是否首单='否' --AND NOT( -- (d.发货时间 IS NULL AND d.退款原因!='缺货(item is out of stock)' AND d.退款原因 IS NOT NULL) -- OR (d.发货时间 IS NULL AND d.订单状态='作废' AND d.退款原因 IS NULL) --) --AND d.SKU!='3270453'--补差价专拍剔除 --AND d.[是否拆分/重建单]='否' GROUP BY CONVERT(VARCHAR(7),d.支付时间,126), d.平台 --按支付月份/平台维度汇总 -- 支付月份 平台 断货订单数 订单总数 直发单断货率 SELECT a.*, CAST(CAST((CASE WHEN a.订单总数=0 THEN NULL ELSE CONVERT(DECIMAL(10,2),a.断货订单数)/a.订单总数 END)*100 AS NUMERIC(10,2)) AS VARCHAR(20))+'%' 直发单断货率, CAST(CAST((CASE WHEN a.日均销量2个及以上订单总数=0 THEN NULL ELSE CONVERT(DECIMAL(10,2),a.日均销量2个及以上断货订单数)/a.日均销量2个及以上订单总数 END)*100 AS NUMERIC(10,2)) AS VARCHAR(20))+'%' 日均销量2个及以上断货率, CAST(CAST((CASE WHEN a.[日均销量0.5个及以上订单总数]=0 THEN NULL ELSE CONVERT(DECIMAL(10,2),a.[日均销量0.5及以上断货订单数])/a.[日均销量0.5个及以上订单总数] END)*100 AS NUMERIC(10,2)) AS VARCHAR(20))+'%' [日均销量0.5个及以上断货率] FROM #temp_summary_3 a SELECT CONVERT(VARCHAR(10),d.支付时间,126) 支付日期, COUNT(DISTINCT CASE WHEN d.配货天数>1 THEN d.订单号 END) 断货订单数, COUNT(DISTINCT d.订单号) 订单总数, COUNT(DISTINCT CASE WHEN d.配货天数>1 AND d.本地仓近30天日均销量>=2 THEN d.订单号 END) 日均销量2个及以上断货订单数, COUNT(DISTINCT CASE WHEN d.本地仓近30天日均销量>=2 THEN d.订单号 END) 日均销量2个及以上订单总数, COUNT(DISTINCT CASE WHEN d.配货天数>1 AND d.本地仓近30天日均销量>=0.5 THEN d.订单号 END) [日均销量0.5及以上断货订单数], COUNT(DISTINCT CASE WHEN d.本地仓近30天日均销量>=0.5 THEN d.订单号 END) [日均销量0.5个及以上订单总数] --COUNT(DISTINCT CASE -- WHEN d.配货时间 IS NOT NULL -- THEN (CASE -- WHEN DATEDIFF(HOUR,d.支付时间,d.配货时间)>24 THEN d.订单号 -- ELSE NULL -- END) -- WHEN DATEDIFF(HOUR,d.支付时间,GETDATE())>24 THEN d.订单号 -- ELSE NULL --END) 断货订单数, INTO #temp_summary_4 FROM #temp_detail d WHERE d.是否断货剔除='否' --d.近30天是否首单='否' --AND NOT( -- (d.发货时间 IS NULL AND d.退款原因!='缺货(item is out of stock)' AND d.退款原因 IS NOT NULL) -- OR (d.发货时间 IS NULL AND d.订单状态='作废' AND d.退款原因 IS NULL) --) --AND d.SKU!='3270453'--补差价专拍剔除 --AND d.[是否拆分/重建单]='否' GROUP BY CONVERT(VARCHAR(10),d.支付时间,126) --按支付时间（按天）维度汇总 -- 支付时间 断货订单数 订单总数 直发单断货率 SELECT a.*, CAST(CAST((CASE WHEN a.订单总数=0 THEN NULL ELSE CONVERT(DECIMAL(10,2),a.断货订单数)/a.订单总数 END)*100 AS NUMERIC(10,2)) AS VARCHAR(20))+'%' 直发单断货率, CAST(CAST((CASE WHEN a.日均销量2个及以上订单总数=0 THEN NULL ELSE CONVERT(DECIMAL(10,2),a.日均销量2个及以上断货订单数)/a.日均销量2个及以上订单总数 END)*100 AS NUMERIC(10,2)) AS VARCHAR(20))+'%' 日均销量2个及以上断货率, CAST(CAST((CASE WHEN a.[日均销量0.5个及以上订单总数]=0 THEN NULL ELSE CONVERT(DECIMAL(10,2),a.[日均销量0.5及以上断货订单数])/a.[日均销量0.5个及以上订单总数] END)*100 AS NUMERIC(10,2)) AS VARCHAR(20))+'%' [日均销量0.5个及以上断货率] FROM #temp_summary_4 a ORDER BY a.支付日期 SELECT CONVERT(VARCHAR(10),d.支付时间,126) 支付日期, COUNT(DISTINCT CASE WHEN d.配货天数>1 THEN d.订单号 END) 断货订单数, COUNT(DISTINCT d.订单号) 订单总数, COUNT(DISTINCT CASE WHEN d.配货天数>1 AND d.本地仓近30天日均销量>=2 THEN d.订单号 END) 日均销量2个及以上断货订单数, COUNT(DISTINCT CASE WHEN d.本地仓近30天日均销量>=2 THEN d.订单号 END) 日均销量2个及以上订单总数, COUNT(DISTINCT CASE WHEN d.配货天数>1 AND d.本地仓近30天日均销量>=0.5 THEN d.订单号 END) [日均销量0.5及以上断货订单数], COUNT(DISTINCT CASE WHEN d.本地仓近30天日均销量>=0.5 THEN d.订单号 END) [日均销量0.5个及以上订单总数] --COUNT(DISTINCT CASE -- WHEN d.配货时间 IS NOT NULL -- THEN (CASE -- WHEN DATEDIFF(HOUR,d.支付时间,d.配货时间)>24 THEN d.订单号 -- ELSE NULL -- END) -- WHEN DATEDIFF(HOUR,d.支付时间,GETDATE())>24 THEN d.订单号 -- ELSE NULL --END) 断货订单数, INTO #temp_summary_5 FROM #temp_detail d WHERE d.SKU!='3270453'--补差价专拍剔除 GROUP BY CONVERT(VARCHAR(10),d.支付时间,126) --按支付时间（按天）维度汇总(只剔除sku 3270453） -- 支付时间 断货订单数 订单总数 直发单断货率 SELECT a.*, CAST(CAST((CASE WHEN a.订单总数=0 THEN NULL ELSE CONVERT(DECIMAL(10,2),a.断货订单数)/a.订单总数 END)*100 AS NUMERIC(10,2)) AS VARCHAR(20))+'%' 直发单断货率, CAST(CAST((CASE WHEN a.日均销量2个及以上订单总数=0 THEN NULL ELSE CONVERT(DECIMAL(10,2),a.日均销量2个及以上断货订单数)/a.日均销量2个及以上订单总数 END)*100 AS NUMERIC(10,2)) AS VARCHAR(20))+'%' 日均销量2个及以上断货率, CAST(CAST((CASE WHEN a.[日均销量0.5个及以上订单总数]=0 THEN NULL ELSE CONVERT(DECIMAL(10,2),a.[日均销量0.5及以上断货订单数])/a.[日均销量0.5个及以上订单总数] END)*100 AS NUMERIC(10,2)) AS VARCHAR(20))+'%' [日均销量0.5个及以上断货率] FROM #temp_summary_5 a ORDER BY a.支付日期
[结果]
[期望]
检查 导出语句SQL 补充完整的nolock