事务执行时间过长，应分步骤分批处理数据，单次事务执行时间不能过长
--修改版本：任务ID:3956 利润相关>>产品利润管理页面增加退款率的分析展示
CREATE PROC [dbo].[Proc_SaleProductProfitStastic]
AS
BEGIN
--第一步：删除三个月前的数据
DELETE FROM RB_Report_SalesProductProfit
WHERE ShipDate < CONVERT(NVARCHAR(100), DATEADD(MONTH, -4, GETDATE()), 23);
DELETE FROM RB_Report_SKUProductProfitStastic
WHERE ShipDate < CONVERT(NVARCHAR(100), DATEADD(MONTH, -4, GETDATE()), 23);
DECLARE CurrMaxShipDate DATE,
NeedInCount INT,
BeforeFifteenDate DATE;
--查询最新的发货日期
SET BeforeFifteenDate = CONVERT(DATE, DATEADD(DAY, -15, GETDATE())) --CONVERT(DATE, DATEADD(DAY, -15, GETDATE()));
SET CurrMaxShipDate =
(
SELECT CONVERT(NVARCHAR(100),
ISNULL( CASE
WHEN MAX(ShipDate) > BeforeFifteenDate THEN
BeforeFifteenDate
ELSE
MAX(ShipDate)
END,
DATEADD(MONTH, -3, GETDATE())
),
23
)
FROM RB_Report_SalesProductProfit (NOLOCK)
);
--删除CurrMaxShipDate 之后的所有数据，确保数据的齐全（插入的时间>ShipDate）
--DELETE FROM RB_Report_SalesProductProfit
-- WHERE ShipDate > CurrMaxShipDate
DELETE FROM RB_Report_SKUProductProfitStastic
WHERE ShipDate > CurrMaxShipDate;
--需要新增的数据
CREATE TABLE SalesProductProfit
(
[ID] [INT] NOT NULL IDENTITY(1, 1),
[CustomerID] [INT] NOT NULL,
[SKU] [NVARCHAR](200) COLLATE Chinese_PRC_CI_AS NOT NULL,
[SellerSKU] [NVARCHAR](400) COLLATE Chinese_PRC_CI_AS NOT NULL,
[OrderSourceID] [INT] NOT NULL,
[OrderSourceName] [NVARCHAR](400) COLLATE Chinese_PRC_CI_AS NOT NULL,
[ProductNameCN] [NVARCHAR](2000) COLLATE Chinese_PRC_CI_AS NULL,
[ShipDate] [DATE] NOT NULL,
[DailyProductNum] [INT] NOT NULL,
[DailyProfits] [MONEY] NOT NULL,
[DailyIncomes] [MONEY] NOT NULL,
[SellerSKUDailyProfitsRate] [DECIMAL](18, 2) NULL,
[SellerSKUSevenDayProfitsRate] [DECIMAL](18, 2) NULL,
[SellerSKUCurrWeekprofits] [MONEY] NULL,
[SellerSKULastWeekprofits] [MONEY] NULL,
[SevenDayProfits] [MONEY] NULL,
[SevenDaySaleNumber] [INT] NULL,
[MonthProfits] [MONEY] NULL,
[MonthSaleNumber] [INT] NULL,
[MonthAvgProfits] [MONEY] NULL,
[BusinessAdminID] [INT] NULL,
[SevenDayIncomes] [MONEY] NULL
DEFAULT ((0)),
[MonthIncomes] [MONEY] NULL
DEFAULT ((0)),
[UpdateTime] [DATETIME] NULL,
[OrderSourceType] [INT] NULL
DEFAULT ((0)),
[DailyAvgRefund] [MONEY] NULL,
[SevenDayAvgRefund] [MONEY] NULL,
[MonthAvgRefund] [MONEY] NULL,
[DailyRefund] [MONEY] NULL,
[SevenDayRefund] [MONEY] NULL,
[MonthRefund] [MONEY] NULL,
[ThreeMonthProfitRate] [DECIMAL](18, 4) NULL,
[ThreeMonthRefundRate] [DECIMAL](18, 4) NULL,
[OtherDailyProfits] [MONEY] NULL,
[OtherSellerSKUDailyProfitsRate] DECIMAL(18,4) NULL,
[OtherSellerSKUSevenDayProfitsRate] DECIMAL(18,4) NULL,
[OtherSellerSKUCurrWeekprofits] MONEY NULL,
[OtherSellerSKULastWeekprofits] MONEY NULL,
[OtherSevenDayProfits] MONEY NULL,
[OtherMonthProfits] MONEY NULL,
[OtherMonthAvgProfits] MONEY NULL,
[OtherThreeMonthProfitRate] DECIMAL(18,4) NULL
);
CREATE INDEX IX_ShipDate ON SalesProductProfit(ShipDate);
CREATE INDEX IX_CustomerID ON SalesProductProfit(CustomerID,SKU,SellerSKU,OrderSourceID,ShipDate);
INSERT INTO SalesProductProfit
(
CustomerID,
SKU,
SellerSKU,
OrderSourceID,
OrderSourceName,
ProductNameCN,
ShipDate,
DailyProductNum,
DailyProfits,
DailyIncomes,
SevenDayProfits,
SevenDaySaleNumber,
MonthProfits,
MonthSaleNumber,
MonthAvgProfits,
BusinessAdminID,
OrderSourceType,
DailyRefund,
SevenDayRefund,
MonthRefund,
DailyAvgRefund,
SevenDayAvgRefund,
MonthAvgRefund,
ThreeMonthProfitRate,
ThreeMonthRefundRate,
OtherDailyProfits,
OtherSevenDayProfits,
OtherMonthProfits,
OtherMonthAvgProfits,
OtherThreeMonthProfitRate
)
SELECT CustomerID,
SKU,
SellerSKU,
OrderSourceID,
OrderSourceName,
ProductNameCN,
ShipDate,
SUM(ProductNum + ProdutNumUpdate),
SUM(Profits),
SUM(Incomes),
0, --七天总利润
0, --七天总销量
0, --月利润
0, --月总销量
0, --月平均利润率
BusinessAdminID,
OrderSourceType,
SUM(SalesRefunds),
0, --七天总退款
0, --月总退款
0, --日平均退款率
0, --七天平均退款率
0, --月平均退款率
0, --三个月利润率
0, --三个月退款率
SUM(Profits+ISNULL(OtherAdvertisingFee,0)+ISNULL(OtherPlatformFee,0)+ISNULL(OtherYunCangFee,0)+ISNULL(OtherReturnFee,0)), --日利润（含其他）
0, --七天总利润 (含其他）
0, --月利润（含其他）
0, --月平均利润率（含其他）
0 --三个月利润率（含其他）
FROM RB_Report_OrderSettlement (NOLOCK)
WHERE ShipDate > CurrMaxShipDate
AND SKU !=
AND SellerSKU !=
GROUP BY CustomerID,
SKU,
SellerSKU,
OrderSourceID,
OrderSourceName,
ProductNameCN,
ShipDate,
BusinessAdminID,
OrderSourceType
ORDER BY ShipDate;
INSERT INTO SalesProductProfit
(
CustomerID,
SKU,
SellerSKU,
OrderSourceID,
OrderSourceName,
ProductNameCN,
ShipDate,
DailyProductNum,
DailyProfits,
DailyIncomes,
SellerSKUDailyProfitsRate,
SellerSKUSevenDayProfitsRate,
SellerSKUCurrWeekprofits,
SellerSKULastWeekprofits,
SevenDayProfits,
SevenDaySaleNumber,
MonthProfits,
MonthSaleNumber,
MonthAvgProfits,
BusinessAdminID,
SevenDayIncomes,
MonthIncomes,
OrderSourceType,
DailyAvgRefund,
SevenDayAvgRefund,
MonthAvgRefund,
DailyRefund,
SevenDayRefund,
MonthRefund,
ThreeMonthProfitRate,
ThreeMonthRefundRate,
OtherDailyProfits,
OtherSellerSKUDailyProfitsRate,
OtherSellerSKUSevenDayProfitsRate,
OtherSellerSKUCurrWeekprofits,
OtherSellerSKULastWeekprofits,
OtherSevenDayProfits,
OtherMonthProfits,
OtherMonthAvgProfits,
OtherThreeMonthProfitRate
)
SELECT CustomerID,
SKU,
SellerSKU,
OrderSourceID,
OrderSourceName,
ProductNameCN,
ShipDate,
DailyProductNum,
DailyProfits,
DailyIncomes,
SellerSKUDailyProfitsRate,
SellerSKUSevenDayProfitsRate,
SellerSKUCurrWeekprofits,
SellerSKULastWeekprofits,
SevenDayProfits,
SevenDaySaleNumber,
MonthProfits,
MonthSaleNumber,
MonthAvgProfits,
BusinessAdminID,
SevenDayIncomes,
MonthIncomes,
OrderSourceType,
DailyAvgRefund,
SevenDayAvgRefund,
MonthAvgRefund,
DailyRefund,
SevenDayRefund,
MonthRefund,
ThreeMonthProfitRate,
ThreeMonthRefundRate,
ISNULL(OtherDailyProfits,0) AS OtherDailyProfits,
ISNULL(OtherSellerSKUDailyProfitsRate,0) AS OtherSellerSKUDailyProfitsRate,
ISNULL(OtherSellerSKUSevenDayProfitsRate,0) AS OtherSellerSKUSevenDayProfitsRate,
ISNULL(OtherSellerSKUCurrWeekprofits,0) AS OtherSellerSKUCurrWeekprofits,
ISNULL(OtherSellerSKULastWeekprofits,0) AS OtherSellerSKULastWeekprofits,
ISNULL(OtherSevenDayProfits,0) AS OtherSevenDayProfits,
ISNULL(OtherSevenDayProfits,0) AS OtherMonthProfits,
ISNULL(OtherMonthAvgProfits,0) AS OtherMonthAvgProfits,
ISNULL(OtherThreeMonthProfitRate,0) AS OtherThreeMonthProfitRate
FROM RB_Report_SalesProductProfit (NOLOCK)
WHERE ShipDate > DATEADD(DAY, -90, CurrMaxShipDate)
AND ShipDate <= CurrMaxShipDate
ORDER BY ShipDate;
--更新SalesProductProfit表的SellerSKUDailyProfitsRate(渠道SKU日平均利润)
--渠道SKU日平均利润率：渠道SKU总的利润率/日销售额
UPDATE SalesProductProfit
SET SellerSKUDailyProfitsRate = CASE
WHEN DailyIncomes = 0 THEN
0
ELSE
CASE
WHEN DailyProfits < 0 THEN
DailyProfits / ABS(DailyIncomes)
ELSE
DailyProfits / DailyIncomes
END
END,
DailyAvgRefund = CASE
WHEN DailyIncomes = 0 THEN
0
ELSE
CASE
WHEN DailyIncomes + ABS(DailyRefund) = 0 THEN
0
ELSE
DailyRefund / (DailyIncomes + ABS(DailyRefund))
END
END,
OtherSellerSKUDailyProfitsRate = CASE
WHEN DailyIncomes = 0 THEN
0
ELSE
CASE
WHEN OtherDailyProfits < 0 THEN
OtherDailyProfits / ABS(DailyIncomes)
ELSE
OtherDailyProfits / DailyIncomes
END
END
WHERE ShipDate > CurrMaxShipDate;
--更新SalesProductProfit表(渠道SKU七天利润计算)
UPDATE rspp
SET SevenDayProfits = o.SevenDayProfits,
SevenDaySaleNumber = o.SevenDaySaleNumber,
SevenDayIncomes = o.SevenDayIncomes,
SellerSKUSevenDayProfitsRate = CASE
WHEN o.SevenDayIncomes = 0 THEN
0
ELSE
(CASE
WHEN o.SevenDayProfits < 0 THEN
o.SevenDayProfits / ABS(o.SevenDayIncomes)
ELSE
o.SevenDayProfits / o.SevenDayIncomes
END
)
END,
SevenDayRefund = o.SevenDayRefund, --七天退款总额
SevenDayAvgRefund = CASE --七天平均退款率
WHEN o.SevenDayIncomes = 0 THEN
0
ELSE
CASE
WHEN o.SevenDayIncomes + ABS(o.SevenDayRefund) = 0 THEN
0
ELSE
o.SevenDayRefund / (o.SevenDayIncomes + ABS(o.SevenDayRefund))
END
END,
OtherSellerSKUSevenDayProfitsRate = CASE
WHEN o.SevenDayIncomes = 0 THEN
0
ELSE
(CASE
WHEN o.OtherSevenDayProfits < 0 THEN
o.OtherSevenDayProfits / ABS(o.SevenDayIncomes)
ELSE
o.OtherSevenDayProfits / o.SevenDayIncomes
END
)
END
FROM SalesProductProfit AS rspp
INNER JOIN
(
SELECT a.ID,
SUM(rb.DailyProfits) AS SevenDayProfits,
SUM(rb.OtherDailyProfits) AS OtherSevenDayProfits,
SUM(rb.DailyProductNum) AS SevenDaySaleNumber,
SUM(rb.DailyIncomes) AS SevenDayIncomes,
SUM(rb.DailyRefund) AS SevenDayRefund
FROM SalesProductProfit AS a
INNER JOIN SalesProductProfit rb (NOLOCK)
ON rb.CustomerID = a.CustomerID
AND rb.SKU = a.SKU
AND rb.SellerSKU = a.SellerSKU
AND rb.OrderSourceID = a.OrderSourceID
AND DATEDIFF(DAY, rb.ShipDate, a.ShipDate)
BETWEEN 0 AND 6
WHERE a.ShipDate > CurrMaxShipDate
GROUP BY a.ID
) AS o
ON o.ID = rspp.ID;
--更改SalesProductProfit表的SellerSKUCurrWeekprofits（本周利润）
UPDATE SalesProductProfit
SET SellerSKUCurrWeekprofits =
(
SELECT SUM(DailyProfits)
FROM SalesProductProfit rb (NOLOCK)
WHERE rb.CustomerID = SalesProductProfit.CustomerID
AND rb.SKU = SalesProductProfit.SKU
AND rb.SellerSKU = SalesProductProfit.SellerSKU
AND rb.OrderSourceID = SalesProductProfit.OrderSourceID
AND rb.ShipDate >= DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -1, SalesProductProfit.ShipDate)), 0)
AND rb.ShipDate < DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -1, SalesProductProfit.ShipDate)), 7)
),
OtherSellerSKUCurrWeekprofits =
(
SELECT SUM(OtherDailyProfits)
FROM SalesProductProfit rb (NOLOCK)
WHERE rb.CustomerID = SalesProductProfit.CustomerID
AND rb.SKU = SalesProductProfit.SKU
AND rb.SellerSKU = SalesProductProfit.SellerSKU
AND rb.OrderSourceID = SalesProductProfit.OrderSourceID
AND rb.ShipDate >= DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -1, SalesProductProfit.ShipDate)), 0)
AND rb.ShipDate < DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -1, SalesProductProfit.ShipDate)), 7)
)
WHERE ShipDate > CurrMaxShipDate;
--更改SalesProductProfit表的SellerSKULastWeekprofits（上周利润）
UPDATE SalesProductProfit
SET SellerSKULastWeekprofits =
(
SELECT ISNULL(SUM(DailyProfits), 0)
FROM SalesProductProfit rb (NOLOCK)
WHERE rb.CustomerID = SalesProductProfit.CustomerID
AND rb.SKU = SalesProductProfit.SKU
AND rb.SellerSKU = SalesProductProfit.SellerSKU
AND rb.OrderSourceID = SalesProductProfit.OrderSourceID
AND rb.ShipDate >= DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -7, SalesProductProfit.ShipDate)), 0)
AND rb.ShipDate < DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -1, SalesProductProfit.ShipDate)), 0)
),
OtherSellerSKULastWeekprofits =
(
SELECT ISNULL(SUM(OtherDailyProfits), 0)
FROM SalesProductProfit rb (NOLOCK)
WHERE rb.CustomerID = SalesProductProfit.CustomerID
AND rb.SKU = SalesProductProfit.SKU
AND rb.SellerSKU = SalesProductProfit.SellerSKU
AND rb.OrderSourceID = SalesProductProfit.OrderSourceID
AND rb.ShipDate >= DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -7, SalesProductProfit.ShipDate)), 0)
AND rb.ShipDate < DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -1, SalesProductProfit.ShipDate)), 0)
)
WHERE ShipDate > CurrMaxShipDate;
--更新SalesProductProfit表的MonthProfits(渠道SKU月总利润)
UPDATE rspp
SET MonthProfits = o.MonthProfits,
MonthSaleNumber = o.MonthSaleNumber,
MonthIncomes = o.MonthIncomes,
MonthAvgProfits = CASE
WHEN o.MonthIncomes = 0 THEN
0
ELSE
(CASE
WHEN o.MonthProfits < 0 THEN
o.MonthProfits / ABS(o.MonthIncomes)
ELSE
o.MonthProfits / o.MonthIncomes
END
)
END,
MonthRefund = o.MonthRefund, --月总退款
MonthAvgRefund = CASE --月平均退款率
WHEN o.MonthIncomes = 0 THEN
0
ELSE
CASE
WHEN o.MonthIncomes + ABS(o.MonthRefund) = 0 THEN
0
ELSE
o.MonthRefund / (o.MonthIncomes + ABS(o.MonthRefund))
END
END,
OtherMonthProfits = o.OtherMonthProfits,
OtherMonthAvgProfits = CASE
WHEN o.MonthIncomes = 0 THEN
0
ELSE
(CASE
WHEN o.OtherMonthProfits < 0 THEN
o.OtherMonthProfits / ABS(o.MonthIncomes)
ELSE
o.OtherMonthProfits / o.MonthIncomes
END
)
END
FROM SalesProductProfit AS rspp
INNER JOIN
(
SELECT a.ID,
SUM(rb.DailyProfits) AS MonthProfits,
SUM(rb.DailyProductNum) AS MonthSaleNumber,
SUM(rb.DailyIncomes) AS MonthIncomes,
SUM(rb.DailyRefund) AS MonthRefund,
SUM(rb.OtherDailyProfits) AS OtherMonthProfits
FROM SalesProductProfit AS a
INNER JOIN SalesProductProfit rb (NOLOCK)
ON rb.CustomerID = a.CustomerID
AND rb.SKU = a.SKU
AND rb.SellerSKU = a.SellerSKU
AND rb.OrderSourceID = a.OrderSourceID
AND DATEDIFF(DAY, rb.ShipDate, a.ShipDate)
BETWEEN 0 AND 29
WHERE a.ShipDate > CurrMaxShipDate
GROUP BY a.ID
) AS o
ON o.ID = rspp.ID;
--更新三个月利润率和退款率
UPDATE rspp
SET ThreeMonthProfitRate = CASE
WHEN o.TreeMonthIncomes = 0 THEN
0
ELSE
(CASE
WHEN o.TreeMonthProfits < 0 THEN
o.TreeMonthProfits / ABS(o.TreeMonthIncomes)
ELSE
o.TreeMonthProfits / o.TreeMonthIncomes
END
)
END,
ThreeMonthRefundRate = CASE
WHEN o.TreeMonthIncomes = 0 THEN
0
ELSE
CASE
WHEN o.TreeMonthIncomes + ABS(o.TreeMonthRefund) = 0 THEN
0
ELSE
o.TreeMonthRefund / (o.TreeMonthIncomes + ABS(o.TreeMonthRefund))
END
END,
OtherThreeMonthProfitRate = CASE
WHEN o.TreeMonthIncomes = 0 THEN
0
ELSE
(CASE
WHEN o.OtherTreeMonthProfits < 0 THEN
o.OtherTreeMonthProfits / ABS(o.TreeMonthIncomes)
ELSE
o.OtherTreeMonthProfits / o.TreeMonthIncomes
END
)
END
FROM SalesProductProfit AS rspp
INNER JOIN
(
SELECT a.ID,
SUM(rb.DailyProfits) AS TreeMonthProfits,
SUM(rb.DailyProductNum) AS TreeMonthSaleNumber,
SUM(rb.DailyIncomes) AS TreeMonthIncomes,
SUM(rb.DailyRefund) AS TreeMonthRefund,
SUM(rb.OtherDailyProfits) AS OtherTreeMonthProfits
FROM SalesProductProfit AS a
INNER JOIN SalesProductProfit rb (NOLOCK)
ON rb.CustomerID = a.CustomerID
AND rb.SKU = a.SKU
AND rb.SellerSKU = a.SellerSKU
AND rb.OrderSourceID = a.OrderSourceID
AND DATEDIFF(DAY, rb.ShipDate, a.ShipDate)
BETWEEN 0 AND 90
WHERE a.ShipDate > CurrMaxShipDate
GROUP BY a.ID
) AS o
ON o.ID = rspp.ID;
--
MERGE RB_Report_SalesProductProfit AS target
USING
(
SELECT CustomerID,
SKU,
SellerSKU,
OrderSourceID,
OrderSourceName,
ProductNameCN,
ShipDate,
DailyProductNum,
DailyProfits,
DailyIncomes,
SellerSKUDailyProfitsRate,
SellerSKUSevenDayProfitsRate,
SellerSKUCurrWeekprofits,
SellerSKULastWeekprofits,
SevenDayProfits,
SevenDaySaleNumber,
MonthProfits,
MonthSaleNumber,
MonthAvgProfits,
BusinessAdminID,
SevenDayIncomes,
MonthIncomes,
UpdateTime,
OrderSourceType,
DailyAvgRefund,
SevenDayAvgRefund,
MonthAvgRefund,
DailyRefund,
SevenDayRefund,
MonthRefund,
ThreeMonthProfitRate,
ThreeMonthRefundRate,
OtherDailyProfits,
OtherSellerSKUDailyProfitsRate,
OtherSellerSKUSevenDayProfitsRate,
OtherSellerSKUCurrWeekprofits,
OtherSellerSKULastWeekprofits,
OtherSevenDayProfits,
OtherMonthProfits,
OtherMonthAvgProfits,
OtherThreeMonthProfitRate
FROM SalesProductProfit
WHERE ShipDate > CurrMaxShipDate
) AS source
(CustomerID, SKU, SellerSKU, OrderSourceID, OrderSourceName, ProductNameCN, ShipDate, DailyProductNum, DailyProfits, DailyIncomes, SellerSKUDailyProfitsRate, SellerSKUSevenDayProfitsRate, SellerSKUCurrWeekprofits, SellerSKULastWeekprofits, SevenDayProfits, SevenDaySaleNumber, MonthProfits, MonthSaleNumber, MonthAvgProfits, BusinessAdminID, SevenDayIncomes, MonthIncomes, UpdateTime, OrderSourceType, DailyAvgRefund, SevenDayAvgRefund, MonthAvgRefund, DailyRefund, SevenDayRefund, MonthRefund,ThreeMonthProfitRate,ThreeMonthRefundRate,
OtherDailyProfits,
OtherSellerSKUDailyProfitsRate,
OtherSellerSKUSevenDayProfitsRate,
OtherSellerSKUCurrWeekprofits,
OtherSellerSKULastWeekprofits,
OtherSevenDayProfits,
OtherMonthProfits,
OtherMonthAvgProfits,
OtherThreeMonthProfitRate
)
ON (
target.CustomerID = source.CustomerID
AND target.SKU = source.SKU
AND target.SellerSKU = source.SellerSKU
AND target.OrderSourceID = source.OrderSourceID
AND target.OrderSourceName = source.OrderSourceName
AND target.ProductNameCN = source.ProductNameCN
AND target.ShipDate = source.ShipDate
AND target.BusinessAdminID = source.BusinessAdminID
AND target.OrderSourceType = source.OrderSourceType
)
WHEN MATCHED AND target.ShipDate > CurrMaxShipDate
AND (
target.DailyProductNum != source.DailyProductNum
OR target.DailyProfits != source.DailyProfits
OR target.SellerSKUDailyProfitsRate != source.SellerSKUDailyProfitsRate
OR target.SellerSKUSevenDayProfitsRate != source.SellerSKUSevenDayProfitsRate
OR target.SellerSKUCurrWeekprofits != source.SellerSKUCurrWeekprofits
OR target.SellerSKULastWeekprofits != source.SellerSKULastWeekprofits
OR target.SevenDayProfits != source.SevenDayProfits
OR target.MonthProfits != source.MonthProfits
OR target.MonthAvgProfits != source.MonthAvgProfits
OR target.ThreeMonthProfitRate != source.ThreeMonthProfitRate
OR target.ThreeMonthRefundRate != source.ThreeMonthRefundRate
OR target.OtherDailyProfits != source.OtherDailyProfits
OR target.OtherSellerSKUDailyProfitsRate != source.OtherSellerSKUDailyProfitsRate
OR target.OtherSellerSKUSevenDayProfitsRate != source.OtherSellerSKUSevenDayProfitsRate
OR target.OtherSellerSKUCurrWeekprofits != source.OtherSellerSKUCurrWeekprofits
OR target.OtherSellerSKULastWeekprofits != source.OtherSellerSKULastWeekprofits
OR target.OtherSevenDayProfits != source.OtherSevenDayProfits
OR target.OtherMonthProfits != source.OtherMonthProfits
OR target.OtherMonthAvgProfits != source.OtherMonthAvgProfits
OR target.OtherThreeMonthProfitRate != source.OtherThreeMonthProfitRate
) THEN
UPDATE SET target.DailyProductNum = source.DailyProductNum,
target.DailyProfits = source.DailyProfits,
target.DailyIncomes = source.DailyIncomes,
target.SellerSKUDailyProfitsRate = source.SellerSKUDailyProfitsRate,
target.SellerSKUSevenDayProfitsRate = source.SellerSKUSevenDayProfitsRate,
target.SellerSKUCurrWeekprofits = source.SellerSKUCurrWeekprofits,
target.SellerSKULastWeekprofits = source.SellerSKULastWeekprofits,
target.SevenDayProfits = source.SevenDayProfits,
target.SevenDaySaleNumber = source.SevenDaySaleNumber,
target.MonthProfits = source.MonthProfits,
target.MonthSaleNumber = source.MonthSaleNumber,
target.MonthAvgProfits = source.MonthAvgProfits,
target.SevenDayIncomes = source.SevenDayIncomes,
target.MonthIncomes = source.MonthIncomes,
target.DailyAvgRefund = source.DailyAvgRefund,
target.SevenDayAvgRefund = source.SevenDayAvgRefund,
target.MonthAvgRefund = source.MonthAvgRefund,
target.DailyRefund = source.DailyRefund,
target.SevenDayRefund = source.SevenDayRefund,
target.MonthRefund = source.MonthRefund,
target.ThreeMonthProfitRate= source.ThreeMonthProfitRate,
target.ThreeMonthRefundRate= source.ThreeMonthRefundRate,
target.OtherDailyProfits= source.OtherDailyProfits,
target.OtherSellerSKUDailyProfitsRate= source.OtherSellerSKUDailyProfitsRate,
target.OtherSellerSKUSevenDayProfitsRate= source.OtherSellerSKUSevenDayProfitsRate,
target.OtherSellerSKUCurrWeekprofits= source.OtherSellerSKUCurrWeekprofits,
target.OtherSellerSKULastWeekprofits= source.OtherSellerSKULastWeekprofits,
target.OtherSevenDayProfits= source.OtherSevenDayProfits,
target.OtherMonthProfits= source.OtherMonthProfits,
target.OtherMonthAvgProfits= source.OtherMonthAvgProfits,
target.OtherThreeMonthProfitRate= source.OtherThreeMonthProfitRate,
target.UpdateTime = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
CustomerID,
SKU,
SellerSKU,
OrderSourceID,
OrderSourceName,
ProductNameCN,
ShipDate,
DailyProductNum,
DailyProfits,
DailyIncomes,
SellerSKUDailyProfitsRate,
SellerSKUSevenDayProfitsRate,
SellerSKUCurrWeekprofits,
SellerSKULastWeekprofits,
SevenDayProfits,
SevenDaySaleNumber,
MonthProfits,
MonthSaleNumber,
MonthAvgProfits,
BusinessAdminID,
SevenDayIncomes,
MonthIncomes,
OrderSourceType,
DailyAvgRefund,
SevenDayAvgRefund,
MonthAvgRefund,
DailyRefund,
SevenDayRefund,
MonthRefund,
ThreeMonthProfitRate,
ThreeMonthRefundRate,
OtherDailyProfits,
OtherSellerSKUDailyProfitsRate,
OtherSellerSKUSevenDayProfitsRate,
OtherSellerSKUCurrWeekprofits,
OtherSellerSKULastWeekprofits,
OtherSevenDayProfits,
OtherMonthProfits,
OtherMonthAvgProfits,
OtherThreeMonthProfitRate,
UpdateTime
)
VALUES
(CustomerID, SKU, SellerSKU, OrderSourceID, OrderSourceName, ProductNameCN, ShipDate, DailyProductNum,
DailyProfits, DailyIncomes, SellerSKUDailyProfitsRate, SellerSKUSevenDayProfitsRate, SellerSKUCurrWeekprofits,
SellerSKULastWeekprofits, SevenDayProfits, SevenDaySaleNumber, MonthProfits, MonthSaleNumber, MonthAvgProfits,
BusinessAdminID, SevenDayIncomes, MonthIncomes, OrderSourceType, DailyAvgRefund, SevenDayAvgRefund,
MonthAvgRefund, DailyRefund, SevenDayRefund, MonthRefund,ThreeMonthProfitRate,ThreeMonthRefundRate,
OtherDailyProfits,
OtherSellerSKUDailyProfitsRate,
OtherSellerSKUSevenDayProfitsRate,
OtherSellerSKUCurrWeekprofits,
OtherSellerSKULastWeekprofits,
OtherSevenDayProfits,
OtherMonthProfits,
OtherMonthAvgProfits,
OtherThreeMonthProfitRate, GETDATE())
WHEN NOT MATCHED BY SOURCE AND target.ShipDate > CurrMaxShipDate THEN
DELETE;
--OUTPUT action,
-- inserted.*,
-- deleted.*;
DROP TABLE SalesProductProfit
--初始化SKU产品利润统计数据
INSERT INTO RB_Report_SKUProductProfitStastic
(
CustomerID,
SKU,
ProductNameCN,
DailyProfits,
DailyIncomes,
DailyAvgSalePrice,
DailySaleNumber,
ShipDate,
DailyAvgProfits,
SevenDayAvgProfits,
SKUCurrWeekprofits,
SKULastWeekprofits,
DailyRefund, --日总退款
SevenDayRefund, --七天总退款
MonthRefund, --月总退款
DailyAvgRefund, --日平均退款率
SevenDayAvgRefund, --七天平均退款率
MonthAvgRefund --月平均退款率
)
SELECT CustomerID,
SKU,
ProductNameCN,
SUM(DailyProfits),
SUM(DailyIncomes),
CASE
WHEN SUM(DailyProductNum) = 0 THEN
0
ELSE
CASE
WHEN SUM(DailyIncomes) < 0 THEN
SUM(DailyIncomes) / ABS(SUM(DailyProductNum))
ELSE
SUM(DailyIncomes) / SUM(DailyProductNum)
END
END,
SUM(DailyProductNum),
ShipDate,
CASE
WHEN SUM(DailyIncomes) = 0 THEN
0
ELSE
CASE
WHEN SUM(DailyIncomes) < 0 THEN
SUM(DailyProfits) / ABS(SUM(DailyIncomes))
ELSE
SUM(DailyProfits) / SUM(DailyIncomes)
END
END,
0,
0,
0,
SUM(DailyRefund), --日总退款
0,
0,
CASE
WHEN SUM(DailyIncomes) = 0 THEN
0
ELSE
CASE
WHEN SUM(DailyIncomes + ABS(DailyRefund)) = 0 THEN
0
ELSE
SUM(DailyRefund) / SUM(DailyIncomes + ABS(DailyRefund))
END
END, --日平均退款率
0,
0
FROM RB_Report_SalesProductProfit (NOLOCK)
WHERE ShipDate > CurrMaxShipDate
GROUP BY SKU,
CustomerID,
ShipDate,
ProductNameCN
ORDER BY ShipDate;
--更新RB_Report_SKUProductProfitStastic表(渠道SKU七天总利润)
UPDATE rspp
SET SevenDayProfits = o.SevenDayProfits,
SevenDaySaleNumber = o.SevenDaySaleNumber,
SevenDayIncomes = o.SevenDayIncomes,
SevenDayAvgProfits = CASE
WHEN o.SevenDayIncomes = 0 THEN
0
ELSE
(CASE
WHEN o.SevenDayProfits < 0 THEN
o.SevenDayProfits / ABS(o.SevenDayIncomes)
ELSE
o.SevenDayProfits / o.SevenDayIncomes
END
)
END,
SevenDayRefund = o.SevenDayRefund, --七天总退款
SevenDayAvgRefund = CASE --七天平均退款率
WHEN o.SevenDayIncomes = 0 THEN
0
ELSE
CASE
WHEN o.SevenDayIncomes + ABS(o.SevenDayRefund) = 0 THEN
0
ELSE
o.SevenDayRefund / (o.SevenDayIncomes + ABS(o.SevenDayRefund))
END
END
FROM RB_Report_SKUProductProfitStastic AS rspp
INNER JOIN
(
SELECT a.ID,
SUM(rb.DailyProfits) AS SevenDayProfits,
SUM(rb.DailySaleNumber) AS SevenDaySaleNumber,
SUM(ISNULL(rb.DailyIncomes, 0)) AS SevenDayIncomes,
SUM(rb.DailyRefund) AS SevenDayRefund
FROM RB_Report_SKUProductProfitStastic AS a
INNER JOIN RB_Report_SKUProductProfitStastic rb (NOLOCK)
ON rb.CustomerID = a.Custo