https://alidocs.dingtalk.com/i/nodes/P7QG4Yx2Jp7PMwD5hYr54MPgV9dEq3XD
业务需求
需求描述
验收标准
价值描述
10000
001-[步骤]
001+ <a href="https://alidocs.dingtalk.com/i/nodes/P7QG4Yx2Jp7PMwD5hYr54MPgV9dEq3XD">https://alidocs.dingtalk.com/i/nodes/P7QG4Yx2Jp7PMwD5hYr54MPgV9dEq3XD
002-IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'tempdb..#tempQ') and type='U') BEGIN DROP TABLE #tempQ END IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'tempdb..#tempAll') and type='U') BEGIN DROP TABLE #tempAll END select OrderSourceID, StatisticDate into #tempQ from RB_Statistic_Queue(NOLOCK)q where q.CustomerID=1 and StatisticType='OrderProfitStaDimDay' and IsProcess = 0 and ProcessMessage='1' ;CREATE INDEX IX_TempTable_ID ON #tempQ (OrderSourceID,StatisticDate);select g1.* into #tempAll From( select 1 CustomerID, '' HashKey,Getdate()AddTime,0 AllAmountRMB, tt1.是否区分发票 PromotionType,convert(date,tt1.DataDate)DataDate,1 DataDim,tt1.BusinessAdminID,tt1.BusinessTeamID,tt1.SalesAdminID,tt1.SalesTeamID,tt1.SettlementDate,tt1.OrderSourceType,tt1.OrderSourceID,tt1.AddOrderReasonType,tt1.WareHouseID,tt1.DeliveryTypeDsec,isnull(tt1.AliExpressTrusteeType,8)AliExpressTrusteeType, tt1.OrderSourceContry, sum(isnull(二毛USD,0))'ProfitsUSD', --二毛USD sum(isnull(二毛RMB,0))'ProfitsRMB',-- 二毛RMB sum(isnull(二点五毛USD,0))'ProfitsTwoFiveUSD',-- 二点五毛USD sum(isnull(二点五毛RMB,0))'ProfitsTwoFiveRMB',-- 二点五毛RMB sum(isnull(销售额USD,0))'SalesPriceUSD',-- 销售额USD sum(isnull(销售额RMB,0))'SalesPriceRMB',-- 销售额RMB sum(isnull(销售收入USD,0))'SalesIncomeUSD',-- 销售收入USD sum(isnull(运费收入USD,0))'TransportPayUSD',-- 运费收入USD sum(isnull(退款金额USD,0))'SalesRefundsUSD',-- 退款金额USD sum(isnull(折扣USD,0))'PromotionRebatesUSD',-- 折扣USD sum(isnull(其他收入USD,0))'OtherCreditsUSD',-- 其他收入USD sum(isnull(税费收入USD,0))'TaxUSD',-- 税费收入USD sum(isnull(销售收入RMB,0))'SalesIncomeRMB',-- 销售收入RMB sum(isnull(运费收入RMB,0))'TransportPayRMB',-- 运费收入RMB sum(isnull(退款金额RMB,0))'SalesRefundsRMB',-- 退款金额RMB sum(isnull(折扣RMB,0))'PromotionRebatesRMB',-- 折扣RMB sum(isnull(其他收入RMB,0))'OtherCreditsRMB',-- 其他收入RMB sum(isnull(税费收入RMB,0))'TaxRMB',-- 税费收入RMB sum(isnull(佣金USD,0))'CommisionFeeUSD',-- 佣金USD sum(isnull(FBA操作费USD,0))'FBATransactionFeesUSD',-- FBA操作费USD sum(isnull(平台其他支出USD,0))'OrderSourceOtherDebitsUSD',-- 平台其他支出USD sum(isnull(广告费USD,0))'CostOfAdvertisingUSD',-- 广告费USD sum(isnull(采购成本USD,0))'BuyPriceUSD',-- 采购成本USD sum(isnull(退还税费USD,0))'TaxRefundFeeUSD',-- 退还税费USD sum(isnull(包装成本USD,0))'PackagedCostUSD',-- 包装成本USD sum(isnull(补贴调整USD,0))'SubsidyPriceUSD',-- 补贴调整USD sum(isnull(本地仓运费USD,0))'PackageFeesUSD',-- 本地仓运费USD sum(isnull(海外仓运费USD,0))'OverSeasShipFeesUSD',-- 海外仓运费USD sum(isnull(头程运费USD,0))'FirstLegFeesUSD',-- 头程运费USD sum(isnull(仓库处理成本USD,0))'WareHouseManagementPriceUSD',-- 仓库处理成本USD sum(isnull(税费USD,0))'TariffFeesUSD',-- 税费USD sum(isnull(汇率折损USD,0))'ExchangeLostUSD',-- 汇率折损USD sum(isnull(海外仓仓租USD,0))'CustomStorageFeeUSD',-- 海外仓仓租USD sum(isnull(人工导入费USD,0))'ImportFeeUSD',-- 人工导入费USD sum(isnull(报损费USD,0))'BreakageFeeUSD',--报损费USD sum(isnull(佣金RMB,0))'CommisionFeeRMB',-- 佣金RMB sum(isnull(FBA操作费RMB,0))'FBATransactionFeesRMB',-- FBA操作费RMB sum(isnull(平台其他支出RMB,0))'OrderSourceOtherDebitsRMB',-- 平台其他支出RMB sum(isnull(广告费RMB,0))'CostOfAdvertisingRMB',-- 广告费RMB sum(isnull(采购成本RMB,0))'BuyPriceRMB',-- 采购成本RMB sum(isnull(退还税费RMB,0))'TaxRefundFeeRMB',-- 退还税费RMB sum(isnull(包装成本RMB,0))'PackagedCostRMB',-- 包装成本RMB sum(isnull(补贴调整RMB,0))'SubsidyPriceRMB',-- 补贴调整RMB sum(isnull(本地仓运费RMB,0))'PackageFeesRMB',-- 本地仓运费RMB sum(isnull(海外仓运费RMB,0))'OverSeasShipFeesRMB',-- 海外仓运费RMB sum(isnull(头程运费RMB,0))'FirstLegFeesRMB',-- 头程运费RMB sum(isnull(仓库处理成本RMB,0))'WareHouseManagementPriceRMB',-- 仓库处理成本RMB sum(isnull(税费RMB,0))'TariffFeesRMB',-- 税费RMB sum(isnull(汇率折损RMB,0))'ExchangeLostRMB',-- 汇率折损RMB sum(isnull(海外仓仓租RMB,0))'CustomStorageFeeRMB',-- 海外仓仓租RMB sum(isnull(人工导入费RMB,0))'ImportFeeRMB',-- 人工导入费RMB sum(isnull(报损费RMB,0))'BreakageFeeRMB',-- 报损费RMB, sum(isnull(AffiliatePartnerCommissionFeeRMB,0))'AffiliatePartnerCommissionFeeRMB', sum(isnull(AffiliatePartnerCommissionFeeUSD,0))'AffiliatePartnerCommissionFeeUSD', sum(isnull(SalesRefundsV2RMB,0))'SalesRefundsV2RMB', sum(isnull(SalesRefundsV2USD,0))'SalesRefundsV2USD', sum(isnull(SalesVatFeeRMB,0))'SalesVatFeeRMB', sum(isnull(SalesVatFeeUSD,0))'SalesVatFeeUSD', sum(isnull(CustomFeeOtherCostPriceRMB,0))'CustomFeeOtherCostPriceRMB', sum(isnull(CustomFeeOtherCostPriceUSD,0))'CustomFeeOtherCostPriceUSD', sum(FirstLegBreakageFeeRMB)FirstLegBreakageFeeRMB, sum(FirstLegBreakageFeeUSD)FirstLegBreakageFeeUSD, sum(OrderNum)OrderNum, sum(ProdutNum)ProdutNum , tt1.Idx From ( select 1'是否区分发票',convert(varchar(10), alias.ShipDate,120) DataDate,alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0)SalesAdminID,ISNULL(alias.SalesTeamID,0)SalesTeamID,convert(varchar(10),alias.SettlementDate,120)SettlementDate,alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end AddOrderReasonType, alias.WareHouseID,ISNULL(alias.DeliveryTypeDsec, '')DeliveryTypeDsec, 3 AliExpressTrusteeType, alias.OrderSourceContry, sum(Profits/alias.USD_ExchangeRMB)'二毛USD', sum(Profits)'二毛RMB', sum((Profits+isnull(BreakageFee,0))/alias.USD_ExchangeRMB)'二点五毛USD', sum(Profits+isnull(BreakageFee,0))'二点五毛RMB', sum(0)'订单数', sum(SalesPrice/alias.USD_ExchangeRMB)'销售额USD', sum(SalesPrice)'销售额RMB', sum(0)'销售数量', sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0)/alias.USD_ExchangeRMB)'销售收入USD', sum(alias.TransportPay/alias.USD_ExchangeRMB)'运费收入USD', sum(alias.SalesRefunds/alias.USD_ExchangeRMB)'退款金额USD', sum(alias.PromotionRebates/alias.USD_ExchangeRMB)'折扣USD', sum(alias.OtherCredits/alias.USD_ExchangeRMB)'其他收入USD', sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0))/alias.USD_ExchangeRMB)'税费收入USD', sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0))'销售收入RMB', sum(alias.TransportPay)'运费收入RMB', sum(alias.SalesRefunds)'退款金额RMB', sum(alias.PromotionRebates)'折扣RMB', sum(alias.OtherCredits)'其他收入RMB', sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)))'税费收入RMB', sum(alias.CommisionFee/alias.USD_ExchangeRMB)'佣金USD', sum(alias.FBATransactionFees/alias.USD_ExchangeRMB)'FBA操作费USD', sum(alias.OrderSourceOtherDebits/alias.USD_ExchangeRMB)'平台其他支出USD', sum(alias.CostOfAdvertising/alias.USD_ExchangeRMB)'广告费USD', sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0))/alias.USD_ExchangeRMB)'采购成本USD', sum(ISNULL(alias.TaxRefundFee,0)/alias.USD_ExchangeRMB) '退还税费USD', sum(alias.PackagedCost/alias.USD_ExchangeRMB)'包装成本USD', sum(ISNULL(alias.SubsidyPrice,0)/alias.USD_ExchangeRMB)'补贴调整USD', sum(alias.PackageFees/alias.USD_ExchangeRMB)'本地仓运费USD', sum(alias.OverSeasShipFees/alias.USD_ExchangeRMB)'海外仓运费USD', sum(alias.FirstLegFees/alias.USD_ExchangeRMB)'头程运费USD', sum(alias.WareHouseManagementPrice/alias.USD_ExchangeRMB)'仓库处理成本USD', sum(alias.TariffFees/alias.USD_ExchangeRMB)'税费USD', sum(alias.ExchangeLost/alias.USD_ExchangeRMB)'汇率折损USD', sum(ISNULL(alias.CustomStorageFee,0)/alias.USD_ExchangeRMB)'海外仓仓租USD', sum(ISNULL(alias.ImportFee,0)/alias.USD_ExchangeRMB)'人工导入费USD', sum(ISNULL(alias.BreakageFee,0)/alias.USD_ExchangeRMB)'报损费USD', sum(alias.CommisionFee)'佣金RMB', sum(alias.FBATransactionFees)'FBA操作费RMB', sum(alias.OrderSourceOtherDebits)'平台其他支出RMB', sum(alias.CostOfAdvertising)'广告费RMB', sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0)))'采购成本RMB', sum(ISNULL(alias.TaxRefundFee,0)) '退还税费RMB', sum(alias.PackagedCost)'包装成本RMB', sum(ISNULL(alias.SubsidyPrice,0))'补贴调整RMB', sum(alias.PackageFees)'本地仓运费RMB', sum(alias.OverSeasShipFees)'海外仓运费RMB', sum(alias.FirstLegFees)'头程运费RMB', sum(alias.WareHouseManagementPrice)'仓库处理成本RMB', sum(alias.TariffFees)'税费RMB', sum(alias.ExchangeLost)'汇率折损RMB', sum(ISNULL(alias.CustomStorageFee,0))'海外仓仓租RMB', sum(ISNULL(alias.ImportFee,0))'人工导入费RMB', sum(ISNULL(alias.BreakageFee,0))'报损费RMB', sum(ISNULL(alias.AffiliatePartnerCommissionFee,0))'AffiliatePartnerCommissionFeeRMB', sum(ISNULL(alias.AffiliatePartnerCommissionFee,0)/alias.USD_ExchangeRMB)'AffiliatePartnerCommissionFeeUSD', ABS(SUM((CASE WHEN ISNULL(alias.SalesRefunds,0)=0 THEN 0 ELSE ISNULL(alias.SalesRefunds,0)+ISNULL(alias.PromotionRebates,0)+ISNULL(alias.OtherCredits,0)+ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0) END))) 'SalesRefundsV2RMB', ABS(SUM((CASE WHEN ISNULL(alias.SalesRefunds,0)=0 THEN 0 ELSE ISNULL(alias.SalesRefunds,0)+ISNULL(alias.PromotionRebates,0)+ISNULL(alias.OtherCredits,0)+ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0) END)/alias.USD_ExchangeRMB)) 'SalesRefundsV2USD', sum(ISNULL(alias.SalesVatFee,0))'SalesVatFeeRMB', sum(ISNULL(alias.SalesVatFee,0)/alias.USD_ExchangeRMB)'SalesVatFeeUSD', sum(ISNULL(alias.CustomFeeOtherCostPrice,0))'CustomFeeOtherCostPriceRMB', sum(ISNULL(alias.CustomFeeOtherCostPrice,0)/alias.USD_ExchangeRMB)'CustomFeeOtherCostPriceUSD', sum(alias.FirstLegBreakageFee)'FirstLegBreakageFeeRMB', sum(alias.FirstLegBreakageFee/alias.USD_ExchangeRMB)'FirstLegBreakageFeeUSD', COUNT(distinct alias.OrderCode)OrderNum, SUM(CASE WHEN alias.SalesPrice!=0 THEN alias.ProductNum+alias.ProdutNumUpdate ELSE 0 END)ProdutNum , alias.Idx From( select alias.*,3 AliExpressTrusteeType, case (alias.OrderCode) when '' then 0 else case when alias.SalesRefunds=0 then ROW_NUMBER () OVER (PARTITION BY alias.OrderCode ORDER BY alias.BusinessAdminID DESC,alias.SalesRefunds DESC) else 0 end end Idx from RB_Report_OrderSettlement alias(nolock) join #tempQ(NOLOCK) q on q.OrderSourceID=alias.OrderSourceID and q.StatisticDate=alias.ShipDate where alias.CustomerID=1 and alias.OrderSourceID in(select OrderSourceID from #tempQ) and alias.OrderSourceType in(1) AND ISNULL(alias.PromotionType,0)=0 AND (alias.OrderSourceType!=1 or ( alias.OrderSourceType=1 and (alias.CostOfAdvertising<>0 and alias.SettlementDate )alias group by alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end,alias.WareHouseID, ISNULL(alias.DeliveryTypeDsec, ''), alias.OrderSourceContry,convert(varchar(10), alias.ShipDate,120),alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0),ISNULL(alias.SalesTeamID,0),convert(varchar(10),alias.SettlementDate,120),alias.Idx UNION ALL select 1'是否区分发票',convert(varchar(10), alias.SettlementDate,120) DataDate,alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0)SalesAdminID,ISNULL(alias.SalesTeamID,0)SalesTeamID,convert(varchar(10),alias.SettlementDate,120)SettlementDate,alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end AddOrderReasonType, alias.WareHouseID,ISNULL(alias.DeliveryTypeDsec, '')DeliveryTypeDsec, 3 AliExpressTrusteeType, alias.OrderSourceContry, sum(Profits/alias.USD_ExchangeRMB)'二毛USD', sum(Profits)'二毛RMB', sum((Profits+isnull(BreakageFee,0))/alias.USD_ExchangeRMB)'二点五毛USD', sum(Profits+isnull(BreakageFee,0))'二点五毛RMB', sum(0)'订单数', sum(SalesPrice/alias.USD_ExchangeRMB)'销售额USD', sum(SalesPrice)'销售额RMB', sum(0)'销售数量', sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0)/alias.USD_ExchangeRMB)'销售收入USD', sum(alias.TransportPay/alias.USD_ExchangeRMB)'运费收入USD', sum(alias.SalesRefunds/alias.USD_ExchangeRMB)'退款金额USD', sum(alias.PromotionRebates/alias.USD_ExchangeRMB)'折扣USD', sum(alias.OtherCredits/alias.USD_ExchangeRMB)'其他收入USD', sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0))/alias.USD_ExchangeRMB)'税费收入USD', sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0))'销售收入RMB', sum(alias.TransportPay)'运费收入RMB', sum(alias.SalesRefunds)'退款金额RMB', sum(alias.PromotionRebates)'折扣RMB', sum(alias.OtherCredits)'其他收入RMB', sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)))'税费收入RMB', sum(alias.CommisionFee/alias.USD_ExchangeRMB)'佣金USD', sum(alias.FBATransactionFees/alias.USD_ExchangeRMB)'FBA操作费USD', sum(alias.OrderSourceOtherDebits/alias.USD_ExchangeRMB)'平台其他支出USD', sum(alias.CostOfAdvertising/alias.USD_ExchangeRMB)'广告费USD', sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0))/alias.USD_ExchangeRMB)'采购成本USD', sum(ISNULL(alias.TaxRefundFee,0)/alias.USD_ExchangeRMB) '退还税费USD', sum(alias.PackagedCost/alias.USD_ExchangeRMB)'包装成本USD', sum(ISNULL(alias.SubsidyPrice,0)/alias.USD_ExchangeRMB)'补贴调整USD', sum(alias.PackageFees/alias.USD_ExchangeRMB)'本地仓运费USD', sum(alias.OverSeasShipFees/alias.USD_ExchangeRMB)'海外仓运费USD', sum(alias.FirstLegFees/alias.USD_ExchangeRMB)'头程运费USD', sum(alias.WareHouseManagementPrice/alias.USD_ExchangeRMB)'仓库处理成本USD', sum(alias.TariffFees/alias.USD_ExchangeRMB)'税费USD', sum(alias.ExchangeLost/alias.USD_ExchangeRMB)'汇率折损USD', sum(ISNULL(alias.CustomStorageFee,0)/alias.USD_ExchangeRMB)'海外仓仓租USD', sum(ISNULL(alias.ImportFee,0)/alias.USD_ExchangeRMB)'人工导入费USD', sum(ISNULL(alias.BreakageFee,0)/alias.USD_ExchangeRMB)'报损费USD', sum(alias.CommisionFee)'佣金RMB', sum(alias.FBATransactionFees)'FBA操作费RMB', sum(alias.OrderSourceOtherDebits)'平台其他支出RMB', sum(alias.CostOfAdvertising)'广告费RMB', sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0)))'采购成本RMB', sum(ISNULL(alias.TaxRefundFee,0)) '退还税费RMB', sum(alias.PackagedCost)'包装成本RMB', sum(ISNULL(alias.SubsidyPrice,0))'补贴调整RMB', sum(alias.PackageFees)'本地仓运费RMB', sum(alias.OverSeasShipFees)'海外仓运费RMB', sum(alias.FirstLegFees)'头程运费RMB', sum(alias.WareHouseManagementPrice)'仓库处理成本RMB', sum(alias.TariffFees)'税费RMB', sum(alias.ExchangeLost)'汇率折损RMB', sum(ISNULL(alias.CustomStorageFee,0))'海外仓仓租RMB', sum(ISNULL(alias.ImportFee,0))'人工导入费RMB', sum(ISNULL(alias.BreakageFee,0))'报损费RMB', sum(ISNULL(alias.AffiliatePartnerCommissionFee,0))'AffiliatePartnerCommissionFeeRMB', sum(ISNULL(alias.AffiliatePartnerCommissionFee,0)/alias.USD_ExchangeRMB)'AffiliatePartnerCommissionFeeUSD', ABS(SUM((CASE WHEN ISNULL(alias.SalesRefunds,0)=0 THEN 0 ELSE ISNULL(alias.SalesRefunds,0)+ISNULL(alias.PromotionRebates,0)+ISNULL(alias.OtherCredits,0)+ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0) END))) 'SalesRefundsV2RMB', ABS(SUM((CASE WHEN ISNULL(alias.SalesRefunds,0)=0 THEN 0 ELSE ISNULL(alias.SalesRefunds,0)+ISNULL(alias.PromotionRebates,0)+ISNULL(alias.OtherCredits,0)+ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0) END)/alias.USD_ExchangeRMB)) 'SalesRefundsV2USD', sum(ISNULL(alias.SalesVatFee,0))'SalesVatFeeRMB', sum(ISNULL(alias.SalesVatFee,0)/alias.USD_ExchangeRMB)'SalesVatFeeUSD', sum(ISNULL(alias.CustomFeeOtherCostPrice,0))'CustomFeeOtherCostPriceRMB', sum(ISNULL(alias.CustomFeeOtherCostPrice,0)/alias.USD_ExchangeRMB)'CustomFeeOtherCostPriceUSD', sum(alias.FirstLegBreakageFee)'FirstLegBreakageFeeRMB', sum(alias.FirstLegBreakageFee/alias.USD_ExchangeRMB)'FirstLegBreakageFeeUSD', COUNT(distinct alias.OrderCode)OrderNum, SUM(CASE WHEN alias.SalesPrice!=0 THEN alias.ProductNum+alias.ProdutNumUpdate ELSE 0 END)ProdutNum , alias.Idx From( select alias.*,3 AliExpressTrusteeType, case (alias.OrderCode) when '' then 0 else case when alias.SalesRefunds=0 then ROW_NUMBER () OVER (PARTITION BY alias.OrderCode ORDER BY alias.BusinessAdminID DESC,alias.SalesRefunds DESC) else 0 end end Idx from RB_Report_OrderSettlement alias(nolock) join #tempQ(NOLOCK) q on q.OrderSourceID=alias.OrderSourceID and q.StatisticDate=convert(varchar(10), alias.SettlementDate,120) where alias.CustomerID=1 and alias.OrderSourceID in(select OrderSourceID from #tempQ) and alias.OrderSourceType in(1) AND ISNULL(alias.PromotionType,0)=1 AND (alias.OrderSourceType!=1 or ( alias.OrderSourceType=1 and (alias.CostOfAdvertising<>0 and alias.SettlementDate )alias group by alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end,alias.WareHouseID, ISNULL(alias.DeliveryTypeDsec, ''), alias.OrderSourceContry,convert(varchar(10), alias.SettlementDate,120),alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0),ISNULL(alias.SalesTeamID,0),convert(varchar(10),alias.SettlementDate,120),alias.Idx UNION ALL select 2 '是否区分发票',convert(varchar(10), alias.SettlementDate,120) DataDate,alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0)SalesAdminID,ISNULL(alias.SalesTeamID,0)SalesTeamID,convert(varchar(10),alias.SettlementDate,120) SettlementDate,alias.OrderSourceType,alias.OrderSourceID,'否'AddOrderReasonType, alias.WareHouseID,ISNULL(alias.DeliveryTypeDsec, '')DeliveryTypeDsec, 3 AliExpressTrusteeType, alias.OrderSourceContry, sum(alias.AdCost*alias.ExchangeRMB/alias.USD_ExchangeRMB)'二毛USD', sum(alias.AdCost*alias.ExchangeRMB)'二毛RMB', sum((alias.AdCost*alias.ExchangeRMB-0)/alias.USD_ExchangeRMB)'二点五毛USD', sum(alias.AdCost*alias.ExchangeRMB-0)'二点五毛RMB', sum(0)'订单数', 0'销售额USD', 0'销售额RMB', sum(0)'销售数量', 0'销售收入USD', 0'运费收入USD', 0'退款金额USD', 0'折扣USD', 0'其他收入USD', 0'税费收入USD', 0'销售收入RMB', 0'运费收入RMB', 0'退款金额RMB', 0'折扣RMB', 0'其他收入RMB', 0'税费收入RMB', 0'佣金USD', 0'FBA操作费USD', 0'平台其他支出USD', sum(alias.AdCost*alias.ExchangeRMB/alias.USD_ExchangeRMB)'广告费USD', 0'采购成本USD', 0'退还税费USD', 0'包装成本USD', 0'补贴调整USD', 0'本地仓运费USD', 0'海外仓运费USD', 0'头程运费USD', 0'仓库处理成本USD', 0'税费USD', 0'汇率折损USD', 0'海外仓仓租USD', 0'人工导入费USD', 0'报损费USD', 0'佣金RMB', 0'FBA操作费RMB', 0'平台其他支出RMB', sum(alias.AdCost*alias.ExchangeRMB)'广告费RMB', 0'采购成本RMB', 0 '退还税费RMB', 0'包装成本RMB', 0'补贴调整RMB', 0'本地仓运费RMB', 0'海外仓运费RMB', 0'头程运费RMB', 0'仓库处理成本RMB', 0'税费RMB', 0'汇率折损RMB', 0'海外仓仓租RMB', 0'人工导入费RMB', 0'报损费RMB',0 'AffiliatePartnerCommissionFeeRMB',0 'AffiliatePartnerCommissionFeeUSD',0 'SalesRefundsV2RMB',0 'SalesRefundsV2USD',0'SalesVatFeeRMB',0'SalesVatFeeUSD',0'CustomFeeOtherCostPriceRMB',0'CustomFeeOtherCostPriceUSD',0 'FirstLegBreakageFeeRMB',0 'FirstLegBreakageFeeUSD',0 'ProdutNum' ,0 'OrderNum',1 'Idx' from RB_Report_OrderSettlement_AdCost_Share alias(nolock) join #tempQ(NOLOCK) q on q.OrderSourceID=alias.OrderSourceID and q.StatisticDate=alias.SettlementDate where alias.CustomerID=1 and alias.OrderSourceID in(select OrderSourceID from #tempQ) ='2024-05-01' AND alias.IsBuildProfit=1 group by alias.OrderSourceType,alias.OrderSourceID,alias.WareHouseID, ISNULL(alias.DeliveryTypeDsec, ''), alias.OrderSourceContry,convert(varchar(10), alias.SettlementDate,120),alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0),ISNULL(alias.SalesTeamID,0),convert(varchar(10),alias.SettlementDate,120) UNION ALL select 2'是否区分发票',convert(varchar(10), alias.ShipDate,120) DataDate,alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0)SalesAdminID,ISNULL(alias.SalesTeamID,0)SalesTeamID,convert(varchar(10),alias.SettlementDate,120)SettlementDate,alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end AddOrderReasonType, alias.WareHouseID,ISNULL(alias.DeliveryTypeDsec, '')DeliveryTypeDsec, alias.AliExpressTrusteeType AliExpressTrusteeType, alias.OrderSourceContry, sum(Profits/alias.USD_ExchangeRMB)'二毛USD', sum(Profits)'二毛RMB', sum((Profits+isnull(BreakageFee,0))/alias.USD_ExchangeRMB)'二点五毛USD', sum(Profits+isnull(BreakageFee,0))'二点五毛RMB', sum(0)'订单数', sum(SalesPrice/alias.USD_ExchangeRMB)'销售额USD', sum(SalesPrice)'销售额RMB', sum(0)'销售数量', sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0)/alias.USD_ExchangeRMB)'销售收入USD', sum(alias.TransportPay/alias.USD_ExchangeRMB)'运费收入USD', sum(alias.SalesRefunds/alias.USD_ExchangeRMB)'退款金额USD', sum(alias.PromotionRebates/alias.USD_ExchangeRMB)'折扣USD', sum(alias.OtherCredits/alias.USD_ExchangeRMB)'其他收入USD', sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0))/alias.USD_ExchangeRMB)'税费收入USD', sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0))'销售收入RMB', sum(alias.TransportPay)'运费收入RMB', sum(alias.SalesRefunds)'退款金额RMB', sum(alias.PromotionRebates)'折扣RMB', sum(alias.OtherCredits)'其他收入RMB', sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)))'税费收入RMB', sum(alias.CommisionFee/alias.USD_ExchangeRMB)'佣金USD', sum(alias.FBATransactionFees/alias.USD_ExchangeRMB)'FBA操作费USD', sum(alias.OrderSourceOtherDebits/alias.USD_ExchangeRMB)'平台其他支出USD', sum(alias.CostOfAdvertising/alias.USD_ExchangeRMB)'广告费USD', sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0))/alias.USD_ExchangeRMB)'采购成本USD', sum(ISNULL(alias.TaxRefundFee,0)/alias.USD_ExchangeRMB) '退还税费USD', sum(alias.PackagedCost/alias.USD_ExchangeRMB)'包装成本USD', sum(ISNULL(alias.SubsidyPrice,0)/alias.USD_ExchangeRMB)'补贴调整USD', sum(alias.PackageFees/alias.USD_ExchangeRMB)'本地仓运费USD', sum(alias.OverSeasShipFees/alias.USD_ExchangeRMB)'海外仓运费USD', sum(alias.FirstLegFees/alias.USD_ExchangeRMB)'头程运费USD', sum(alias.WareHouseManagementPrice/alias.USD_ExchangeRMB)'仓库处理成本USD', sum(alias.TariffFees/alias.USD_ExchangeRMB)'税费USD', sum(alias.ExchangeLost/alias.USD_ExchangeRMB)'汇率折损USD', sum(ISNULL(alias.CustomStorageFee,0)/alias.USD_ExchangeRMB)'海外仓仓租USD', sum(ISNULL(alias.ImportFee,0)/alias.USD_ExchangeRMB)'人工导入费USD', sum(ISNULL(alias.BreakageFee,0)/alias.USD_ExchangeRMB)'报损费USD', sum(alias.CommisionFee)'佣金RMB', sum(alias.FBATransactionFees)'FBA操作费RMB', sum(alias.OrderSourceOtherDebits)'平台其他支出RMB', sum(alias.CostOfAdvertising)'广告费RMB', sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0)))'采购成本RMB', sum(ISNULL(alias.TaxRefundFee,0)) '退还税费RMB', sum(alias.PackagedCost)'包装成本RMB', sum(ISNULL(alias.SubsidyPrice,0))'补贴调整RMB', sum(alias.PackageFees)'本地仓运费RMB', sum(alias.OverSeasShipFees)'海外仓运费RMB', sum(alias.FirstLegFees)'头程运费RMB', sum(alias.WareHouseManagementPrice)'仓库处理成本RMB', sum(alias.TariffFees)'税费RMB', sum(alias.ExchangeLost)'汇率折损RMB', sum(ISNULL(alias.CustomStorageFee,0))'海外仓仓租RMB', sum(ISNULL(alias.ImportFee,0))'人工导入费RMB', sum(ISNULL(alias.BreakageFee,0))'报损费RMB', sum(ISNULL(alias.AffiliatePartnerCommissionFee,0))'AffiliatePartnerCommissionFeeRMB', sum(ISNULL(alias.AffiliatePartnerCommissionFee,0)/alias.USD_ExchangeRMB)'AffiliatePartnerCommissionFeeUSD', ABS(SUM((CASE WHEN ISNULL(alias.SalesRefunds,0)=0 THEN 0 ELSE ISNULL(alias.SalesRefunds,0)+ISNULL(alias.PromotionRebates,0)+ISNULL(alias.OtherCredits,0)+ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0) END))) 'SalesRefundsV2RMB', ABS(SUM((CASE WHEN ISNULL(alias.SalesRefunds,0)=0 THEN 0 ELSE ISNULL(alias.SalesRefunds,0)+ISNULL(alias.PromotionRebates,0)+ISNULL(alias.OtherCredits,0)+ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0) END)/alias.USD_ExchangeRMB)) 'SalesRefundsV2USD', sum(ISNULL(alias.SalesVatFee,0))'SalesVatFeeRMB', sum(ISNULL(alias.SalesVatFee,0)/alias.USD_ExchangeRMB)'SalesVatFeeUSD', sum(ISNULL(alias.CustomFeeOtherCostPrice,0))'CustomFeeOtherCostPriceRMB', sum(ISNULL(alias.CustomFeeOtherCostPrice,0)/alias.USD_ExchangeRMB)'CustomFeeOtherCostPriceUSD', sum(alias.FirstLegBreakageFee)'FirstLegBreakageFeeRMB', sum(alias.FirstLegBreakageFee/alias.USD_ExchangeRMB)'FirstLegBreakageFeeUSD', COUNT(distinct alias.OrderCode)OrderNum, SUM(CASE WHEN alias.SalesPrice!=0 THEN alias.ProductNum+alias.ProdutNumUpdate ELSE 0 END)ProdutNum , alias.Idx From( select alias.*,oi.AliExpressTrusteeType AliExpressTrusteeType, case (alias.OrderCode) when '' then 0 else case when alias.SalesRefunds=0 then ROW_NUMBER () OVER (PARTITION BY alias.OrderCode ORDER BY alias.BusinessAdminID DESC,alias.SalesRefunds DESC) else 0 end end Idx from RB_Report_OrderSettlement alias(nolock) join #tempQ(NOLOCK) q on q.OrderSourceID=alias.OrderSourceID and q.StatisticDate=alias.ShipDate left join RB_OrderInfo oi(NOLOCK) ON oi.CustomerID=alias.CustomerID AND oi.OrderSourceID=alias.OrderSourceID AND oi.OrderCode=alias.OrderCode where alias.CustomerID=1 and alias.OrderSourceID in(select OrderSourceID from #tempQ) and alias.OrderSourceType in(57) AND (alias.OrderSourceType!=1 or ( alias.OrderSourceType=1 and (alias.CostOfAdvertising<>0 and alias.SettlementDate )alias group by alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end,alias.WareHouseID, ISNULL(alias.DeliveryTypeDsec, ''), alias.AliExpressTrusteeType, alias.OrderSourceContry,convert(varchar(10), alias.ShipDate,120),alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0),ISNULL(alias.SalesTeamID,0),convert(varchar(10),alias.SettlementDate,120),alias.Idx UNION ALL select 2'是否区分发票',convert(varchar(10), alias.ShipDate,120) DataDate,alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0)SalesAdminID,ISNULL(alias.SalesTeamID,0)SalesTeamID,convert(varchar(10),alias.SettlementDate,120)SettlementDate,alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end AddOrderReasonType, alias.WareHouseID,ISNULL(alias.DeliveryTypeDsec, '')DeliveryTypeDsec, 3 AliExpressTrusteeType, alias.OrderSourceContry, sum(Profits/alias.USD_ExchangeRMB)'二毛USD', sum(Profits)'二毛RMB', sum((Profits+isnull(BreakageFee,0))/alias.USD_ExchangeRMB)'二点五毛USD', sum(Profits+isnull(BreakageFee,0))'二点五毛RMB', sum(0)'订单数', sum(SalesPrice/alias.USD_ExchangeRMB)'销售额USD', sum(SalesPrice)'销售额RMB', sum(0)'销售数量', sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0)/alias.USD_ExchangeRMB)'销售收入USD', sum(alias.TransportPay/alias.USD_ExchangeRMB)'运费收入USD', sum(alias.SalesRefunds/alias.USD_ExchangeRMB)'退款金额USD', sum(alias.PromotionRebates/alias.USD_ExchangeRMB)'折扣USD', sum(alias.OtherCredits/alias.USD_ExchangeRMB)'其他收入USD', sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0))/alias.USD_ExchangeRMB)'税费收入USD', sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0))'销售收入RMB', sum(alias.TransportPay)'运费收入RMB', sum(alias.SalesRefunds)'退款金额RMB', sum(alias.PromotionRebates)'折扣RMB', sum(alias.OtherCredits)'其他收入RMB', sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)))'税费收入RMB', sum(alias.CommisionFee/alias.USD_ExchangeRMB)'佣金USD', sum(alias.FBATransactionFees/alias.USD_ExchangeRMB)'FBA操作费USD', sum(alias.OrderSourceOtherDebits/alias.USD_ExchangeRMB)'平台其他支出USD', sum(alias.CostOfAdvertising/alias.USD_ExchangeRMB)'广告费USD', sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0))/alias.USD_ExchangeRMB)'采购成本USD', sum(ISNULL(alias.TaxRefundFee,0)/alias.USD_ExchangeRMB) '退还税费USD', sum(alias.PackagedCost/ali
001-<p>[步骤]</p>
001+ <p><a href="https://alidocs.dingtalk.com/i/nodes/P7QG4Yx2Jp7PMwD5hYr54MPgV9dEq3XD">https://alidocs.dingtalk.com/i/nodes/P7QG4Yx2Jp7PMwD5hYr54MPgV9dEq3XD</a></p>
002-IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'tempdb..#tempQ') and type='U') <br /><br /> BEGIN<br /><br /> DROP TABLE #tempQ<br /><br /> END<br /><br /> <br /><br /> IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'tempdb..#tempAll') and type='U') <br /><br /> BEGIN<br /><br /> DROP TABLE #tempAll<br /><br /> END<br /><br /><br /><br /> select OrderSourceID, StatisticDate into #tempQ from RB_Statistic_Queue(NOLOCK)q where q.CustomerID=1 and StatisticType='OrderProfitStaDimDay' and IsProcess = 0 and ProcessMessage='1' ;CREATE INDEX IX_TempTable_ID ON #tempQ (OrderSourceID,StatisticDate);select g1.* into #tempAll From( <br /><br /> select 1 CustomerID, '' HashKey,Getdate()AddTime,0 AllAmountRMB,<br /><br /> tt1.是否区分发票 PromotionType,convert(date,tt1.DataDate)DataDate,1 DataDim,tt1.BusinessAdminID,tt1.BusinessTeamID,tt1.SalesAdminID,tt1.SalesTeamID,tt1.SettlementDate,tt1.OrderSourceType,tt1.OrderSourceID,tt1.AddOrderReasonType,tt1.WareHouseID,tt1.DeliveryTypeDsec,isnull(tt1.AliExpressTrusteeType,8)AliExpressTrusteeType,<br /><br /> tt1.OrderSourceContry,<br /><br /> sum(isnull(二毛USD,0))'ProfitsUSD', --二毛USD<br /><br /> sum(isnull(二毛RMB,0))'ProfitsRMB',-- 二毛RMB<br /><br /> sum(isnull(二点五毛USD,0))'ProfitsTwoFiveUSD',-- 二点五毛USD<br /><br /> sum(isnull(二点五毛RMB,0))'ProfitsTwoFiveRMB',-- 二点五毛RMB<br /><br /> sum(isnull(销售额USD,0))'SalesPriceUSD',-- 销售额USD<br /><br /> sum(isnull(销售额RMB,0))'SalesPriceRMB',-- 销售额RMB<br /><br /> sum(isnull(销售收入USD,0))'SalesIncomeUSD',-- 销售收入USD<br /><br /> sum(isnull(运费收入USD,0))'TransportPayUSD',-- 运费收入USD<br /><br /> sum(isnull(退款金额USD,0))'SalesRefundsUSD',-- 退款金额USD<br /><br /> sum(isnull(折扣USD,0))'PromotionRebatesUSD',-- 折扣USD<br /><br /> sum(isnull(其他收入USD,0))'OtherCreditsUSD',-- 其他收入USD<br /><br /> sum(isnull(税费收入USD,0))'TaxUSD',-- 税费收入USD<br /><br /> sum(isnull(销售收入RMB,0))'SalesIncomeRMB',-- 销售收入RMB<br /><br /> sum(isnull(运费收入RMB,0))'TransportPayRMB',-- 运费收入RMB<br /><br /> sum(isnull(退款金额RMB,0))'SalesRefundsRMB',-- 退款金额RMB<br /><br /> sum(isnull(折扣RMB,0))'PromotionRebatesRMB',-- 折扣RMB<br /><br /> sum(isnull(其他收入RMB,0))'OtherCreditsRMB',-- 其他收入RMB<br /><br /> sum(isnull(税费收入RMB,0))'TaxRMB',-- 税费收入RMB<br /><br /> sum(isnull(佣金USD,0))'CommisionFeeUSD',-- 佣金USD<br /><br /> sum(isnull(FBA操作费USD,0))'FBATransactionFeesUSD',-- FBA操作费USD<br /><br /> sum(isnull(平台其他支出USD,0))'OrderSourceOtherDebitsUSD',-- 平台其他支出USD<br /><br /> sum(isnull(广告费USD,0))'CostOfAdvertisingUSD',-- 广告费USD<br /><br /> sum(isnull(采购成本USD,0))'BuyPriceUSD',-- 采购成本USD<br /><br /> sum(isnull(退还税费USD,0))'TaxRefundFeeUSD',-- 退还税费USD<br /><br /> sum(isnull(包装成本USD,0))'PackagedCostUSD',-- 包装成本USD<br /><br /> sum(isnull(补贴调整USD,0))'SubsidyPriceUSD',-- 补贴调整USD<br /><br /> sum(isnull(本地仓运费USD,0))'PackageFeesUSD',-- 本地仓运费USD<br /><br /> sum(isnull(海外仓运费USD,0))'OverSeasShipFeesUSD',-- 海外仓运费USD<br /><br /> sum(isnull(头程运费USD,0))'FirstLegFeesUSD',-- 头程运费USD<br /><br /> sum(isnull(仓库处理成本USD,0))'WareHouseManagementPriceUSD',-- 仓库处理成本USD<br /><br /> sum(isnull(税费USD,0))'TariffFeesUSD',-- 税费USD<br /><br /> sum(isnull(汇率折损USD,0))'ExchangeLostUSD',-- 汇率折损USD<br /><br /> sum(isnull(海外仓仓租USD,0))'CustomStorageFeeUSD',-- 海外仓仓租USD<br /><br /> sum(isnull(人工导入费USD,0))'ImportFeeUSD',-- 人工导入费USD<br /><br /> sum(isnull(报损费USD,0))'BreakageFeeUSD',--报损费USD<br /><br /> sum(isnull(佣金RMB,0))'CommisionFeeRMB',-- 佣金RMB<br /><br /> sum(isnull(FBA操作费RMB,0))'FBATransactionFeesRMB',-- FBA操作费RMB<br /><br /> sum(isnull(平台其他支出RMB,0))'OrderSourceOtherDebitsRMB',-- 平台其他支出RMB<br /><br /> sum(isnull(广告费RMB,0))'CostOfAdvertisingRMB',-- 广告费RMB<br /><br /> sum(isnull(采购成本RMB,0))'BuyPriceRMB',-- 采购成本RMB<br /><br /> sum(isnull(退还税费RMB,0))'TaxRefundFeeRMB',-- 退还税费RMB<br /><br /> sum(isnull(包装成本RMB,0))'PackagedCostRMB',-- 包装成本RMB<br /><br /> sum(isnull(补贴调整RMB,0))'SubsidyPriceRMB',-- 补贴调整RMB<br /><br /> sum(isnull(本地仓运费RMB,0))'PackageFeesRMB',-- 本地仓运费RMB<br /><br /> sum(isnull(海外仓运费RMB,0))'OverSeasShipFeesRMB',-- 海外仓运费RMB<br /><br /> sum(isnull(头程运费RMB,0))'FirstLegFeesRMB',-- 头程运费RMB<br /><br /> sum(isnull(仓库处理成本RMB,0))'WareHouseManagementPriceRMB',-- 仓库处理成本RMB<br /><br /> sum(isnull(税费RMB,0))'TariffFeesRMB',-- 税费RMB<br /><br /> sum(isnull(汇率折损RMB,0))'ExchangeLostRMB',-- 汇率折损RMB<br /><br /> sum(isnull(海外仓仓租RMB,0))'CustomStorageFeeRMB',-- 海外仓仓租RMB<br /><br /> sum(isnull(人工导入费RMB,0))'ImportFeeRMB',-- 人工导入费RMB<br /><br /> sum(isnull(报损费RMB,0))'BreakageFeeRMB',-- 报损费RMB,<br /><br /> sum(isnull(AffiliatePartnerCommissionFeeRMB,0))'AffiliatePartnerCommissionFeeRMB',<br /><br /> sum(isnull(AffiliatePartnerCommissionFeeUSD,0))'AffiliatePartnerCommissionFeeUSD',<br /><br /> sum(isnull(SalesRefundsV2RMB,0))'SalesRefundsV2RMB',<br /><br /> sum(isnull(SalesRefundsV2USD,0))'SalesRefundsV2USD',<br /><br /><br /><br /> sum(isnull(SalesVatFeeRMB,0))'SalesVatFeeRMB',<br /><br /> sum(isnull(SalesVatFeeUSD,0))'SalesVatFeeUSD',<br /><br /> sum(isnull(CustomFeeOtherCostPriceRMB,0))'CustomFeeOtherCostPriceRMB',<br /><br /> sum(isnull(CustomFeeOtherCostPriceUSD,0))'CustomFeeOtherCostPriceUSD',<br /><br /><br /><br /> sum(FirstLegBreakageFeeRMB)FirstLegBreakageFeeRMB,<br /><br /> sum(FirstLegBreakageFeeUSD)FirstLegBreakageFeeUSD,<br /><br /> sum(OrderNum)OrderNum,<br /><br /> sum(ProdutNum)ProdutNum ,<br /><br /> tt1.Idx<br /><br /><br /><br /> From (<br /><br /> select 1'是否区分发票',convert(varchar(10), alias.ShipDate,120) DataDate,alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0)SalesAdminID,ISNULL(alias.SalesTeamID,0)SalesTeamID,convert(varchar(10),alias.SettlementDate,120)SettlementDate,alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end AddOrderReasonType,<br /><br /> alias.WareHouseID,ISNULL(alias.DeliveryTypeDsec, '')DeliveryTypeDsec,<br /><br /> 3 AliExpressTrusteeType,<br /><br /> alias.OrderSourceContry,<br /><br /> sum(Profits/alias.USD_ExchangeRMB)'二毛USD',<br /><br /> sum(Profits)'二毛RMB',<br /><br /> sum((Profits+isnull(BreakageFee,0))/alias.USD_ExchangeRMB)'二点五毛USD',<br /><br /> sum(Profits+isnull(BreakageFee,0))'二点五毛RMB',<br /><br /> sum(0)'订单数',<br /><br /> sum(SalesPrice/alias.USD_ExchangeRMB)'销售额USD',<br /><br /> sum(SalesPrice)'销售额RMB',<br /><br /> sum(0)'销售数量',<br /><br /> sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0)/alias.USD_ExchangeRMB)'销售收入USD',<br /><br /> sum(alias.TransportPay/alias.USD_ExchangeRMB)'运费收入USD',<br /><br /> sum(alias.SalesRefunds/alias.USD_ExchangeRMB)'退款金额USD',<br /><br /> sum(alias.PromotionRebates/alias.USD_ExchangeRMB)'折扣USD',<br /><br /> sum(alias.OtherCredits/alias.USD_ExchangeRMB)'其他收入USD',<br /><br /> sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0))/alias.USD_ExchangeRMB)'税费收入USD',<br /><br /><br /><br /> sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0))'销售收入RMB',<br /><br /> sum(alias.TransportPay)'运费收入RMB',<br /><br /> sum(alias.SalesRefunds)'退款金额RMB',<br /><br /> sum(alias.PromotionRebates)'折扣RMB',<br /><br /> sum(alias.OtherCredits)'其他收入RMB',<br /><br /> sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)))'税费收入RMB',<br /><br /><br /><br /> sum(alias.CommisionFee/alias.USD_ExchangeRMB)'佣金USD',<br /><br /> sum(alias.FBATransactionFees/alias.USD_ExchangeRMB)'FBA操作费USD',<br /><br /> sum(alias.OrderSourceOtherDebits/alias.USD_ExchangeRMB)'平台其他支出USD',<br /><br /> sum(alias.CostOfAdvertising/alias.USD_ExchangeRMB)'广告费USD',<br /><br /> sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0))/alias.USD_ExchangeRMB)'采购成本USD',<br /><br /> sum(ISNULL(alias.TaxRefundFee,0)/alias.USD_ExchangeRMB) '退还税费USD',<br /><br /> sum(alias.PackagedCost/alias.USD_ExchangeRMB)'包装成本USD',<br /><br /> sum(ISNULL(alias.SubsidyPrice,0)/alias.USD_ExchangeRMB)'补贴调整USD',<br /><br /> sum(alias.PackageFees/alias.USD_ExchangeRMB)'本地仓运费USD',<br /><br /> sum(alias.OverSeasShipFees/alias.USD_ExchangeRMB)'海外仓运费USD',<br /><br /> sum(alias.FirstLegFees/alias.USD_ExchangeRMB)'头程运费USD',<br /><br /> sum(alias.WareHouseManagementPrice/alias.USD_ExchangeRMB)'仓库处理成本USD',<br /><br /> sum(alias.TariffFees/alias.USD_ExchangeRMB)'税费USD',<br /><br /> sum(alias.ExchangeLost/alias.USD_ExchangeRMB)'汇率折损USD',<br /><br /> sum(ISNULL(alias.CustomStorageFee,0)/alias.USD_ExchangeRMB)'海外仓仓租USD',<br /><br /> sum(ISNULL(alias.ImportFee,0)/alias.USD_ExchangeRMB)'人工导入费USD',<br /><br /> sum(ISNULL(alias.BreakageFee,0)/alias.USD_ExchangeRMB)'报损费USD',<br /><br /><br /><br /> sum(alias.CommisionFee)'佣金RMB',<br /><br /> sum(alias.FBATransactionFees)'FBA操作费RMB',<br /><br /> sum(alias.OrderSourceOtherDebits)'平台其他支出RMB',<br /><br /> sum(alias.CostOfAdvertising)'广告费RMB',<br /><br /> sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0)))'采购成本RMB',<br /><br /> sum(ISNULL(alias.TaxRefundFee,0)) '退还税费RMB',<br /><br /> sum(alias.PackagedCost)'包装成本RMB',<br /><br /> sum(ISNULL(alias.SubsidyPrice,0))'补贴调整RMB',<br /><br /> sum(alias.PackageFees)'本地仓运费RMB',<br /><br /> sum(alias.OverSeasShipFees)'海外仓运费RMB',<br /><br /> sum(alias.FirstLegFees)'头程运费RMB',<br /><br /> sum(alias.WareHouseManagementPrice)'仓库处理成本RMB',<br /><br /> sum(alias.TariffFees)'税费RMB',<br /><br /> sum(alias.ExchangeLost)'汇率折损RMB',<br /><br /> sum(ISNULL(alias.CustomStorageFee,0))'海外仓仓租RMB',<br /><br /> sum(ISNULL(alias.ImportFee,0))'人工导入费RMB',<br /><br /> sum(ISNULL(alias.BreakageFee,0))'报损费RMB',<br /><br /> <br /><br /> sum(ISNULL(alias.AffiliatePartnerCommissionFee,0))'AffiliatePartnerCommissionFeeRMB',<br /><br /> sum(ISNULL(alias.AffiliatePartnerCommissionFee,0)/alias.USD_ExchangeRMB)'AffiliatePartnerCommissionFeeUSD',<br /><br /> ABS(SUM((CASE WHEN ISNULL(alias.SalesRefunds,0)=0 THEN 0 ELSE ISNULL(alias.SalesRefunds,0)+ISNULL(alias.PromotionRebates,0)+ISNULL(alias.OtherCredits,0)+ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0) END))) 'SalesRefundsV2RMB',<br /><br /> ABS(SUM((CASE WHEN ISNULL(alias.SalesRefunds,0)=0 THEN 0 ELSE ISNULL(alias.SalesRefunds,0)+ISNULL(alias.PromotionRebates,0)+ISNULL(alias.OtherCredits,0)+ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0) END)/alias.USD_ExchangeRMB)) 'SalesRefundsV2USD',<br /><br /><br /><br /> sum(ISNULL(alias.SalesVatFee,0))'SalesVatFeeRMB',<br /><br /> sum(ISNULL(alias.SalesVatFee,0)/alias.USD_ExchangeRMB)'SalesVatFeeUSD',<br /><br /> sum(ISNULL(alias.CustomFeeOtherCostPrice,0))'CustomFeeOtherCostPriceRMB',<br /><br /> sum(ISNULL(alias.CustomFeeOtherCostPrice,0)/alias.USD_ExchangeRMB)'CustomFeeOtherCostPriceUSD',<br /><br /><br /><br /> sum(alias.FirstLegBreakageFee)'FirstLegBreakageFeeRMB',<br /><br /> sum(alias.FirstLegBreakageFee/alias.USD_ExchangeRMB)'FirstLegBreakageFeeUSD',<br /><br /> COUNT(distinct alias.OrderCode)OrderNum,<br /><br /> SUM(CASE WHEN alias.SalesPrice!=0 THEN alias.ProductNum+alias.ProdutNumUpdate ELSE 0 END)ProdutNum ,<br /><br /> alias.Idx<br /><br /><br /><br /> From(<br /><br /> select alias.*,3 AliExpressTrusteeType,<br /><br /> case (alias.OrderCode) when '' then 0 <br /><br /> else <br /><br /> case when alias.SalesRefunds=0 then ROW_NUMBER () OVER (PARTITION BY alias.OrderCode ORDER BY alias.BusinessAdminID DESC,alias.SalesRefunds DESC)<br /><br /> else 0 <br /><br /> end <br /><br /> end Idx<br /><br /> from RB_Report_OrderSettlement alias(nolock)<br /><br /> join #tempQ(NOLOCK) q on q.OrderSourceID=alias.OrderSourceID and q.StatisticDate=alias.ShipDate <br /><br /> <br /><br /> <br /><br /> where alias.CustomerID=1 and alias.OrderSourceID in(select OrderSourceID from #tempQ) <br /><br /> <br /><br /> and alias.OrderSourceType in(1) <br /><br /> AND ISNULL(alias.PromotionType,0)=0 <br /><br /> AND (alias.OrderSourceType!=1 or ( alias.OrderSourceType=1 and (alias.CostOfAdvertising<>0 and alias.SettlementDate<br /><br /> )alias<br /><br /><br /><br /> group by alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end,alias.WareHouseID,<br /><br /> ISNULL(alias.DeliveryTypeDsec, ''),<br /><br /> <br /><br /> alias.OrderSourceContry,convert(varchar(10), alias.ShipDate,120),alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0),ISNULL(alias.SalesTeamID,0),convert(varchar(10),alias.SettlementDate,120),alias.Idx <br /><br /> <br /><br /><br /><br />UNION ALL<br /><br /><br /><br /> select 1'是否区分发票',convert(varchar(10), alias.SettlementDate,120) DataDate,alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0)SalesAdminID,ISNULL(alias.SalesTeamID,0)SalesTeamID,convert(varchar(10),alias.SettlementDate,120)SettlementDate,alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end AddOrderReasonType,<br /><br /> alias.WareHouseID,ISNULL(alias.DeliveryTypeDsec, '')DeliveryTypeDsec,<br /><br /> 3 AliExpressTrusteeType,<br /><br /> alias.OrderSourceContry,<br /><br /> sum(Profits/alias.USD_ExchangeRMB)'二毛USD',<br /><br /> sum(Profits)'二毛RMB',<br /><br /> sum((Profits+isnull(BreakageFee,0))/alias.USD_ExchangeRMB)'二点五毛USD',<br /><br /> sum(Profits+isnull(BreakageFee,0))'二点五毛RMB',<br /><br /> sum(0)'订单数',<br /><br /> sum(SalesPrice/alias.USD_ExchangeRMB)'销售额USD',<br /><br /> sum(SalesPrice)'销售额RMB',<br /><br /> sum(0)'销售数量',<br /><br /> sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0)/alias.USD_ExchangeRMB)'销售收入USD',<br /><br /> sum(alias.TransportPay/alias.USD_ExchangeRMB)'运费收入USD',<br /><br /> sum(alias.SalesRefunds/alias.USD_ExchangeRMB)'退款金额USD',<br /><br /> sum(alias.PromotionRebates/alias.USD_ExchangeRMB)'折扣USD',<br /><br /> sum(alias.OtherCredits/alias.USD_ExchangeRMB)'其他收入USD',<br /><br /> sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0))/alias.USD_ExchangeRMB)'税费收入USD',<br /><br /><br /><br /> sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0))'销售收入RMB',<br /><br /> sum(alias.TransportPay)'运费收入RMB',<br /><br /> sum(alias.SalesRefunds)'退款金额RMB',<br /><br /> sum(alias.PromotionRebates)'折扣RMB',<br /><br /> sum(alias.OtherCredits)'其他收入RMB',<br /><br /> sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)))'税费收入RMB',<br /><br /><br /><br /> sum(alias.CommisionFee/alias.USD_ExchangeRMB)'佣金USD',<br /><br /> sum(alias.FBATransactionFees/alias.USD_ExchangeRMB)'FBA操作费USD',<br /><br /> sum(alias.OrderSourceOtherDebits/alias.USD_ExchangeRMB)'平台其他支出USD',<br /><br /> sum(alias.CostOfAdvertising/alias.USD_ExchangeRMB)'广告费USD',<br /><br /> sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0))/alias.USD_ExchangeRMB)'采购成本USD',<br /><br /> sum(ISNULL(alias.TaxRefundFee,0)/alias.USD_ExchangeRMB) '退还税费USD',<br /><br /> sum(alias.PackagedCost/alias.USD_ExchangeRMB)'包装成本USD',<br /><br /> sum(ISNULL(alias.SubsidyPrice,0)/alias.USD_ExchangeRMB)'补贴调整USD',<br /><br /> sum(alias.PackageFees/alias.USD_ExchangeRMB)'本地仓运费USD',<br /><br /> sum(alias.OverSeasShipFees/alias.USD_ExchangeRMB)'海外仓运费USD',<br /><br /> sum(alias.FirstLegFees/alias.USD_ExchangeRMB)'头程运费USD',<br /><br /> sum(alias.WareHouseManagementPrice/alias.USD_ExchangeRMB)'仓库处理成本USD',<br /><br /> sum(alias.TariffFees/alias.USD_ExchangeRMB)'税费USD',<br /><br /> sum(alias.ExchangeLost/alias.USD_ExchangeRMB)'汇率折损USD',<br /><br /> sum(ISNULL(alias.CustomStorageFee,0)/alias.USD_ExchangeRMB)'海外仓仓租USD',<br /><br /> sum(ISNULL(alias.ImportFee,0)/alias.USD_ExchangeRMB)'人工导入费USD',<br /><br /> sum(ISNULL(alias.BreakageFee,0)/alias.USD_ExchangeRMB)'报损费USD',<br /><br /><br /><br /> sum(alias.CommisionFee)'佣金RMB',<br /><br /> sum(alias.FBATransactionFees)'FBA操作费RMB',<br /><br /> sum(alias.OrderSourceOtherDebits)'平台其他支出RMB',<br /><br /> sum(alias.CostOfAdvertising)'广告费RMB',<br /><br /> sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0)))'采购成本RMB',<br /><br /> sum(ISNULL(alias.TaxRefundFee,0)) '退还税费RMB',<br /><br /> sum(alias.PackagedCost)'包装成本RMB',<br /><br /> sum(ISNULL(alias.SubsidyPrice,0))'补贴调整RMB',<br /><br /> sum(alias.PackageFees)'本地仓运费RMB',<br /><br /> sum(alias.OverSeasShipFees)'海外仓运费RMB',<br /><br /> sum(alias.FirstLegFees)'头程运费RMB',<br /><br /> sum(alias.WareHouseManagementPrice)'仓库处理成本RMB',<br /><br /> sum(alias.TariffFees)'税费RMB',<br /><br /> sum(alias.ExchangeLost)'汇率折损RMB',<br /><br /> sum(ISNULL(alias.CustomStorageFee,0))'海外仓仓租RMB',<br /><br /> sum(ISNULL(alias.ImportFee,0))'人工导入费RMB',<br /><br /> sum(ISNULL(alias.BreakageFee,0))'报损费RMB',<br /><br /> <br /><br /> sum(ISNULL(alias.AffiliatePartnerCommissionFee,0))'AffiliatePartnerCommissionFeeRMB',<br /><br /> sum(ISNULL(alias.AffiliatePartnerCommissionFee,0)/alias.USD_ExchangeRMB)'AffiliatePartnerCommissionFeeUSD',<br /><br /> ABS(SUM((CASE WHEN ISNULL(alias.SalesRefunds,0)=0 THEN 0 ELSE ISNULL(alias.SalesRefunds,0)+ISNULL(alias.PromotionRebates,0)+ISNULL(alias.OtherCredits,0)+ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0) END))) 'SalesRefundsV2RMB',<br /><br /> ABS(SUM((CASE WHEN ISNULL(alias.SalesRefunds,0)=0 THEN 0 ELSE ISNULL(alias.SalesRefunds,0)+ISNULL(alias.PromotionRebates,0)+ISNULL(alias.OtherCredits,0)+ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0) END)/alias.USD_ExchangeRMB)) 'SalesRefundsV2USD',<br /><br /><br /><br /> sum(ISNULL(alias.SalesVatFee,0))'SalesVatFeeRMB',<br /><br /> sum(ISNULL(alias.SalesVatFee,0)/alias.USD_ExchangeRMB)'SalesVatFeeUSD',<br /><br /> sum(ISNULL(alias.CustomFeeOtherCostPrice,0))'CustomFeeOtherCostPriceRMB',<br /><br /> sum(ISNULL(alias.CustomFeeOtherCostPrice,0)/alias.USD_ExchangeRMB)'CustomFeeOtherCostPriceUSD',<br /><br /><br /><br /> sum(alias.FirstLegBreakageFee)'FirstLegBreakageFeeRMB',<br /><br /> sum(alias.FirstLegBreakageFee/alias.USD_ExchangeRMB)'FirstLegBreakageFeeUSD',<br /><br /> COUNT(distinct alias.OrderCode)OrderNum,<br /><br /> SUM(CASE WHEN alias.SalesPrice!=0 THEN alias.ProductNum+alias.ProdutNumUpdate ELSE 0 END)ProdutNum ,<br /><br /> alias.Idx<br /><br /><br /><br /> From(<br /><br /> select alias.*,3 AliExpressTrusteeType,<br /><br /> case (alias.OrderCode) when '' then 0 <br /><br /> else <br /><br /> case when alias.SalesRefunds=0 then ROW_NUMBER () OVER (PARTITION BY alias.OrderCode ORDER BY alias.BusinessAdminID DESC,alias.SalesRefunds DESC)<br /><br /> else 0 <br /><br /> end <br /><br /> end Idx<br /><br /> from RB_Report_OrderSettlement alias(nolock)<br /><br /> join #tempQ(NOLOCK) q on q.OrderSourceID=alias.OrderSourceID and q.StatisticDate=convert(varchar(10), alias.SettlementDate,120) <br /><br /> <br /><br /> <br /><br /> where alias.CustomerID=1 and alias.OrderSourceID in(select OrderSourceID from #tempQ) <br /><br /> <br /><br /> and alias.OrderSourceType in(1) <br /><br /> AND ISNULL(alias.PromotionType,0)=1 <br /><br /> AND (alias.OrderSourceType!=1 or ( alias.OrderSourceType=1 and (alias.CostOfAdvertising<>0 and alias.SettlementDate<br /><br /> )alias<br /><br /><br /><br /> group by alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end,alias.WareHouseID,<br /><br /> ISNULL(alias.DeliveryTypeDsec, ''),<br /><br /> <br /><br /> alias.OrderSourceContry,convert(varchar(10), alias.SettlementDate,120),alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0),ISNULL(alias.SalesTeamID,0),convert(varchar(10),alias.SettlementDate,120),alias.Idx <br /><br /> <br /><br /><br /><br />UNION ALL<br /><br /><br /><br /> select 2 '是否区分发票',convert(varchar(10), alias.SettlementDate,120) DataDate,alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0)SalesAdminID,ISNULL(alias.SalesTeamID,0)SalesTeamID,convert(varchar(10),alias.SettlementDate,120) SettlementDate,alias.OrderSourceType,alias.OrderSourceID,'否'AddOrderReasonType,<br /><br /> alias.WareHouseID,ISNULL(alias.DeliveryTypeDsec, '')DeliveryTypeDsec,<br /><br /> 3 AliExpressTrusteeType,<br /><br /> alias.OrderSourceContry,<br /><br /> sum(alias.AdCost*alias.ExchangeRMB/alias.USD_ExchangeRMB)'二毛USD',<br /><br /> sum(alias.AdCost*alias.ExchangeRMB)'二毛RMB',<br /><br /> sum((alias.AdCost*alias.ExchangeRMB-0)/alias.USD_ExchangeRMB)'二点五毛USD',<br /><br /> sum(alias.AdCost*alias.ExchangeRMB-0)'二点五毛RMB',<br /><br /> sum(0)'订单数',<br /><br /> 0'销售额USD',<br /><br /> 0'销售额RMB',<br /><br /> sum(0)'销售数量',<br /><br /> 0'销售收入USD',<br /><br /> 0'运费收入USD',<br /><br /> 0'退款金额USD',<br /><br /> 0'折扣USD',<br /><br /> 0'其他收入USD',<br /><br /> 0'税费收入USD',<br /><br /><br /><br /> 0'销售收入RMB',<br /><br /> 0'运费收入RMB',<br /><br /> 0'退款金额RMB',<br /><br /> 0'折扣RMB',<br /><br /> 0'其他收入RMB',<br /><br /> 0'税费收入RMB',<br /><br /><br /><br /> 0'佣金USD',<br /><br /> 0'FBA操作费USD',<br /><br /> 0'平台其他支出USD',<br /><br /> sum(alias.AdCost*alias.ExchangeRMB/alias.USD_ExchangeRMB)'广告费USD',<br /><br /> 0'采购成本USD',<br /><br /> 0'退还税费USD',<br /><br /> 0'包装成本USD',<br /><br /> 0'补贴调整USD',<br /><br /> 0'本地仓运费USD',<br /><br /> 0'海外仓运费USD',<br /><br /> 0'头程运费USD',<br /><br /> 0'仓库处理成本USD',<br /><br /> 0'税费USD',<br /><br /> 0'汇率折损USD',<br /><br /> 0'海外仓仓租USD',<br /><br /> 0'人工导入费USD',<br /><br /> 0'报损费USD',<br /><br /><br /><br /> 0'佣金RMB',<br /><br /> 0'FBA操作费RMB',<br /><br /> 0'平台其他支出RMB',<br /><br /> sum(alias.AdCost*alias.ExchangeRMB)'广告费RMB',<br /><br /> 0'采购成本RMB',<br /><br /> 0 '退还税费RMB',<br /><br /> 0'包装成本RMB',<br /><br /> 0'补贴调整RMB',<br /><br /> 0'本地仓运费RMB',<br /><br /> 0'海外仓运费RMB',<br /><br /> 0'头程运费RMB',<br /><br /> 0'仓库处理成本RMB',<br /><br /> 0'税费RMB',<br /><br /> 0'汇率折损RMB',<br /><br /> 0'海外仓仓租RMB',<br /><br /> 0'人工导入费RMB',<br /><br /> 0'报损费RMB',0 'AffiliatePartnerCommissionFeeRMB',0 'AffiliatePartnerCommissionFeeUSD',0 'SalesRefundsV2RMB',0 'SalesRefundsV2USD',0'SalesVatFeeRMB',0'SalesVatFeeUSD',0'CustomFeeOtherCostPriceRMB',0'CustomFeeOtherCostPriceUSD',0 'FirstLegBreakageFeeRMB',0 'FirstLegBreakageFeeUSD',0 'ProdutNum' ,0 'OrderNum',1 'Idx'<br /><br /> from RB_Report_OrderSettlement_AdCost_Share alias(nolock)<br /><br /> join #tempQ(NOLOCK) q on q.OrderSourceID=alias.OrderSourceID and q.StatisticDate=alias.SettlementDate <br /><br /> where alias.CustomerID=1 and alias.OrderSourceID in(select OrderSourceID from #tempQ) <br /><br />='2024-05-01' AND alias.IsBuildProfit=1 <br /><br /> group by alias.OrderSourceType,alias.OrderSourceID,alias.WareHouseID,<br /><br /> ISNULL(alias.DeliveryTypeDsec, ''),<br /><br /> alias.OrderSourceContry,convert(varchar(10), alias.SettlementDate,120),alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0),ISNULL(alias.SalesTeamID,0),convert(varchar(10),alias.SettlementDate,120)<br /><br /> <br /><br /><br /><br />UNION ALL<br /><br /><br /><br /> select 2'是否区分发票',convert(varchar(10), alias.ShipDate,120) DataDate,alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0)SalesAdminID,ISNULL(alias.SalesTeamID,0)SalesTeamID,convert(varchar(10),alias.SettlementDate,120)SettlementDate,alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end AddOrderReasonType,<br /><br /> alias.WareHouseID,ISNULL(alias.DeliveryTypeDsec, '')DeliveryTypeDsec,<br /><br /> alias.AliExpressTrusteeType AliExpressTrusteeType,<br /><br /> alias.OrderSourceContry,<br /><br /> sum(Profits/alias.USD_ExchangeRMB)'二毛USD',<br /><br /> sum(Profits)'二毛RMB',<br /><br /> sum((Profits+isnull(BreakageFee,0))/alias.USD_ExchangeRMB)'二点五毛USD',<br /><br /> sum(Profits+isnull(BreakageFee,0))'二点五毛RMB',<br /><br /> sum(0)'订单数',<br /><br /> sum(SalesPrice/alias.USD_ExchangeRMB)'销售额USD',<br /><br /> sum(SalesPrice)'销售额RMB',<br /><br /> sum(0)'销售数量',<br /><br /> sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0)/alias.USD_ExchangeRMB)'销售收入USD',<br /><br /> sum(alias.TransportPay/alias.USD_ExchangeRMB)'运费收入USD',<br /><br /> sum(alias.SalesRefunds/alias.USD_ExchangeRMB)'退款金额USD',<br /><br /> sum(alias.PromotionRebates/alias.USD_ExchangeRMB)'折扣USD',<br /><br /> sum(alias.OtherCredits/alias.USD_ExchangeRMB)'其他收入USD',<br /><br /> sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0))/alias.USD_ExchangeRMB)'税费收入USD',<br /><br /><br /><br /> sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0))'销售收入RMB',<br /><br /> sum(alias.TransportPay)'运费收入RMB',<br /><br /> sum(alias.SalesRefunds)'退款金额RMB',<br /><br /> sum(alias.PromotionRebates)'折扣RMB',<br /><br /> sum(alias.OtherCredits)'其他收入RMB',<br /><br /> sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)))'税费收入RMB',<br /><br /><br /><br /> sum(alias.CommisionFee/alias.USD_ExchangeRMB)'佣金USD',<br /><br /> sum(alias.FBATransactionFees/alias.USD_ExchangeRMB)'FBA操作费USD',<br /><br /> sum(alias.OrderSourceOtherDebits/alias.USD_ExchangeRMB)'平台其他支出USD',<br /><br /> sum(alias.CostOfAdvertising/alias.USD_ExchangeRMB)'广告费USD',<br /><br /> sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0))/alias.USD_ExchangeRMB)'采购成本USD',<br /><br /> sum(ISNULL(alias.TaxRefundFee,0)/alias.USD_ExchangeRMB) '退还税费USD',<br /><br /> sum(alias.PackagedCost/alias.USD_ExchangeRMB)'包装成本USD',<br /><br /> sum(ISNULL(alias.SubsidyPrice,0)/alias.USD_ExchangeRMB)'补贴调整USD',<br /><br /> sum(alias.PackageFees/alias.USD_ExchangeRMB)'本地仓运费USD',<br /><br /> sum(alias.OverSeasShipFees/alias.USD_ExchangeRMB)'海外仓运费USD',<br /><br /> sum(alias.FirstLegFees/alias.USD_ExchangeRMB)'头程运费USD',<br /><br /> sum(alias.WareHouseManagementPrice/alias.USD_ExchangeRMB)'仓库处理成本USD',<br /><br /> sum(alias.TariffFees/alias.USD_ExchangeRMB)'税费USD',<br /><br /> sum(alias.ExchangeLost/alias.USD_ExchangeRMB)'汇率折损USD',<br /><br /> sum(ISNULL(alias.CustomStorageFee,0)/alias.USD_ExchangeRMB)'海外仓仓租USD',<br /><br /> sum(ISNULL(alias.ImportFee,0)/alias.USD_ExchangeRMB)'人工导入费USD',<br /><br /> sum(ISNULL(alias.BreakageFee,0)/alias.USD_ExchangeRMB)'报损费USD',<br /><br /><br /><br /> sum(alias.CommisionFee)'佣金RMB',<br /><br /> sum(alias.FBATransactionFees)'FBA操作费RMB',<br /><br /> sum(alias.OrderSourceOtherDebits)'平台其他支出RMB',<br /><br /> sum(alias.CostOfAdvertising)'广告费RMB',<br /><br /> sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0)))'采购成本RMB',<br /><br /> sum(ISNULL(alias.TaxRefundFee,0)) '退还税费RMB',<br /><br /> sum(alias.PackagedCost)'包装成本RMB',<br /><br /> sum(ISNULL(alias.SubsidyPrice,0))'补贴调整RMB',<br /><br /> sum(alias.PackageFees)'本地仓运费RMB',<br /><br /> sum(alias.OverSeasShipFees)'海外仓运费RMB',<br /><br /> sum(alias.FirstLegFees)'头程运费RMB',<br /><br /> sum(alias.WareHouseManagementPrice)'仓库处理成本RMB',<br /><br /> sum(alias.TariffFees)'税费RMB',<br /><br /> sum(alias.ExchangeLost)'汇率折损RMB',<br /><br /> sum(ISNULL(alias.CustomStorageFee,0))'海外仓仓租RMB',<br /><br /> sum(ISNULL(alias.ImportFee,0))'人工导入费RMB',<br /><br /> sum(ISNULL(alias.BreakageFee,0))'报损费RMB',<br /><br /> <br /><br /> sum(ISNULL(alias.AffiliatePartnerCommissionFee,0))'AffiliatePartnerCommissionFeeRMB',<br /><br /> sum(ISNULL(alias.AffiliatePartnerCommissionFee,0)/alias.USD_ExchangeRMB)'AffiliatePartnerCommissionFeeUSD',<br /><br /> ABS(SUM((CASE WHEN ISNULL(alias.SalesRefunds,0)=0 THEN 0 ELSE ISNULL(alias.SalesRefunds,0)+ISNULL(alias.PromotionRebates,0)+ISNULL(alias.OtherCredits,0)+ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0) END))) 'SalesRefundsV2RMB',<br /><br /> ABS(SUM((CASE WHEN ISNULL(alias.SalesRefunds,0)=0 THEN 0 ELSE ISNULL(alias.SalesRefunds,0)+ISNULL(alias.PromotionRebates,0)+ISNULL(alias.OtherCredits,0)+ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0) END)/alias.USD_ExchangeRMB)) 'SalesRefundsV2USD',<br /><br /><br /><br /> sum(ISNULL(alias.SalesVatFee,0))'SalesVatFeeRMB',<br /><br /> sum(ISNULL(alias.SalesVatFee,0)/alias.USD_ExchangeRMB)'SalesVatFeeUSD',<br /><br /> sum(ISNULL(alias.CustomFeeOtherCostPrice,0))'CustomFeeOtherCostPriceRMB',<br /><br /> sum(ISNULL(alias.CustomFeeOtherCostPrice,0)/alias.USD_ExchangeRMB)'CustomFeeOtherCostPriceUSD',<br /><br /><br /><br /> sum(alias.FirstLegBreakageFee)'FirstLegBreakageFeeRMB',<br /><br /> sum(alias.FirstLegBreakageFee/alias.USD_ExchangeRMB)'FirstLegBreakageFeeUSD',<br /><br /> COUNT(distinct alias.OrderCode)OrderNum,<br /><br /> SUM(CASE WHEN alias.SalesPrice!=0 THEN alias.ProductNum+alias.ProdutNumUpdate ELSE 0 END)ProdutNum ,<br /><br /> alias.Idx<br /><br /><br /><br /> From(<br /><br /> select alias.*,oi.AliExpressTrusteeType AliExpressTrusteeType,<br /><br /> case (alias.OrderCode) when '' then 0 <br /><br /> else <br /><br /> case when alias.SalesRefunds=0 then ROW_NUMBER () OVER (PARTITION BY alias.OrderCode ORDER BY alias.BusinessAdminID DESC,alias.SalesRefunds DESC)<br /><br /> else 0 <br /><br /> end <br /><br /> end Idx<br /><br /> from RB_Report_OrderSettlement alias(nolock)<br /><br /> join #tempQ(NOLOCK) q on q.OrderSourceID=alias.OrderSourceID and q.StatisticDate=alias.ShipDate <br /><br /> left join RB_OrderInfo oi(NOLOCK) ON oi.CustomerID=alias.CustomerID AND oi.OrderSourceID=alias.OrderSourceID AND oi.OrderCode=alias.OrderCode<br /><br /> <br /><br /> where alias.CustomerID=1 and alias.OrderSourceID in(select OrderSourceID from #tempQ) <br /><br /> <br /><br /> and alias.OrderSourceType in(57) <br /><br /> <br /><br /> AND (alias.OrderSourceType!=1 or ( alias.OrderSourceType=1 and (alias.CostOfAdvertising<>0 and alias.SettlementDate<br /><br /> )alias<br /><br /><br /><br /> group by alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end,alias.WareHouseID,<br /><br /> ISNULL(alias.DeliveryTypeDsec, ''),<br /><br /> alias.AliExpressTrusteeType,<br /><br /> alias.OrderSourceContry,convert(varchar(10), alias.ShipDate,120),alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0),ISNULL(alias.SalesTeamID,0),convert(varchar(10),alias.SettlementDate,120),alias.Idx <br /><br /> <br /><br /><br /><br />UNION ALL<br /><br /><br /><br /> select 2'是否区分发票',convert(varchar(10), alias.ShipDate,120) DataDate,alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0)SalesAdminID,ISNULL(alias.SalesTeamID,0)SalesTeamID,convert(varchar(10),alias.SettlementDate,120)SettlementDate,alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end AddOrderReasonType,<br /><br /> alias.WareHouseID,ISNULL(alias.DeliveryTypeDsec, '')DeliveryTypeDsec,<br /><br /> 3 AliExpressTrusteeType,<br /><br /> alias.OrderSourceContry,<br /><br /> sum(Profits/alias.USD_ExchangeRMB)'二毛USD',<br /><br /> sum(Profits)'二毛RMB',<br /><br /> sum((Profits+isnull(BreakageFee,0))/alias.USD_ExchangeRMB)'二点五毛USD',<br /><br /> sum(Profits+isnull(BreakageFee,0))'二点五毛RMB',<br /><br /> sum(0)'订单数',<br /><br /> sum(SalesPrice/alias.USD_ExchangeRMB)'销售额USD',<br /><br /> sum(SalesPrice)'销售额RMB',<br /><br /> sum(0)'销售数量',<br /><br /> sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0)/alias.USD_ExchangeRMB)'销售收入USD',<br /><br /> sum(alias.TransportPay/alias.USD_ExchangeRMB)'运费收入USD',<br /><br /> sum(alias.SalesRefunds/alias.USD_ExchangeRMB)'退款金额USD',<br /><br /> sum(alias.PromotionRebates/alias.USD_ExchangeRMB)'折扣USD',<br /><br /> sum(alias.OtherCredits/alias.USD_ExchangeRMB)'其他收入USD',<br /><br /> sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0))/alias.USD_ExchangeRMB)'税费收入USD',<br /><br /><br /><br /> sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0))'销售收入RMB',<br /><br /> sum(alias.TransportPay)'运费收入RMB',<br /><br /> sum(alias.SalesRefunds)'退款金额RMB',<br /><br /> sum(alias.PromotionRebates)'折扣RMB',<br /><br /> sum(alias.OtherCredits)'其他收入RMB',<br /><br /> sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)))'税费收入RMB',<br /><br /><br /><br /> sum(alias.CommisionFee/alias.USD_ExchangeRMB)'佣金USD',<br /><br /> sum(alias.FBATransactionFees/alias.USD_ExchangeRMB)'FBA操作费USD',<br /><br /> sum(alias.OrderSourceOtherDebits/alias.USD_ExchangeRMB)'平台其他支出USD',<br /><br /> sum(alias.CostOfAdvertising/alias.USD_ExchangeRMB)'广告费USD',<br /><br /> sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0))/alias.USD_ExchangeRMB)'采购成本USD',<br /><br /> sum(ISNULL(alias.TaxRefundFee,0)/alias.USD_ExchangeRMB) '退还税费USD',<br /><br /> sum(alias.PackagedCost/ali
| 提报业务部门 | 财务管理中心 |
|---|---|
| 提报人员 | 林很 |
| 需求来源 | bug转需求 |
| 所属模块 | 财务 财务管理 |
| 所属计划 |
|
| 开发组 | 财务开发组 |
| 受益业务部门 | 技术研发中心 |
| 验收人员 | 许瑞丰 |
| 业务价值 | 控制风险 |
| 每月价值量 | 10000 |
| 每月节省人数 | 0 |
| 优先级 | P1 |
| 价值类型 | 长期价值 |
| 预计工时 | 8 |
| 成本 | 250 |
| ROI | 40.00 |
| 操作手册 | 否 |
| 更新点 | |
| 是否高危 | 否 |
| 评分 | |
| 当前状态 | 已关闭 |
| 所处阶段 | 已关闭 |
| 关键词 | |
| 计划上线 | 2025-06-24 |
| 上线时间 | 2025-06-24 |
| 计划验收时间 | 2025-06-27 |
| 业务验收时间 | 2025-06-30 19:49:33 |
| 满意度 | 满意 |
| 价值验收 | 基本达成 |
| 满意度分值 | 80 |
| 业务回填价值 | 10000 |
| 验收达成价值 | 48000.0 |
| 验收达成ROI | 12.0 |
| 由谁创建 | 林很 于 2025-06-06 16:26:32 |
|---|---|
| 指派给 | Closed 于 2025-07-04 14:29:21 |
| 由谁评审 | 张明磊 |
| 评审时间 | 2025-06-12 |
| 由谁关闭 | 周杰 于 2025-07-04 14:29:21 |
| 关闭原因 | 已完成 |
| 最后修改 | 周杰 于 2025-07-04 14:29:21 |
| 相关Bug |
|
|---|---|
| 相关用例 |
|
| 相关需求 |
|
| 细分需求 |
|
工时：8H
1. 优化及验证