[步骤]
[09:13:06] -------------------------------------------------------------
CODE:N0913066
Error:21_596_1_-2146232060
SQL: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 ,
sum(isnull(tt1.退款商品数量,0))RefundsProductNum ,
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 ,
SUM(CASE WHEN ISNULL(alias.SalesRefunds,0)<>0 THEN alias.ProductNum+alias.ProdutNumUpdate ELSE 0 END)退款商品数量,
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<'2024-05-01' ) ) or alias.CostOfAdvertising=0 OR alias.OrderDescription LIKE '%发票广告费%' )
)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 ,
SUM(CASE WHEN ISNULL(alias.SalesRefunds,0)<>0 THEN alias.ProductNum+alias.ProdutNumUpdate ELSE 0 END)退款商品数量,
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<'2024-05-01' ) ) or alias.CostOfAdvertising=0 OR alias.OrderDescription LIKE '%发票广告费%' )
)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',0'退款商品数量',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)
and alias.OrderSourceType in(1) AND alias.SettlementDate>='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 ,
SUM(CASE WHEN ISNULL(alias.SalesRefunds,0)<>0 THEN alias.ProductNum+alias.ProdutNumUpdate ELSE 0 END)退款商品数量,
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<'2024-05-01' ) ) or alias.CostOfAdvertising=0 OR alias.OrderDescription LIKE '%发票广告费%' )
)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/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',
&nbs