业务需求
需求描述

https://alidocs.dingtalk.com/i/nodes/2Amq4vjg89gab9DztpXwrB0aV3kdP0wQ

验收标准
价值描述
5000
历史记录  
  1. 2025-12-17 10:09:43, 由 林很 Bug转化而来，Bug编号为 61119 。
  2. 2025-12-17 10:10:10, 由 林很 编辑。
    修改了 所属计划 ，旧值为 ""，新值为 "2392"。
    修改了 计划验收时间，旧值为 "0000-00-00"，新值为 "2025-12-30"。
    修改了 所处阶段 ，旧值为 "wait"，新值为 "planned"。
  3. 2025-12-17 10:11:49, 由 林很 变更。
    修改了 版本号 ，旧值为 "1"，新值为 "2"。
    修改了 需求名称，区别为：
    001- 【系统优化】erp本地服务慢sql优化处理
    001+ 【系统优化】产品利润管理本地服务慢sql优化处理
    001- 【系统优化】erp本地服务慢sql优化处理
    001+ 【系统优化】产品利润管理本地服务慢sql优化处理
  4. 2025-12-17 10:30:05, 由 林很 变更。
    修改了 版本号 ，旧值为 "2"，新值为 "3"。
    修改了 需求描述，区别为：
    001- 事务执行时间过长，应分步骤分批处理数据，单次事务执行时间不能过长
    001+ <a href="https://alidocs.dingtalk.com/i/nodes/2Amq4vjg89gab9DztpXwrB0aV3kdP0wQ">https://alidocs.dingtalk.com/i/nodes/2Amq4vjg89gab9DztpXwrB0aV3kdP0wQ
    002- <img src="https://irobotbox-hd1.oss-cn-hangzhou.aliyuncs.com/1/Kindeditor/2025/12/15/1f45634952984ddab228228fe82d6924/665d09b3-c061-4055-a6fa-1537ecabb8e4.png" title="from clipboard" alt="from clipboard" />
    003- --修改版本：任务ID:3956 利润相关&gt;&gt;产品利润管理页面增加退款率的分析展示
    004- CREATE PROC [dbo].[Proc_SaleProductProfitStastic]
    005- AS
    006- BEGIN
    007- --第一步：删除三个月前的数据
    008- DELETE FROM RB_Report_SalesProductProfit
    009- WHERE ShipDate &lt; CONVERT(NVARCHAR(100), DATEADD(MONTH, -4, GETDATE()), 23);
    010- DELETE FROM RB_Report_SKUProductProfitStastic
    011- WHERE ShipDate &lt; CONVERT(NVARCHAR(100), DATEADD(MONTH, -4, GETDATE()), 23);
    012-
    013- DECLARE CurrMaxShipDate DATE,
    014- NeedInCount INT,
    015- BeforeFifteenDate DATE;
    016-
    017- --查询最新的发货日期
    018- SET BeforeFifteenDate = CONVERT(DATE, DATEADD(DAY, -15, GETDATE())) --CONVERT(DATE, DATEADD(DAY, -15, GETDATE()));
    019- SET CurrMaxShipDate =
    020- (
    021- SELECT CONVERT(NVARCHAR(100),
    022- ISNULL( CASE
    023- WHEN MAX(ShipDate) &gt; BeforeFifteenDate THEN
    024- BeforeFifteenDate
    025- ELSE
    026- MAX(ShipDate)
    027- END,
    028- DATEADD(MONTH, -3, GETDATE())
    029- ),
    030- 23
    031- )
    032- FROM RB_Report_SalesProductProfit (NOLOCK)
    033- );
    034-
    035-
    036- --删除CurrMaxShipDate 之后的所有数据，确保数据的齐全（插入的时间&gt;ShipDate）
    037- --DELETE FROM RB_Report_SalesProductProfit
    038- -- WHERE ShipDate &gt; CurrMaxShipDate
    039- DELETE FROM RB_Report_SKUProductProfitStastic
    040- WHERE ShipDate &gt; CurrMaxShipDate;
    041-
    042- --需要新增的数据
    043- CREATE TABLE SalesProductProfit
    044- (
    045- [ID] [INT] NOT NULL IDENTITY(1, 1),
    046- [CustomerID] [INT] NOT NULL,
    047- [SKU] [NVARCHAR](200) COLLATE Chinese_PRC_CI_AS NOT NULL,
    048- [SellerSKU] [NVARCHAR](400) COLLATE Chinese_PRC_CI_AS NOT NULL,
    049- [OrderSourceID] [INT] NOT NULL,
    050- [OrderSourceName] [NVARCHAR](400) COLLATE Chinese_PRC_CI_AS NOT NULL,
    051- [ProductNameCN] [NVARCHAR](2000) COLLATE Chinese_PRC_CI_AS NULL,
    052- [ShipDate] [DATE] NOT NULL,
    053- [DailyProductNum] [INT] NOT NULL,
    054- [DailyProfits] [MONEY] NOT NULL,
    055- [DailyIncomes] [MONEY] NOT NULL,
    056- [SellerSKUDailyProfitsRate] [DECIMAL](18, 2) NULL,
    057- [SellerSKUSevenDayProfitsRate] [DECIMAL](18, 2) NULL,
    058- [SellerSKUCurrWeekprofits] [MONEY] NULL,
    059- [SellerSKULastWeekprofits] [MONEY] NULL,
    060- [SevenDayProfits] [MONEY] NULL,
    061- [SevenDaySaleNumber] [INT] NULL,
    062- [MonthProfits] [MONEY] NULL,
    063- [MonthSaleNumber] [INT] NULL,
    064- [MonthAvgProfits] [MONEY] NULL,
    065- [BusinessAdminID] [INT] NULL,
    066- [SevenDayIncomes] [MONEY] NULL
    067- DEFAULT ((0)),
    068- [MonthIncomes] [MONEY] NULL
    069- DEFAULT ((0)),
    070- [UpdateTime] [DATETIME] NULL,
    071- [OrderSourceType] [INT] NULL
    072- DEFAULT ((0)),
    073- [DailyAvgRefund] [MONEY] NULL,
    074- [SevenDayAvgRefund] [MONEY] NULL,
    075- [MonthAvgRefund] [MONEY] NULL,
    076- [DailyRefund] [MONEY] NULL,
    077- [SevenDayRefund] [MONEY] NULL,
    078- [MonthRefund] [MONEY] NULL,
    079- [ThreeMonthProfitRate] [DECIMAL](18, 4) NULL,
    080- [ThreeMonthRefundRate] [DECIMAL](18, 4) NULL,
    081- [OtherDailyProfits] [MONEY] NULL,
    082- [OtherSellerSKUDailyProfitsRate] DECIMAL(18,4) NULL,
    083- [OtherSellerSKUSevenDayProfitsRate] DECIMAL(18,4) NULL,
    084- [OtherSellerSKUCurrWeekprofits] MONEY NULL,
    085- [OtherSellerSKULastWeekprofits] MONEY NULL,
    086- [OtherSevenDayProfits] MONEY NULL,
    087- [OtherMonthProfits] MONEY NULL,
    088- [OtherMonthAvgProfits] MONEY NULL,
    089- [OtherThreeMonthProfitRate] DECIMAL(18,4) NULL
    090- );
    091- CREATE INDEX IX_ShipDate ON SalesProductProfit(ShipDate);
    092- CREATE INDEX IX_CustomerID ON SalesProductProfit(CustomerID,SKU,SellerSKU,OrderSourceID,ShipDate);
    093-
    094- INSERT INTO SalesProductProfit
    095- (
    096- CustomerID,
    097- SKU,
    098- SellerSKU,
    099- OrderSourceID,
    100- OrderSourceName,
    101- ProductNameCN,
    102- ShipDate,
    103- DailyProductNum,
    104- DailyProfits,
    105- DailyIncomes,
    106- SevenDayProfits,
    107- SevenDaySaleNumber,
    108- MonthProfits,
    109- MonthSaleNumber,
    110- MonthAvgProfits,
    111- BusinessAdminID,
    112- OrderSourceType,
    113- DailyRefund,
    114- SevenDayRefund,
    115- MonthRefund,
    116- DailyAvgRefund,
    117- SevenDayAvgRefund,
    118- MonthAvgRefund,
    119- ThreeMonthProfitRate,
    120- ThreeMonthRefundRate,
    121- OtherDailyProfits,
    122- OtherSevenDayProfits,
    123- OtherMonthProfits,
    124- OtherMonthAvgProfits,
    125- OtherThreeMonthProfitRate
    126- )
    127- SELECT CustomerID,
    128- SKU,
    129- SellerSKU,
    130- OrderSourceID,
    131- OrderSourceName,
    132- ProductNameCN,
    133- ShipDate,
    134- SUM(ProductNum + ProdutNumUpdate),
    135- SUM(Profits),
    136- SUM(Incomes),
    137- 0, --七天总利润
    138- 0, --七天总销量
    139- 0, --月利润
    140- 0, --月总销量
    141- 0, --月平均利润率
    142-
    143- BusinessAdminID,
    144- OrderSourceType,
    145- SUM(SalesRefunds),
    146- 0, --七天总退款
    147- 0, --月总退款
    148- 0, --日平均退款率
    149- 0, --七天平均退款率
    150- 0, --月平均退款率
    151- 0, --三个月利润率
    152- 0, --三个月退款率
    153-
    154- SUM(Profits+ISNULL(OtherAdvertisingFee,0)+ISNULL(OtherPlatformFee,0)+ISNULL(OtherYunCangFee,0)+ISNULL(OtherReturnFee,0)), --日利润（含其他）
    155- 0, --七天总利润 (含其他）
    156- 0, --月利润（含其他）
    157- 0, --月平均利润率（含其他）
    158- 0 --三个月利润率（含其他）
    159- FROM RB_Report_OrderSettlement (NOLOCK)
    160- WHERE ShipDate &gt; CurrMaxShipDate
    161- AND SKU !=
    162- AND SellerSKU !=
    163- GROUP BY CustomerID,
    164- SKU,
    165- SellerSKU,
    166- OrderSourceID,
    167- OrderSourceName,
    168- ProductNameCN,
    169- ShipDate,
    170- BusinessAdminID,
    171- OrderSourceType
    172- ORDER BY ShipDate;
    173-
    174-
    175-
    176- INSERT INTO SalesProductProfit
    177- (
    178- CustomerID,
    179- SKU,
    180- SellerSKU,
    181- OrderSourceID,
    182- OrderSourceName,
    183- ProductNameCN,
    184- ShipDate,
    185- DailyProductNum,
    186- DailyProfits,
    187- DailyIncomes,
    188- SellerSKUDailyProfitsRate,
    189- SellerSKUSevenDayProfitsRate,
    190- SellerSKUCurrWeekprofits,
    191- SellerSKULastWeekprofits,
    192- SevenDayProfits,
    193- SevenDaySaleNumber,
    194- MonthProfits,
    195- MonthSaleNumber,
    196- MonthAvgProfits,
    197- BusinessAdminID,
    198- SevenDayIncomes,
    199- MonthIncomes,
    200- OrderSourceType,
    201- DailyAvgRefund,
    202- SevenDayAvgRefund,
    203- MonthAvgRefund,
    204- DailyRefund,
    205- SevenDayRefund,
    206- MonthRefund,
    207- ThreeMonthProfitRate,
    208- ThreeMonthRefundRate,
    209- OtherDailyProfits,
    210- OtherSellerSKUDailyProfitsRate,
    211- OtherSellerSKUSevenDayProfitsRate,
    212- OtherSellerSKUCurrWeekprofits,
    213- OtherSellerSKULastWeekprofits,
    214- OtherSevenDayProfits,
    215- OtherMonthProfits,
    216- OtherMonthAvgProfits,
    217- OtherThreeMonthProfitRate
    218- )
    219- SELECT CustomerID,
    220- SKU,
    221- SellerSKU,
    222- OrderSourceID,
    223- OrderSourceName,
    224- ProductNameCN,
    225- ShipDate,
    226- DailyProductNum,
    227- DailyProfits,
    228- DailyIncomes,
    229- SellerSKUDailyProfitsRate,
    230- SellerSKUSevenDayProfitsRate,
    231- SellerSKUCurrWeekprofits,
    232- SellerSKULastWeekprofits,
    233- SevenDayProfits,
    234- SevenDaySaleNumber,
    235- MonthProfits,
    236- MonthSaleNumber,
    237- MonthAvgProfits,
    238- BusinessAdminID,
    239- SevenDayIncomes,
    240- MonthIncomes,
    241- OrderSourceType,
    242- DailyAvgRefund,
    243- SevenDayAvgRefund,
    244- MonthAvgRefund,
    245- DailyRefund,
    246- SevenDayRefund,
    247- MonthRefund,
    248- ThreeMonthProfitRate,
    249- ThreeMonthRefundRate,
    250- ISNULL(OtherDailyProfits,0) AS OtherDailyProfits,
    251- ISNULL(OtherSellerSKUDailyProfitsRate,0) AS OtherSellerSKUDailyProfitsRate,
    252- ISNULL(OtherSellerSKUSevenDayProfitsRate,0) AS OtherSellerSKUSevenDayProfitsRate,
    253- ISNULL(OtherSellerSKUCurrWeekprofits,0) AS OtherSellerSKUCurrWeekprofits,
    254- ISNULL(OtherSellerSKULastWeekprofits,0) AS OtherSellerSKULastWeekprofits,
    255- ISNULL(OtherSevenDayProfits,0) AS OtherSevenDayProfits,
    256- ISNULL(OtherSevenDayProfits,0) AS OtherMonthProfits,
    257- ISNULL(OtherMonthAvgProfits,0) AS OtherMonthAvgProfits,
    258- ISNULL(OtherThreeMonthProfitRate,0) AS OtherThreeMonthProfitRate
    259- FROM RB_Report_SalesProductProfit (NOLOCK)
    260- WHERE ShipDate &gt; DATEADD(DAY, -90, CurrMaxShipDate)
    261- AND ShipDate &lt;= CurrMaxShipDate
    262- ORDER BY ShipDate;
    263-
    264-
    265-
    266-
    267- --更新SalesProductProfit表的SellerSKUDailyProfitsRate(渠道SKU日平均利润)
    268- --渠道SKU日平均利润率：渠道SKU总的利润率/日销售额
    269- UPDATE SalesProductProfit
    270- SET SellerSKUDailyProfitsRate = CASE
    271- WHEN DailyIncomes = 0 THEN
    272- 0
    273- ELSE
    274- CASE
    275- WHEN DailyProfits &lt; 0 THEN
    276- DailyProfits / ABS(DailyIncomes)
    277- ELSE
    278- DailyProfits / DailyIncomes
    279- END
    280- END,
    281- DailyAvgRefund = CASE
    282- WHEN DailyIncomes = 0 THEN
    283- 0
    284- ELSE
    285- CASE
    286- WHEN DailyIncomes + ABS(DailyRefund) = 0 THEN
    287- 0
    288- ELSE
    289- DailyRefund / (DailyIncomes + ABS(DailyRefund))
    290- END
    291- END,
    292- OtherSellerSKUDailyProfitsRate = CASE
    293- WHEN DailyIncomes = 0 THEN
    294- 0
    295- ELSE
    296- CASE
    297- WHEN OtherDailyProfits &lt; 0 THEN
    298- OtherDailyProfits / ABS(DailyIncomes)
    299- ELSE
    300- OtherDailyProfits / DailyIncomes
    301- END
    302- END
    303- WHERE ShipDate &gt; CurrMaxShipDate;
    304-
    305-
    306- --更新SalesProductProfit表(渠道SKU七天利润计算)
    307- UPDATE rspp
    308- SET SevenDayProfits = o.SevenDayProfits,
    309- SevenDaySaleNumber = o.SevenDaySaleNumber,
    310- SevenDayIncomes = o.SevenDayIncomes,
    311- SellerSKUSevenDayProfitsRate = CASE
    312- WHEN o.SevenDayIncomes = 0 THEN
    313- 0
    314- ELSE
    315- (CASE
    316- WHEN o.SevenDayProfits &lt; 0 THEN
    317- o.SevenDayProfits / ABS(o.SevenDayIncomes)
    318- ELSE
    319- o.SevenDayProfits / o.SevenDayIncomes
    320- END
    321- )
    322- END,
    323- SevenDayRefund = o.SevenDayRefund, --七天退款总额
    324- SevenDayAvgRefund = CASE --七天平均退款率
    325- WHEN o.SevenDayIncomes = 0 THEN
    326- 0
    327- ELSE
    328- CASE
    329- WHEN o.SevenDayIncomes + ABS(o.SevenDayRefund) = 0 THEN
    330- 0
    331- ELSE
    332- o.SevenDayRefund / (o.SevenDayIncomes + ABS(o.SevenDayRefund))
    333- END
    334- END,
    335- OtherSellerSKUSevenDayProfitsRate = CASE
    336- WHEN o.SevenDayIncomes = 0 THEN
    337- 0
    338- ELSE
    339- (CASE
    340- WHEN o.OtherSevenDayProfits &lt; 0 THEN
    341- o.OtherSevenDayProfits / ABS(o.SevenDayIncomes)
    342- ELSE
    343- o.OtherSevenDayProfits / o.SevenDayIncomes
    344- END
    345- )
    346- END
    347- FROM SalesProductProfit AS rspp
    348- INNER JOIN
    349- (
    350- SELECT a.ID,
    351- SUM(rb.DailyProfits) AS SevenDayProfits,
    352- SUM(rb.OtherDailyProfits) AS OtherSevenDayProfits,
    353- SUM(rb.DailyProductNum) AS SevenDaySaleNumber,
    354- SUM(rb.DailyIncomes) AS SevenDayIncomes,
    355- SUM(rb.DailyRefund) AS SevenDayRefund
    356- FROM SalesProductProfit AS a
    357- INNER JOIN SalesProductProfit rb (NOLOCK)
    358- ON rb.CustomerID = a.CustomerID
    359- AND rb.SKU = a.SKU
    360- AND rb.SellerSKU = a.SellerSKU
    361- AND rb.OrderSourceID = a.OrderSourceID
    362- AND DATEDIFF(DAY, rb.ShipDate, a.ShipDate)
    363- BETWEEN 0 AND 6
    364- WHERE a.ShipDate &gt; CurrMaxShipDate
    365- GROUP BY a.ID
    366- ) AS o
    367- ON o.ID = rspp.ID;
    368-
    369-
    370-
    371- --更改SalesProductProfit表的SellerSKUCurrWeekprofits（本周利润）
    372- UPDATE SalesProductProfit
    373- SET SellerSKUCurrWeekprofits =
    374- (
    375- SELECT SUM(DailyProfits)
    376- FROM SalesProductProfit rb (NOLOCK)
    377- WHERE rb.CustomerID = SalesProductProfit.CustomerID
    378- AND rb.SKU = SalesProductProfit.SKU
    379- AND rb.SellerSKU = SalesProductProfit.SellerSKU
    380- AND rb.OrderSourceID = SalesProductProfit.OrderSourceID
    381- AND rb.ShipDate &gt;= DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -1, SalesProductProfit.ShipDate)), 0)
    382- AND rb.ShipDate &lt; DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -1, SalesProductProfit.ShipDate)), 7)
    383- ),
    384- OtherSellerSKUCurrWeekprofits =
    385- (
    386- SELECT SUM(OtherDailyProfits)
    387- FROM SalesProductProfit rb (NOLOCK)
    388- WHERE rb.CustomerID = SalesProductProfit.CustomerID
    389- AND rb.SKU = SalesProductProfit.SKU
    390- AND rb.SellerSKU = SalesProductProfit.SellerSKU
    391- AND rb.OrderSourceID = SalesProductProfit.OrderSourceID
    392- AND rb.ShipDate &gt;= DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -1, SalesProductProfit.ShipDate)), 0)
    393- AND rb.ShipDate &lt; DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -1, SalesProductProfit.ShipDate)), 7)
    394- )
    395- WHERE ShipDate &gt; CurrMaxShipDate;
    396-
    397- --更改SalesProductProfit表的SellerSKULastWeekprofits（上周利润）
    398- UPDATE SalesProductProfit
    399- SET SellerSKULastWeekprofits =
    400- (
    401- SELECT ISNULL(SUM(DailyProfits), 0)
    402- FROM SalesProductProfit rb (NOLOCK)
    403- WHERE rb.CustomerID = SalesProductProfit.CustomerID
    404- AND rb.SKU = SalesProductProfit.SKU
    405- AND rb.SellerSKU = SalesProductProfit.SellerSKU
    406- AND rb.OrderSourceID = SalesProductProfit.OrderSourceID
    407- AND rb.ShipDate &gt;= DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -7, SalesProductProfit.ShipDate)), 0)
    408- AND rb.ShipDate &lt; DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -1, SalesProductProfit.ShipDate)), 0)
    409- ),
    410- OtherSellerSKULastWeekprofits =
    411- (
    412- SELECT ISNULL(SUM(OtherDailyProfits), 0)
    413- FROM SalesProductProfit rb (NOLOCK)
    414- WHERE rb.CustomerID = SalesProductProfit.CustomerID
    415- AND rb.SKU = SalesProductProfit.SKU
    416- AND rb.SellerSKU = SalesProductProfit.SellerSKU
    417- AND rb.OrderSourceID = SalesProductProfit.OrderSourceID
    418- AND rb.ShipDate &gt;= DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -7, SalesProductProfit.ShipDate)), 0)
    419- AND rb.ShipDate &lt; DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -1, SalesProductProfit.ShipDate)), 0)
    420- )
    421- WHERE ShipDate &gt; CurrMaxShipDate;
    422-
    423- --更新SalesProductProfit表的MonthProfits(渠道SKU月总利润)
    424- UPDATE rspp
    425- SET MonthProfits = o.MonthProfits,
    426- MonthSaleNumber = o.MonthSaleNumber,
    427- MonthIncomes = o.MonthIncomes,
    428- MonthAvgProfits = CASE
    429- WHEN o.MonthIncomes = 0 THEN
    430- 0
    431- ELSE
    432- (CASE
    433- WHEN o.MonthProfits &lt; 0 THEN
    434- o.MonthProfits / ABS(o.MonthIncomes)
    435- ELSE
    436- o.MonthProfits / o.MonthIncomes
    437- END
    438- )
    439- END,
    440- MonthRefund = o.MonthRefund, --月总退款
    441- MonthAvgRefund = CASE --月平均退款率
    442- WHEN o.MonthIncomes = 0 THEN
    443- 0
    444- ELSE
    445- CASE
    446- WHEN o.MonthIncomes + ABS(o.MonthRefund) = 0 THEN
    447- 0
    448- ELSE
    449- o.MonthRefund / (o.MonthIncomes + ABS(o.MonthRefund))
    450- END
    451- END,
    452- OtherMonthProfits = o.OtherMonthProfits,
    453- OtherMonthAvgProfits = CASE
    454- WHEN o.MonthIncomes = 0 THEN
    455- 0
    456- ELSE
    457- (CASE
    458- WHEN o.OtherMonthProfits &lt; 0 THEN
    459- o.OtherMonthProfits / ABS(o.MonthIncomes)
    460- ELSE
    461- o.OtherMonthProfits / o.MonthIncomes
    462- END
    463- )
    464- END
    465- FROM SalesProductProfit AS rspp
    466- INNER JOIN
    467- (
    468- SELECT a.ID,
    469- SUM(rb.DailyProfits) AS MonthProfits,
    470- SUM(rb.DailyProductNum) AS MonthSaleNumber,
    471- SUM(rb.DailyIncomes) AS MonthIncomes,
    472- SUM(rb.DailyRefund) AS MonthRefund,
    473- SUM(rb.OtherDailyProfits) AS OtherMonthProfits
    474- FROM SalesProductProfit AS a
    475- INNER JOIN SalesProductProfit rb (NOLOCK)
    476- ON rb.CustomerID = a.CustomerID
    477- AND rb.SKU = a.SKU
    478- AND rb.SellerSKU = a.SellerSKU
    479- AND rb.OrderSourceID = a.OrderSourceID
    480- AND DATEDIFF(DAY, rb.ShipDate, a.ShipDate)
    481- BETWEEN 0 AND 29
    482- WHERE a.ShipDate &gt; CurrMaxShipDate
    483- GROUP BY a.ID
    484- ) AS o
    485- ON o.ID = rspp.ID;
    486-
    487- --更新三个月利润率和退款率
    488- UPDATE rspp
    489- SET ThreeMonthProfitRate = CASE
    490- WHEN o.TreeMonthIncomes = 0 THEN
    491- 0
    492- ELSE
    493- (CASE
    494- WHEN o.TreeMonthProfits &lt; 0 THEN
    495- o.TreeMonthProfits / ABS(o.TreeMonthIncomes)
    496- ELSE
    497- o.TreeMonthProfits / o.TreeMonthIncomes
    498- END
    499- )
    500- END,
    501- ThreeMonthRefundRate = CASE
    502- WHEN o.TreeMonthIncomes = 0 THEN
    503- 0
    504- ELSE
    505- CASE
    506- WHEN o.TreeMonthIncomes + ABS(o.TreeMonthRefund) = 0 THEN
    507- 0
    508- ELSE
    509- o.TreeMonthRefund / (o.TreeMonthIncomes + ABS(o.TreeMonthRefund))
    510- END
    511- END,
    512- OtherThreeMonthProfitRate = CASE
    513- WHEN o.TreeMonthIncomes = 0 THEN
    514- 0
    515- ELSE
    516- (CASE
    517- WHEN o.OtherTreeMonthProfits &lt; 0 THEN
    518- o.OtherTreeMonthProfits / ABS(o.TreeMonthIncomes)
    519- ELSE
    520- o.OtherTreeMonthProfits / o.TreeMonthIncomes
    521- END
    522- )
    523- END
    524- FROM SalesProductProfit AS rspp
    525- INNER JOIN
    526- (
    527- SELECT a.ID,
    528- SUM(rb.DailyProfits) AS TreeMonthProfits,
    529- SUM(rb.DailyProductNum) AS TreeMonthSaleNumber,
    530- SUM(rb.DailyIncomes) AS TreeMonthIncomes,
    531- SUM(rb.DailyRefund) AS TreeMonthRefund,
    532- SUM(rb.OtherDailyProfits) AS OtherTreeMonthProfits
    533- FROM SalesProductProfit AS a
    534- INNER JOIN SalesProductProfit rb (NOLOCK)
    535- ON rb.CustomerID = a.CustomerID
    536- AND rb.SKU = a.SKU
    537- AND rb.SellerSKU = a.SellerSKU
    538- AND rb.OrderSourceID = a.OrderSourceID
    539- AND DATEDIFF(DAY, rb.ShipDate, a.ShipDate)
    540- BETWEEN 0 AND 90
    541- WHERE a.ShipDate &gt; CurrMaxShipDate
    542- GROUP BY a.ID
    543- ) AS o
    544- ON o.ID = rspp.ID;
    545- --
    546-
    547- MERGE RB_Report_SalesProductProfit AS target
    548- USING
    549- (
    550- SELECT CustomerID,
    551- SKU,
    552- SellerSKU,
    553- OrderSourceID,
    554- OrderSourceName,
    555- ProductNameCN,
    556- ShipDate,
    557- DailyProductNum,
    558- DailyProfits,
    559- DailyIncomes,
    560- SellerSKUDailyProfitsRate,
    561- SellerSKUSevenDayProfitsRate,
    562- SellerSKUCurrWeekprofits,
    563- SellerSKULastWeekprofits,
    564- SevenDayProfits,
    565- SevenDaySaleNumber,
    566- MonthProfits,
    567- MonthSaleNumber,
    568- MonthAvgProfits,
    569- BusinessAdminID,
    570- SevenDayIncomes,
    571- MonthIncomes,
    572- UpdateTime,
    573- OrderSourceType,
    574- DailyAvgRefund,
    575- SevenDayAvgRefund,
    576- MonthAvgRefund,
    577- DailyRefund,
    578- SevenDayRefund,
    579- MonthRefund,
    580- ThreeMonthProfitRate,
    581- ThreeMonthRefundRate,
    582- OtherDailyProfits,
    583- OtherSellerSKUDailyProfitsRate,
    584- OtherSellerSKUSevenDayProfitsRate,
    585- OtherSellerSKUCurrWeekprofits,
    586- OtherSellerSKULastWeekprofits,
    587- OtherSevenDayProfits,
    588- OtherMonthProfits,
    589- OtherMonthAvgProfits,
    590- OtherThreeMonthProfitRate
    591- FROM SalesProductProfit
    592- WHERE ShipDate &gt; CurrMaxShipDate
    593- ) AS source
    594- (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,
    595- OtherDailyProfits,
    596- OtherSellerSKUDailyProfitsRate,
    597- OtherSellerSKUSevenDayProfitsRate,
    598- OtherSellerSKUCurrWeekprofits,
    599- OtherSellerSKULastWeekprofits,
    600- OtherSevenDayProfits,
    601- OtherMonthProfits,
    602- OtherMonthAvgProfits,
    603- OtherThreeMonthProfitRate
    604- )
    605- ON (
    606- target.CustomerID = source.CustomerID
    607- AND target.SKU = source.SKU
    608- AND target.SellerSKU = source.SellerSKU
    609- AND target.OrderSourceID = source.OrderSourceID
    610- AND target.OrderSourceName = source.OrderSourceName
    611- AND target.ProductNameCN = source.ProductNameCN
    612- AND target.ShipDate = source.ShipDate
    613- AND target.BusinessAdminID = source.BusinessAdminID
    614- AND target.OrderSourceType = source.OrderSourceType
    615- )
    616- WHEN MATCHED AND target.ShipDate &gt; CurrMaxShipDate
    617- AND (
    618- target.DailyProductNum != source.DailyProductNum
    619- OR target.DailyProfits != source.DailyProfits
    620- OR target.SellerSKUDailyProfitsRate != source.SellerSKUDailyProfitsRate
    621- OR target.SellerSKUSevenDayProfitsRate != source.SellerSKUSevenDayProfitsRate
    622- OR target.SellerSKUCurrWeekprofits != source.SellerSKUCurrWeekprofits
    623- OR target.SellerSKULastWeekprofits != source.SellerSKULastWeekprofits
    624- OR target.SevenDayProfits != source.SevenDayProfits
    625- OR target.MonthProfits != source.MonthProfits
    626- OR target.MonthAvgProfits != source.MonthAvgProfits
    627- OR target.ThreeMonthProfitRate != source.ThreeMonthProfitRate
    628- OR target.ThreeMonthRefundRate != source.ThreeMonthRefundRate
    629- OR target.OtherDailyProfits != source.OtherDailyProfits
    630- OR target.OtherSellerSKUDailyProfitsRate != source.OtherSellerSKUDailyProfitsRate
    631- OR target.OtherSellerSKUSevenDayProfitsRate != source.OtherSellerSKUSevenDayProfitsRate
    632- OR target.OtherSellerSKUCurrWeekprofits != source.OtherSellerSKUCurrWeekprofits
    633- OR target.OtherSellerSKULastWeekprofits != source.OtherSellerSKULastWeekprofits
    634- OR target.OtherSevenDayProfits != source.OtherSevenDayProfits
    635- OR target.OtherMonthProfits != source.OtherMonthProfits
    636- OR target.OtherMonthAvgProfits != source.OtherMonthAvgProfits
    637- OR target.OtherThreeMonthProfitRate != source.OtherThreeMonthProfitRate
    638-
    639- ) THEN
    640- UPDATE SET target.DailyProductNum = source.DailyProductNum,
    641- target.DailyProfits = source.DailyProfits,
    642- target.DailyIncomes = source.DailyIncomes,
    643- target.SellerSKUDailyProfitsRate = source.SellerSKUDailyProfitsRate,
    644- target.SellerSKUSevenDayProfitsRate = source.SellerSKUSevenDayProfitsRate,
    645- target.SellerSKUCurrWeekprofits = source.SellerSKUCurrWeekprofits,
    646- target.SellerSKULastWeekprofits = source.SellerSKULastWeekprofits,
    647- target.SevenDayProfits = source.SevenDayProfits,
    648- target.SevenDaySaleNumber = source.SevenDaySaleNumber,
    649- target.MonthProfits = source.MonthProfits,
    650- target.MonthSaleNumber = source.MonthSaleNumber,
    651- target.MonthAvgProfits = source.MonthAvgProfits,
    652- target.SevenDayIncomes = source.SevenDayIncomes,
    653- target.MonthIncomes = source.MonthIncomes,
    654- target.DailyAvgRefund = source.DailyAvgRefund,
    655- target.SevenDayAvgRefund = source.SevenDayAvgRefund,
    656- target.MonthAvgRefund = source.MonthAvgRefund,
    657- target.DailyRefund = source.DailyRefund,
    658- target.SevenDayRefund = source.SevenDayRefund,
    659- target.MonthRefund = source.MonthRefund,
    660- target.ThreeMonthProfitRate= source.ThreeMonthProfitRate,
    661- target.ThreeMonthRefundRate= source.ThreeMonthRefundRate,
    662- target.OtherDailyProfits= source.OtherDailyProfits,
    663- target.OtherSellerSKUDailyProfitsRate= source.OtherSellerSKUDailyProfitsRate,
    664- target.OtherSellerSKUSevenDayProfitsRate= source.OtherSellerSKUSevenDayProfitsRate,
    665- target.OtherSellerSKUCurrWeekprofits= source.OtherSellerSKUCurrWeekprofits,
    666- target.OtherSellerSKULastWeekprofits= source.OtherSellerSKULastWeekprofits,
    667- target.OtherSevenDayProfits= source.OtherSevenDayProfits,
    668- target.OtherMonthProfits= source.OtherMonthProfits,
    669- target.OtherMonthAvgProfits= source.OtherMonthAvgProfits,
    670- target.OtherThreeMonthProfitRate= source.OtherThreeMonthProfitRate,
    671- target.UpdateTime = GETDATE()
    672- WHEN NOT MATCHED BY TARGET THEN
    673- INSERT
    674- (
    675- CustomerID,
    676- SKU,
    677- SellerSKU,
    678- OrderSourceID,
    679- OrderSourceName,
    680- ProductNameCN,
    681- ShipDate,
    682- DailyProductNum,
    683- DailyProfits,
    684- DailyIncomes,
    685- SellerSKUDailyProfitsRate,
    686- SellerSKUSevenDayProfitsRate,
    687- SellerSKUCurrWeekprofits,
    688- SellerSKULastWeekprofits,
    689- SevenDayProfits,
    690- SevenDaySaleNumber,
    691- MonthProfits,
    692- MonthSaleNumber,
    693- MonthAvgProfits,
    694- BusinessAdminID,
    695- SevenDayIncomes,
    696- MonthIncomes,
    697- OrderSourceType,
    698- DailyAvgRefund,
    699- SevenDayAvgRefund,
    700- MonthAvgRefund,
    701- DailyRefund,
    702- SevenDayRefund,
    703- MonthRefund,
    704- ThreeMonthProfitRate,
    705- ThreeMonthRefundRate,
    706- OtherDailyProfits,
    707- OtherSellerSKUDailyProfitsRate,
    708- OtherSellerSKUSevenDayProfitsRate,
    709- OtherSellerSKUCurrWeekprofits,
    710- OtherSellerSKULastWeekprofits,
    711- OtherSevenDayProfits,
    712- OtherMonthProfits,
    713- OtherMonthAvgProfits,
    714- OtherThreeMonthProfitRate,
    715- UpdateTime
    716- )
    717- VALUES
    718- (CustomerID, SKU, SellerSKU, OrderSourceID, OrderSourceName, ProductNameCN, ShipDate, DailyProductNum,
    719- DailyProfits, DailyIncomes, SellerSKUDailyProfitsRate, SellerSKUSevenDayProfitsRate, SellerSKUCurrWeekprofits,
    720- SellerSKULastWeekprofits, SevenDayProfits, SevenDaySaleNumber, MonthProfits, MonthSaleNumber, MonthAvgProfits,
    721- BusinessAdminID, SevenDayIncomes, MonthIncomes, OrderSourceType, DailyAvgRefund, SevenDayAvgRefund,
    722- MonthAvgRefund, DailyRefund, SevenDayRefund, MonthRefund,ThreeMonthProfitRate,ThreeMonthRefundRate,
    723- OtherDailyProfits,
    724- OtherSellerSKUDailyProfitsRate,
    725- OtherSellerSKUSevenDayProfitsRate,
    726- OtherSellerSKUCurrWeekprofits,
    727- OtherSellerSKULastWeekprofits,
    728- OtherSevenDayProfits,
    729- OtherMonthProfits,
    730- OtherMonthAvgProfits,
    731- OtherThreeMonthProfitRate, GETDATE())
    732- WHEN NOT MATCHED BY SOURCE AND target.ShipDate &gt; CurrMaxShipDate THEN
    733- DELETE;
    734- --OUTPUT action,
    735- -- inserted.*,
    736- -- deleted.*;
    737-
    738-
    739- DROP TABLE SalesProductProfit
    740-
    741- --初始化SKU产品利润统计数据
    742- INSERT INTO RB_Report_SKUProductProfitStastic
    743- (
    744- CustomerID,
    745- SKU,
    746- ProductNameCN,
    747- DailyProfits,
    748- DailyIncomes,
    749- DailyAvgSalePrice,
    750- DailySaleNumber,
    751- ShipDate,
    752- DailyAvgProfits,
    753- SevenDayAvgProfits,
    754- SKUCurrWeekprofits,
    755- SKULastWeekprofits,
    756- DailyRefund, --日总退款
    757- SevenDayRefund, --七天总退款
    758- MonthRefund, --月总退款
    759- DailyAvgRefund, --日平均退款率
    760- SevenDayAvgRefund, --七天平均退款率
    761- MonthAvgRefund --月平均退款率
    762- )
    763- SELECT CustomerID,
    764- SKU,
    765- ProductNameCN,
    766- SUM(DailyProfits),
    767- SUM(DailyIncomes),
    768- CASE
    769- WHEN SUM(DailyProductNum) = 0 THEN
    770- 0
    771- ELSE
    772- CASE
    773- WHEN SUM(DailyIncomes) &lt; 0 THEN
    774- SUM(DailyIncomes) / ABS(SUM(DailyProductNum))
    775- ELSE
    776- SUM(DailyIncomes) / SUM(DailyProductNum)
    777- END
    778- END,
    779- SUM(DailyProductNum),
    780- ShipDate,
    781- CASE
    782- WHEN SUM(DailyIncomes) = 0 THEN
    783- 0
    784- ELSE
    785- CASE
    786- WHEN SUM(DailyIncomes) &lt; 0 THEN
    787- SUM(DailyProfits) / ABS(SUM(DailyIncomes))
    788- ELSE
    789- SUM(DailyProfits) / SUM(DailyIncomes)
    790- END
    791- END,
    792- 0,
    793- 0,
    794- 0,
    795- SUM(DailyRefund), --日总退款
    796- 0,
    797- 0,
    798- CASE
    799- WHEN SUM(DailyIncomes) = 0 THEN
    800- 0
    801- ELSE
    802- CASE
    803- WHEN SUM(DailyIncomes + ABS(DailyRefund)) = 0 THEN
    804- 0
    805- ELSE
    806- SUM(DailyRefund) / SUM(DailyIncomes + ABS(DailyRefund))
    807- END
    808- END, --日平均退款率
    809- 0,
    810- 0
    811- FROM RB_Report_SalesProductProfit (NOLOCK)
    812- WHERE ShipDate &gt; CurrMaxShipDate
    813- GROUP BY SKU,
    814- CustomerID,
    815- ShipDate,
    816- ProductNameCN
    817- ORDER BY ShipDate;
    818-
    819-
    820- --更新RB_Report_SKUProductProfitStastic表(渠道SKU七天总利润)
    821- UPDATE rspp
    822- SET SevenDayProfits = o.SevenDayProfits,
    823- SevenDaySaleNumber = o.SevenDaySaleNumber,
    824- SevenDayIncomes = o.SevenDayIncomes,
    825- SevenDayAvgProfits = CASE
    826- WHEN o.SevenDayIncomes = 0 THEN
    827- 0
    828- ELSE
    829- (CASE
    830- WHEN o.SevenDayProfits &lt; 0 THEN
    831- o.SevenDayProfits / ABS(o.SevenDayIncomes)
    832- ELSE
    833- o.SevenDayProfits / o.SevenDayIncomes
    834- END
    835- )
    836- END,
    837- SevenDayRefund = o.SevenDayRefund, --七天总退款
    838- SevenDayAvgRefund = CASE --七天平均退款率
    839- WHEN o.SevenDayIncomes = 0 THEN
    840- 0
    841- ELSE
    842- CASE
    843- WHEN o.SevenDayIncomes + ABS(o.SevenDayRefund) = 0 THEN
    844- 0
    845- ELSE
    846- o.SevenDayRefund / (o.SevenDayIncomes + ABS(o.SevenDayRefund))
    847- END
    848- END
    849- FROM RB_Report_SKUProductProfitStastic AS rspp
    850- INNER JOIN
    851- (
    852- SELECT a.ID,
    853- SUM(rb.DailyProfits) AS SevenDayProfits,
    854- SUM(rb.DailySaleNumber) AS SevenDaySaleNumber,
    855- SUM(ISNULL(rb.DailyIncomes, 0)) AS SevenDayIncomes,
    856- SUM(rb.DailyRefund) AS SevenDayRefund
    857- FROM RB_Report_SKUProductProfitStastic AS a
    858- INNER JOIN RB_Report_SKUProductProfitStastic rb (NOLOCK)
    859- ON rb.CustomerID = a.Custo
    001- <p>事务执行时间过长，应分步骤分批处理数据，单次事务执行时间不能过长</p>
    001+ <p><a href="https://alidocs.dingtalk.com/i/nodes/2Amq4vjg89gab9DztpXwrB0aV3kdP0wQ">https://alidocs.dingtalk.com/i/nodes/2Amq4vjg89gab9DztpXwrB0aV3kdP0wQ</a></p>
    002- <p><img src="https://irobotbox-hd1.oss-cn-hangzhou.aliyuncs.com/1/Kindeditor/2025/12/15/1f45634952984ddab228228fe82d6924/665d09b3-c061-4055-a6fa-1537ecabb8e4.png" title="from clipboard" alt="from clipboard" /></p>
    003- <p>--修改版本：任务ID:3956 利润相关&gt;&gt;产品利润管理页面增加退款率的分析展示</p>
    004- <p>CREATE PROC [dbo].[Proc_SaleProductProfitStastic]</p>
    005- <p>AS</p>
    006- <p>BEGIN</p>
    007- <p>--第一步：删除三个月前的数据</p>
    008- <p>DELETE FROM RB_Report_SalesProductProfit</p>
    009- <p>WHERE ShipDate &lt; CONVERT(NVARCHAR(100), DATEADD(MONTH, -4, GETDATE()), 23);</p>
    010- <p>DELETE FROM RB_Report_SKUProductProfitStastic</p>
    011- <p>WHERE ShipDate &lt; CONVERT(NVARCHAR(100), DATEADD(MONTH, -4, GETDATE()), 23);</p>
    012- <p><br /></p>
    013- <p>DECLARE CurrMaxShipDate DATE,</p>
    014- <p>NeedInCount INT,</p>
    015- <p>BeforeFifteenDate DATE;</p>
    016- <p><br /></p>
    017- <p>--查询最新的发货日期</p>
    018- <p>SET BeforeFifteenDate = CONVERT(DATE, DATEADD(DAY, -15, GETDATE())) --CONVERT(DATE, DATEADD(DAY, -15, GETDATE()));</p>
    019- <p>SET CurrMaxShipDate =</p>
    020- <p>(</p>
    021- <p>SELECT CONVERT(NVARCHAR(100),</p>
    022- <p>ISNULL( CASE</p>
    023- <p>WHEN MAX(ShipDate) &gt; BeforeFifteenDate THEN</p>
    024- <p>BeforeFifteenDate</p>
    025- <p>ELSE</p>
    026- <p>MAX(ShipDate)</p>
    027- <p>END,</p>
    028- <p>DATEADD(MONTH, -3, GETDATE())</p>
    029- <p>),</p>
    030- <p>23</p>
    031- <p>)</p>
    032- <p>FROM RB_Report_SalesProductProfit (NOLOCK)</p>
    033- <p>);</p>
    034- <p><br /></p>
    035- <p><br /></p>
    036- <p>--删除CurrMaxShipDate 之后的所有数据，确保数据的齐全（插入的时间&gt;ShipDate）</p>
    037- <p>--DELETE FROM RB_Report_SalesProductProfit</p>
    038- <p>-- WHERE ShipDate &gt; CurrMaxShipDate</p>
    039- <p>DELETE FROM RB_Report_SKUProductProfitStastic</p>
    040- <p>WHERE ShipDate &gt; CurrMaxShipDate;</p>
    041- <p><br /></p>
    042- <p>--需要新增的数据</p>
    043- <p>CREATE TABLE SalesProductProfit</p>
    044- <p>(</p>
    045- <p>[ID] [INT] NOT NULL IDENTITY(1, 1),</p>
    046- <p>[CustomerID] [INT] NOT NULL,</p>
    047- <p>[SKU] [NVARCHAR](200) COLLATE Chinese_PRC_CI_AS NOT NULL,</p>
    048- <p>[SellerSKU] [NVARCHAR](400) COLLATE Chinese_PRC_CI_AS NOT NULL,</p>
    049- <p>[OrderSourceID] [INT] NOT NULL,</p>
    050- <p>[OrderSourceName] [NVARCHAR](400) COLLATE Chinese_PRC_CI_AS NOT NULL,</p>
    051- <p>[ProductNameCN] [NVARCHAR](2000) COLLATE Chinese_PRC_CI_AS NULL,</p>
    052- <p>[ShipDate] [DATE] NOT NULL,</p>
    053- <p>[DailyProductNum] [INT] NOT NULL,</p>
    054- <p>[DailyProfits] [MONEY] NOT NULL,</p>
    055- <p>[DailyIncomes] [MONEY] NOT NULL,</p>
    056- <p>[SellerSKUDailyProfitsRate] [DECIMAL](18, 2) NULL,</p>
    057- <p>[SellerSKUSevenDayProfitsRate] [DECIMAL](18, 2) NULL,</p>
    058- <p>[SellerSKUCurrWeekprofits] [MONEY] NULL,</p>
    059- <p>[SellerSKULastWeekprofits] [MONEY] NULL,</p>
    060- <p>[SevenDayProfits] [MONEY] NULL,</p>
    061- <p>[SevenDaySaleNumber] [INT] NULL,</p>
    062- <p>[MonthProfits] [MONEY] NULL,</p>
    063- <p>[MonthSaleNumber] [INT] NULL,</p>
    064- <p>[MonthAvgProfits] [MONEY] NULL,</p>
    065- <p>[BusinessAdminID] [INT] NULL,</p>
    066- <p>[SevenDayIncomes] [MONEY] NULL</p>
    067- <p>DEFAULT ((0)),</p>
    068- <p>[MonthIncomes] [MONEY] NULL</p>
    069- <p>DEFAULT ((0)),</p>
    070- <p>[UpdateTime] [DATETIME] NULL,</p>
    071- <p>[OrderSourceType] [INT] NULL</p>
    072- <p>DEFAULT ((0)),</p>
    073- <p>[DailyAvgRefund] [MONEY] NULL,</p>
    074- <p>[SevenDayAvgRefund] [MONEY] NULL,</p>
    075- <p>[MonthAvgRefund] [MONEY] NULL,</p>
    076- <p>[DailyRefund] [MONEY] NULL,</p>
    077- <p>[SevenDayRefund] [MONEY] NULL,</p>
    078- <p>[MonthRefund] [MONEY] NULL,</p>
    079- <p>[ThreeMonthProfitRate] [DECIMAL](18, 4) NULL,</p>
    080- <p>[ThreeMonthRefundRate] [DECIMAL](18, 4) NULL,</p>
    081- <p>[OtherDailyProfits] [MONEY] NULL,</p>
    082- <p>[OtherSellerSKUDailyProfitsRate] DECIMAL(18,4) NULL,</p>
    083- <p>[OtherSellerSKUSevenDayProfitsRate] DECIMAL(18,4) NULL,</p>
    084- <p>[OtherSellerSKUCurrWeekprofits] MONEY NULL,</p>
    085- <p>[OtherSellerSKULastWeekprofits] MONEY NULL,</p>
    086- <p>[OtherSevenDayProfits] MONEY NULL,</p>
    087- <p>[OtherMonthProfits] MONEY NULL,</p>
    088- <p>[OtherMonthAvgProfits] MONEY NULL,</p>
    089- <p>[OtherThreeMonthProfitRate] DECIMAL(18,4) NULL</p>
    090- <p>);</p>
    091- <p>CREATE INDEX IX_ShipDate ON SalesProductProfit(ShipDate);</p>
    092- <p>CREATE INDEX IX_CustomerID ON SalesProductProfit(CustomerID,SKU,SellerSKU,OrderSourceID,ShipDate);</p>
    093- <p><br /></p>
    094- <p>INSERT INTO SalesProductProfit</p>
    095- <p>(</p>
    096- <p>CustomerID,</p>
    097- <p>SKU,</p>
    098- <p>SellerSKU,</p>
    099- <p>OrderSourceID,</p>
    100- <p>OrderSourceName,</p>
    101- <p>ProductNameCN,</p>
    102- <p>ShipDate,</p>
    103- <p>DailyProductNum,</p>
    104- <p>DailyProfits,</p>
    105- <p>DailyIncomes,</p>
    106- <p>SevenDayProfits,</p>
    107- <p>SevenDaySaleNumber,</p>
    108- <p>MonthProfits,</p>
    109- <p>MonthSaleNumber,</p>
    110- <p>MonthAvgProfits,</p>
    111- <p>BusinessAdminID,</p>
    112- <p>OrderSourceType,</p>
    113- <p>DailyRefund,</p>
    114- <p>SevenDayRefund,</p>
    115- <p>MonthRefund,</p>
    116- <p>DailyAvgRefund,</p>
    117- <p>SevenDayAvgRefund,</p>
    118- <p>MonthAvgRefund,</p>
    119- <p>ThreeMonthProfitRate,</p>
    120- <p>ThreeMonthRefundRate,</p>
    121- <p>OtherDailyProfits,</p>
    122- <p>OtherSevenDayProfits,</p>
    123- <p>OtherMonthProfits,</p>
    124- <p>OtherMonthAvgProfits,</p>
    125- <p>OtherThreeMonthProfitRate</p>
    126- <p>)</p>
    127- <p>SELECT CustomerID,</p>
    128- <p>SKU,</p>
    129- <p>SellerSKU,</p>
    130- <p>OrderSourceID,</p>
    131- <p>OrderSourceName,</p>
    132- <p>ProductNameCN,</p>
    133- <p>ShipDate,</p>
    134- <p>SUM(ProductNum + ProdutNumUpdate),</p>
    135- <p>SUM(Profits),</p>
    136- <p>SUM(Incomes),</p>
    137- <p>0, --七天总利润</p>
    138- <p>0, --七天总销量</p>
    139- <p>0, --月利润</p>
    140- <p>0, --月总销量</p>
    141- <p>0, --月平均利润率</p>
    142- <p><br /></p>
    143- <p>BusinessAdminID,</p>
    144- <p>OrderSourceType,</p>
    145- <p>SUM(SalesRefunds),</p>
    146- <p>0, --七天总退款</p>
    147- <p>0, --月总退款</p>
    148- <p>0, --日平均退款率</p>
    149- <p>0, --七天平均退款率</p>
    150- <p>0, --月平均退款率</p>
    151- <p>0, --三个月利润率</p>
    152- <p>0, --三个月退款率</p>
    153- <p><br /></p>
    154- <p>SUM(Profits+ISNULL(OtherAdvertisingFee,0)+ISNULL(OtherPlatformFee,0)+ISNULL(OtherYunCangFee,0)+ISNULL(OtherReturnFee,0)), --日利润（含其他）</p>
    155- <p>0, --七天总利润 (含其他）</p>
    156- <p>0, --月利润（含其他）</p>
    157- <p>0, --月平均利润率（含其他）</p>
    158- <p>0 --三个月利润率（含其他）</p>
    159- <p>FROM RB_Report_OrderSettlement (NOLOCK)</p>
    160- <p>WHERE ShipDate &gt; CurrMaxShipDate</p>
    161- <p>AND SKU !=</p>
    162- <p>AND SellerSKU !=</p>
    163- <p>GROUP BY CustomerID,</p>
    164- <p>SKU,</p>
    165- <p>SellerSKU,</p>
    166- <p>OrderSourceID,</p>
    167- <p>OrderSourceName,</p>
    168- <p>ProductNameCN,</p>
    169- <p>ShipDate,</p>
    170- <p>BusinessAdminID,</p>
    171- <p>OrderSourceType</p>
    172- <p>ORDER BY ShipDate;</p>
    173- <p><br /></p>
    174- <p><br /></p>
    175- <p><br /></p>
    176- <p>INSERT INTO SalesProductProfit</p>
    177- <p>(</p>
    178- <p>CustomerID,</p>
    179- <p>SKU,</p>
    180- <p>SellerSKU,</p>
    181- <p>OrderSourceID,</p>
    182- <p>OrderSourceName,</p>
    183- <p>ProductNameCN,</p>
    184- <p>ShipDate,</p>
    185- <p>DailyProductNum,</p>
    186- <p>DailyProfits,</p>
    187- <p>DailyIncomes,</p>
    188- <p>SellerSKUDailyProfitsRate,</p>
    189- <p>SellerSKUSevenDayProfitsRate,</p>
    190- <p>SellerSKUCurrWeekprofits,</p>
    191- <p>SellerSKULastWeekprofits,</p>
    192- <p>SevenDayProfits,</p>
    193- <p>SevenDaySaleNumber,</p>
    194- <p>MonthProfits,</p>
    195- <p>MonthSaleNumber,</p>
    196- <p>MonthAvgProfits,</p>
    197- <p>BusinessAdminID,</p>
    198- <p>SevenDayIncomes,</p>
    199- <p>MonthIncomes,</p>
    200- <p>OrderSourceType,</p>
    201- <p>DailyAvgRefund,</p>
    202- <p>SevenDayAvgRefund,</p>
    203- <p>MonthAvgRefund,</p>
    204- <p>DailyRefund,</p>
    205- <p>SevenDayRefund,</p>
    206- <p>MonthRefund,</p>
    207- <p>ThreeMonthProfitRate,</p>
    208- <p>ThreeMonthRefundRate,</p>
    209- <p>OtherDailyProfits,</p>
    210- <p>OtherSellerSKUDailyProfitsRate,</p>
    211- <p>OtherSellerSKUSevenDayProfitsRate,</p>
    212- <p>OtherSellerSKUCurrWeekprofits,</p>
    213- <p>OtherSellerSKULastWeekprofits,</p>
    214- <p>OtherSevenDayProfits,</p>
    215- <p>OtherMonthProfits,</p>
    216- <p>OtherMonthAvgProfits,</p>
    217- <p>OtherThreeMonthProfitRate</p>
    218- <p>)</p>
    219- <p>SELECT CustomerID,</p>
    220- <p>SKU,</p>
    221- <p>SellerSKU,</p>
    222- <p>OrderSourceID,</p>
    223- <p>OrderSourceName,</p>
    224- <p>ProductNameCN,</p>
    225- <p>ShipDate,</p>
    226- <p>DailyProductNum,</p>
    227- <p>DailyProfits,</p>
    228- <p>DailyIncomes,</p>
    229- <p>SellerSKUDailyProfitsRate,</p>
    230- <p>SellerSKUSevenDayProfitsRate,</p>
    231- <p>SellerSKUCurrWeekprofits,</p>
    232- <p>SellerSKULastWeekprofits,</p>
    233- <p>SevenDayProfits,</p>
    234- <p>SevenDaySaleNumber,</p>
    235- <p>MonthProfits,</p>
    236- <p>MonthSaleNumber,</p>
    237- <p>MonthAvgProfits,</p>
    238- <p>BusinessAdminID,</p>
    239- <p>SevenDayIncomes,</p>
    240- <p>MonthIncomes,</p>
    241- <p>OrderSourceType,</p>
    242- <p>DailyAvgRefund,</p>
    243- <p>SevenDayAvgRefund,</p>
    244- <p>MonthAvgRefund,</p>
    245- <p>DailyRefund,</p>
    246- <p>SevenDayRefund,</p>
    247- <p>MonthRefund,</p>
    248- <p>ThreeMonthProfitRate,</p>
    249- <p>ThreeMonthRefundRate,</p>
    250- <p>ISNULL(OtherDailyProfits,0) AS OtherDailyProfits,</p>
    251- <p>ISNULL(OtherSellerSKUDailyProfitsRate,0) AS OtherSellerSKUDailyProfitsRate,</p>
    252- <p>ISNULL(OtherSellerSKUSevenDayProfitsRate,0) AS OtherSellerSKUSevenDayProfitsRate,</p>
    253- <p>ISNULL(OtherSellerSKUCurrWeekprofits,0) AS OtherSellerSKUCurrWeekprofits,</p>
    254- <p>ISNULL(OtherSellerSKULastWeekprofits,0) AS OtherSellerSKULastWeekprofits,</p>
    255- <p>ISNULL(OtherSevenDayProfits,0) AS OtherSevenDayProfits,</p>
    256- <p>ISNULL(OtherSevenDayProfits,0) AS OtherMonthProfits,</p>
    257- <p>ISNULL(OtherMonthAvgProfits,0) AS OtherMonthAvgProfits,</p>
    258- <p>ISNULL(OtherThreeMonthProfitRate,0) AS OtherThreeMonthProfitRate</p>
    259- <p>FROM RB_Report_SalesProductProfit (NOLOCK)</p>
    260- <p>WHERE ShipDate &gt; DATEADD(DAY, -90, CurrMaxShipDate)</p>
    261- <p>AND ShipDate &lt;= CurrMaxShipDate</p>
    262- <p>ORDER BY ShipDate;</p>
    263- <p><br /></p>
    264- <p><br /></p>
    265- <p><br /></p>
    266- <p><br /></p>
    267- <p>--更新SalesProductProfit表的SellerSKUDailyProfitsRate(渠道SKU日平均利润)</p>
    268- <p>--渠道SKU日平均利润率：渠道SKU总的利润率/日销售额</p>
    269- <p>UPDATE SalesProductProfit</p>
    270- <p>SET SellerSKUDailyProfitsRate = CASE</p>
    271- <p>WHEN DailyIncomes = 0 THEN</p>
    272- <p>0</p>
    273- <p>ELSE</p>
    274- <p>CASE</p>
    275- <p>WHEN DailyProfits &lt; 0 THEN</p>
    276- <p>DailyProfits / ABS(DailyIncomes)</p>
    277- <p>ELSE</p>
    278- <p>DailyProfits / DailyIncomes</p>
    279- <p>END</p>
    280- <p>END,</p>
    281- <p>DailyAvgRefund = CASE</p>
    282- <p>WHEN DailyIncomes = 0 THEN</p>
    283- <p>0</p>
    284- <p>ELSE</p>
    285- <p>CASE</p>
    286- <p>WHEN DailyIncomes + ABS(DailyRefund) = 0 THEN</p>
    287- <p>0</p>
    288- <p>ELSE</p>
    289- <p>DailyRefund / (DailyIncomes + ABS(DailyRefund))</p>
    290- <p>END</p>
    291- <p>END,</p>
    292- <p>OtherSellerSKUDailyProfitsRate = CASE</p>
    293- <p>WHEN DailyIncomes = 0 THEN</p>
    294- <p>0</p>
    295- <p>ELSE</p>
    296- <p>CASE</p>
    297- <p>WHEN OtherDailyProfits &lt; 0 THEN</p>
    298- <p>OtherDailyProfits / ABS(DailyIncomes)</p>
    299- <p>ELSE</p>
    300- <p>OtherDailyProfits / DailyIncomes</p>
    301- <p>END</p>
    302- <p>END</p>
    303- <p>WHERE ShipDate &gt; CurrMaxShipDate;</p>
    304- <p><br /></p>
    305- <p><br /></p>
    306- <p>--更新SalesProductProfit表(渠道SKU七天利润计算)</p>
    307- <p>UPDATE rspp</p>
    308- <p>SET SevenDayProfits = o.SevenDayProfits,</p>
    309- <p>SevenDaySaleNumber = o.SevenDaySaleNumber,</p>
    310- <p>SevenDayIncomes = o.SevenDayIncomes,</p>
    311- <p>SellerSKUSevenDayProfitsRate = CASE</p>
    312- <p>WHEN o.SevenDayIncomes = 0 THEN</p>
    313- <p>0</p>
    314- <p>ELSE</p>
    315- <p>(CASE</p>
    316- <p>WHEN o.SevenDayProfits &lt; 0 THEN</p>
    317- <p>o.SevenDayProfits / ABS(o.SevenDayIncomes)</p>
    318- <p>ELSE</p>
    319- <p>o.SevenDayProfits / o.SevenDayIncomes</p>
    320- <p>END</p>
    321- <p>)</p>
    322- <p>END,</p>
    323- <p>SevenDayRefund = o.SevenDayRefund, --七天退款总额</p>
    324- <p>SevenDayAvgRefund = CASE --七天平均退款率</p>
    325- <p>WHEN o.SevenDayIncomes = 0 THEN</p>
    326- <p>0</p>
    327- <p>ELSE</p>
    328- <p>CASE</p>
    329- <p>WHEN o.SevenDayIncomes + ABS(o.SevenDayRefund) = 0 THEN</p>
    330- <p>0</p>
    331- <p>ELSE</p>
    332- <p>o.SevenDayRefund / (o.SevenDayIncomes + ABS(o.SevenDayRefund))</p>
    333- <p>END</p>
    334- <p>END,</p>
    335- <p>OtherSellerSKUSevenDayProfitsRate = CASE</p>
    336- <p>WHEN o.SevenDayIncomes = 0 THEN</p>
    337- <p>0</p>
    338- <p>ELSE</p>
    339- <p>(CASE</p>
    340- <p>WHEN o.OtherSevenDayProfits &lt; 0 THEN</p>
    341- <p>o.OtherSevenDayProfits / ABS(o.SevenDayIncomes)</p>
    342- <p>ELSE</p>
    343- <p>o.OtherSevenDayProfits / o.SevenDayIncomes</p>
    344- <p>END</p>
    345- <p>)</p>
    346- <p>END</p>
    347- <p>FROM SalesProductProfit AS rspp</p>
    348- <p>INNER JOIN</p>
    349- <p>(</p>
    350- <p>SELECT a.ID,</p>
    351- <p>SUM(rb.DailyProfits) AS SevenDayProfits,</p>
    352- <p>SUM(rb.OtherDailyProfits) AS OtherSevenDayProfits,</p>
    353- <p>SUM(rb.DailyProductNum) AS SevenDaySaleNumber,</p>
    354- <p>SUM(rb.DailyIncomes) AS SevenDayIncomes,</p>
    355- <p>SUM(rb.DailyRefund) AS SevenDayRefund</p>
    356- <p>FROM SalesProductProfit AS a</p>
    357- <p>INNER JOIN SalesProductProfit rb (NOLOCK)</p>
    358- <p>ON rb.CustomerID = a.CustomerID</p>
    359- <p>AND rb.SKU = a.SKU</p>
    360- <p>AND rb.SellerSKU = a.SellerSKU</p>
    361- <p>AND rb.OrderSourceID = a.OrderSourceID</p>
    362- <p>AND DATEDIFF(DAY, rb.ShipDate, a.ShipDate)</p>
    363- <p>BETWEEN 0 AND 6</p>
    364- <p>WHERE a.ShipDate &gt; CurrMaxShipDate</p>
    365- <p>GROUP BY a.ID</p>
    366- <p>) AS o</p>
    367- <p>ON o.ID = rspp.ID;</p>
    368- <p><br /></p>
    369- <p><br /></p>
    370- <p><br /></p>
    371- <p>--更改SalesProductProfit表的SellerSKUCurrWeekprofits（本周利润）</p>
    372- <p>UPDATE SalesProductProfit</p>
    373- <p>SET SellerSKUCurrWeekprofits =</p>
    374- <p>(</p>
    375- <p>SELECT SUM(DailyProfits)</p>
    376- <p>FROM SalesProductProfit rb (NOLOCK)</p>
    377- <p>WHERE rb.CustomerID = SalesProductProfit.CustomerID</p>
    378- <p>AND rb.SKU = SalesProductProfit.SKU</p>
    379- <p>AND rb.SellerSKU = SalesProductProfit.SellerSKU</p>
    380- <p>AND rb.OrderSourceID = SalesProductProfit.OrderSourceID</p>
    381- <p>AND rb.ShipDate &gt;= DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -1, SalesProductProfit.ShipDate)), 0)</p>
    382- <p>AND rb.ShipDate &lt; DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -1, SalesProductProfit.ShipDate)), 7)</p>
    383- <p>),</p>
    384- <p>OtherSellerSKUCurrWeekprofits =</p>
    385- <p>(</p>
    386- <p>SELECT SUM(OtherDailyProfits)</p>
    387- <p>FROM SalesProductProfit rb (NOLOCK)</p>
    388- <p>WHERE rb.CustomerID = SalesProductProfit.CustomerID</p>
    389- <p>AND rb.SKU = SalesProductProfit.SKU</p>
    390- <p>AND rb.SellerSKU = SalesProductProfit.SellerSKU</p>
    391- <p>AND rb.OrderSourceID = SalesProductProfit.OrderSourceID</p>
    392- <p>AND rb.ShipDate &gt;= DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -1, SalesProductProfit.ShipDate)), 0)</p>
    393- <p>AND rb.ShipDate &lt; DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -1, SalesProductProfit.ShipDate)), 7)</p>
    394- <p>)</p>
    395- <p>WHERE ShipDate &gt; CurrMaxShipDate;</p>
    396- <p><br /></p>
    397- <p>--更改SalesProductProfit表的SellerSKULastWeekprofits（上周利润）</p>
    398- <p>UPDATE SalesProductProfit</p>
    399- <p>SET SellerSKULastWeekprofits =</p>
    400- <p>(</p>
    401- <p>SELECT ISNULL(SUM(DailyProfits), 0)</p>
    402- <p>FROM SalesProductProfit rb (NOLOCK)</p>
    403- <p>WHERE rb.CustomerID = SalesProductProfit.CustomerID</p>
    404- <p>AND rb.SKU = SalesProductProfit.SKU</p>
    405- <p>AND rb.SellerSKU = SalesProductProfit.SellerSKU</p>
    406- <p>AND rb.OrderSourceID = SalesProductProfit.OrderSourceID</p>
    407- <p>AND rb.ShipDate &gt;= DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -7, SalesProductProfit.ShipDate)), 0)</p>
    408- <p>AND rb.ShipDate &lt; DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -1, SalesProductProfit.ShipDate)), 0)</p>
    409- <p>),</p>
    410- <p>OtherSellerSKULastWeekprofits =</p>
    411- <p>(</p>
    412- <p>SELECT ISNULL(SUM(OtherDailyProfits), 0)</p>
    413- <p>FROM SalesProductProfit rb (NOLOCK)</p>
    414- <p>WHERE rb.CustomerID = SalesProductProfit.CustomerID</p>
    415- <p>AND rb.SKU = SalesProductProfit.SKU</p>
    416- <p>AND rb.SellerSKU = SalesProductProfit.SellerSKU</p>
    417- <p>AND rb.OrderSourceID = SalesProductProfit.OrderSourceID</p>
    418- <p>AND rb.ShipDate &gt;= DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -7, SalesProductProfit.ShipDate)), 0)</p>
    419- <p>AND rb.ShipDate &lt; DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, -1, SalesProductProfit.ShipDate)), 0)</p>
    420- <p>)</p>
    421- <p>WHERE ShipDate &gt; CurrMaxShipDate;</p>
    422- <p><br /></p>
    423- <p>--更新SalesProductProfit表的MonthProfits(渠道SKU月总利润)</p>
    424- <p>UPDATE rspp</p>
    425- <p>SET MonthProfits = o.MonthProfits,</p>
    426- <p>MonthSaleNumber = o.MonthSaleNumber,</p>
    427- <p>MonthIncomes = o.MonthIncomes,</p>
    428- <p>MonthAvgProfits = CASE</p>
    429- <p>WHEN o.MonthIncomes = 0 THEN</p>
    430- <p>0</p>
    431- <p>ELSE</p>
    432- <p>(CASE</p>
    433- <p>WHEN o.MonthProfits &lt; 0 THEN</p>
    434- <p>o.MonthProfits / ABS(o.MonthIncomes)</p>
    435- <p>ELSE</p>
    436- <p>o.MonthProfits / o.MonthIncomes</p>
    437- <p>END</p>
    438- <p>)</p>
    439- <p>END,</p>
    440- <p>MonthRefund = o.MonthRefund, --月总退款</p>
    441- <p>MonthAvgRefund = CASE --月平均退款率</p>
    442- <p>WHEN o.MonthIncomes = 0 THEN</p>
    443- <p>0</p>
    444- <p>ELSE</p>
    445- <p>CASE</p>
    446- <p>WHEN o.MonthIncomes + ABS(o.MonthRefund) = 0 THEN</p>
    447- <p>0</p>
    448- <p>ELSE</p>
    449- <p>o.MonthRefund / (o.MonthIncomes + ABS(o.MonthRefund))</p>
    450- <p>END</p>
    451- <p>END,</p>
    452- <p>OtherMonthProfits = o.OtherMonthProfits,</p>
    453- <p>OtherMonthAvgProfits = CASE</p>
    454- <p>WHEN o.MonthIncomes = 0 THEN</p>
    455- <p>0</p>
    456- <p>ELSE</p>
    457- <p>(CASE</p>
    458- <p>WHEN o.OtherMonthProfits &lt; 0 THEN</p>
    459- <p>o.OtherMonthProfits / ABS(o.MonthIncomes)</p>
    460- <p>ELSE</p>
    461- <p>o.OtherMonthProfits / o.MonthIncomes</p>
    462- <p>END</p>
    463- <p>)</p>
    464- <p>END</p>
    465- <p>FROM SalesProductProfit AS rspp</p>
    466- <p>INNER JOIN</p>
    467- <p>(</p>
    468- <p>SELECT a.ID,</p>
    469- <p>SUM(rb.DailyProfits) AS MonthProfits,</p>
    470- <p>SUM(rb.DailyProductNum) AS MonthSaleNumber,</p>
    471- <p>SUM(rb.DailyIncomes) AS MonthIncomes,</p>
    472- <p>SUM(rb.DailyRefund) AS MonthRefund,</p>
    473- <p>SUM(rb.OtherDailyProfits) AS OtherMonthProfits</p>
    474- <p>FROM SalesProductProfit AS a</p>
    475- <p>INNER JOIN SalesProductProfit rb (NOLOCK)</p>
    476- <p>ON rb.CustomerID = a.CustomerID</p>
    477- <p>AND rb.SKU = a.SKU</p>
    478- <p>AND rb.SellerSKU = a.SellerSKU</p>
    479- <p>AND rb.OrderSourceID = a.OrderSourceID</p>
    480- <p>AND DATEDIFF(DAY, rb.ShipDate, a.ShipDate)</p>
    481- <p>BETWEEN 0 AND 29</p>
    482- <p>WHERE a.ShipDate &gt; CurrMaxShipDate</p>
    483- <p>GROUP BY a.ID</p>
    484- <p>) AS o</p>
    485- <p>ON o.ID = rspp.ID;</p>
    486- <p><br /></p>
    487- <p>--更新三个月利润率和退款率</p>
    488- <p>UPDATE rspp</p>
    489- <p>SET ThreeMonthProfitRate = CASE</p>
    490- <p>WHEN o.TreeMonthIncomes = 0 THEN</p>
    491- <p>0</p>
    492- <p>ELSE</p>
    493- <p>(CASE</p>
    494- <p>WHEN o.TreeMonthProfits &lt; 0 THEN</p>
    495- <p>o.TreeMonthProfits / ABS(o.TreeMonthIncomes)</p>
    496- <p>ELSE</p>
    497- <p>o.TreeMonthProfits / o.TreeMonthIncomes</p>
    498- <p>END</p>
    499- <p>)</p>
    500- <p>END,</p>
    501- <p>ThreeMonthRefundRate = CASE</p>
    502- <p>WHEN o.TreeMonthIncomes = 0 THEN</p>
    503- <p>0</p>
    504- <p>ELSE</p>
    505- <p>CASE</p>
    506- <p>WHEN o.TreeMonthIncomes + ABS(o.TreeMonthRefund) = 0 THEN</p>
    507- <p>0</p>
    508- <p>ELSE</p>
    509- <p>o.TreeMonthRefund / (o.TreeMonthIncomes + ABS(o.TreeMonthRefund))</p>
    510- <p>END</p>
    511- <p>END,</p>
    512- <p>OtherThreeMonthProfitRate = CASE</p>
    513- <p>WHEN o.TreeMonthIncomes = 0 THEN</p>
    514- <p>0</p>
    515- <p>ELSE</p>
    516- <p>(CASE</p>
    517- <p>WHEN o.OtherTreeMonthProfits &lt; 0 THEN</p>
    518- <p>o.OtherTreeMonthProfits / ABS(o.TreeMonthIncomes)</p>
    519- <p>ELSE</p>
    520- <p>o.OtherTreeMonthProfits / o.TreeMonthIncomes</p>
    521- <p>END</p>
    522- <p>)</p>
    523- <p>END</p>
    524- <p>FROM SalesProductProfit AS rspp</p>
    525- <p>INNER JOIN</p>
    526- <p>(</p>
    527- <p>SELECT a.ID,</p>
    528- <p>SUM(rb.DailyProfits) AS TreeMonthProfits,</p>
    529- <p>SUM(rb.DailyProductNum) AS TreeMonthSaleNumber,</p>
    530- <p>SUM(rb.DailyIncomes) AS TreeMonthIncomes,</p>
    531- <p>SUM(rb.DailyRefund) AS TreeMonthRefund,</p>
    532- <p>SUM(rb.OtherDailyProfits) AS OtherTreeMonthProfits</p>
    533- <p>FROM SalesProductProfit AS a</p>
    534- <p>INNER JOIN SalesProductProfit rb (NOLOCK)</p>
    535- <p>ON rb.CustomerID = a.CustomerID</p>
    536- <p>AND rb.SKU = a.SKU</p>
    537- <p>AND rb.SellerSKU = a.SellerSKU</p>
    538- <p>AND rb.OrderSourceID = a.OrderSourceID</p>
    539- <p>AND DATEDIFF(DAY, rb.ShipDate, a.ShipDate)</p>
    540- <p>BETWEEN 0 AND 90</p>
    541- <p>WHERE a.ShipDate &gt; CurrMaxShipDate</p>
    542- <p>GROUP BY a.ID</p>
    543- <p>) AS o</p>
    544- <p>ON o.ID = rspp.ID;</p>
    545- <p>--</p>
    546- <p><br /></p>
    547- <p>MERGE RB_Report_SalesProductProfit AS target</p>
    548- <p>USING</p>
    549- <p>(</p>
    550- <p>SELECT CustomerID,</p>
    551- <p>SKU,</p>
    552- <p>SellerSKU,</p>
    553- <p>OrderSourceID,</p>
    554- <p>OrderSourceName,</p>
    555- <p>ProductNameCN,</p>
    556- <p>ShipDate,</p>
    557- <p>DailyProductNum,</p>
    558- <p>DailyProfits,</p>
    559- <p>DailyIncomes,</p>
    560- <p>SellerSKUDailyProfitsRate,</p>
    561- <p>SellerSKUSevenDayProfitsRate,</p>
    562- <p>SellerSKUCurrWeekprofits,</p>
    563- <p>SellerSKULastWeekprofits,</p>
    564- <p>SevenDayProfits,</p>
    565- <p>SevenDaySaleNumber,</p>
    566- <p>MonthProfits,</p>
    567- <p>MonthSaleNumber,</p>
    568- <p>MonthAvgProfits,</p>
    569- <p>BusinessAdminID,</p>
    570- <p>SevenDayIncomes,</p>
    571- <p>MonthIncomes,</p>
    572- <p>UpdateTime,</p>
    573- <p>OrderSourceType,</p>
    574- <p>DailyAvgRefund,</p>
    575- <p>SevenDayAvgRefund,</p>
    576- <p>MonthAvgRefund,</p>
    577- <p>DailyRefund,</p>
    578- <p>SevenDayRefund,</p>
    579- <p>MonthRefund,</p>
    580- <p>ThreeMonthProfitRate,</p>
    581- <p>ThreeMonthRefundRate,</p>
    582- <p>OtherDailyProfits,</p>
    583- <p>OtherSellerSKUDailyProfitsRate,</p>
    584- <p>OtherSellerSKUSevenDayProfitsRate,</p>
    585- <p>OtherSellerSKUCurrWeekprofits,</p>
    586- <p>OtherSellerSKULastWeekprofits,</p>
    587- <p>OtherSevenDayProfits,</p>
    588- <p>OtherMonthProfits,</p>
    589- <p>OtherMonthAvgProfits,</p>
    590- <p>OtherThreeMonthProfitRate</p>
    591- <p>FROM SalesProductProfit</p>
    592- <p>WHERE ShipDate &gt; CurrMaxShipDate</p>
    593- <p>) AS source</p>
    594- <p>(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,</p>
    595- <p>OtherDailyProfits,</p>
    596- <p>OtherSellerSKUDailyProfitsRate,</p>
    597- <p>OtherSellerSKUSevenDayProfitsRate,</p>
    598- <p>OtherSellerSKUCurrWeekprofits,</p>
    599- <p>OtherSellerSKULastWeekprofits,</p>
    600- <p>OtherSevenDayProfits,</p>
    601- <p>OtherMonthProfits,</p>
    602- <p>OtherMonthAvgProfits,</p>
    603- <p>OtherThreeMonthProfitRate</p>
    604- <p>)</p>
    605- <p>ON (</p>
    606- <p>target.CustomerID = source.CustomerID</p>
    607- <p>AND target.SKU = source.SKU</p>
    608- <p>AND target.SellerSKU = source.SellerSKU</p>
    609- <p>AND target.OrderSourceID = source.OrderSourceID</p>
    610- <p>AND target.OrderSourceName = source.OrderSourceName</p>
    611- <p>AND target.ProductNameCN = source.ProductNameCN</p>
    612- <p>AND target.ShipDate = source.ShipDate</p>
    613- <p>AND target.BusinessAdminID = source.BusinessAdminID</p>
    614- <p>AND target.OrderSourceType = source.OrderSourceType</p>
    615- <p>)</p>
    616- <p>WHEN MATCHED AND target.ShipDate &gt; CurrMaxShipDate</p>
    617- <p>AND (</p>
    618- <p>target.DailyProductNum != source.DailyProductNum</p>
    619- <p>OR target.DailyProfits != source.DailyProfits</p>
    620- <p>OR target.SellerSKUDailyProfitsRate != source.SellerSKUDailyProfitsRate</p>
    621- <p>OR target.SellerSKUSevenDayProfitsRate != source.SellerSKUSevenDayProfitsRate</p>
    622- <p>OR target.SellerSKUCurrWeekprofits != source.SellerSKUCurrWeekprofits</p>
    623- <p>OR target.SellerSKULastWeekprofits != source.SellerSKULastWeekprofits</p>
    624- <p>OR target.SevenDayProfits != source.SevenDayProfits</p>
    625- <p>OR target.MonthProfits != source.MonthProfits</p>
    626- <p>OR target.MonthAvgProfits != source.MonthAvgProfits</p>
    627- <p>OR target.ThreeMonthProfitRate != source.ThreeMonthProfitRate</p>
    628- <p>OR target.ThreeMonthRefundRate != source.ThreeMonthRefundRate</p>
    629- <p>OR target.OtherDailyProfits != source.OtherDailyProfits</p>
    630- <p>OR target.OtherSellerSKUDailyProfitsRate != source.OtherSellerSKUDailyProfitsRate</p>
    631- <p>OR target.OtherSellerSKUSevenDayProfitsRate != source.OtherSellerSKUSevenDayProfitsRate</p>
    632- <p>OR target.OtherSellerSKUCurrWeekprofits != source.OtherSellerSKUCurrWeekprofits</p>
    633- <p>OR target.OtherSellerSKULastWeekprofits != source.OtherSellerSKULastWeekprofits</p>
    634- <p>OR target.OtherSevenDayProfits != source.OtherSevenDayProfits</p>
    635- <p>OR target.OtherMonthProfits != source.OtherMonthProfits</p>
    636- <p>OR target.OtherMonthAvgProfits != source.OtherMonthAvgProfits</p>
    637- <p>OR target.OtherThreeMonthProfitRate != source.OtherThreeMonthProfitRate</p>
    638- <p><br /></p>
    639- <p>) THEN</p>
    640- <p>UPDATE SET target.DailyProductNum = source.DailyProductNum,</p>
    641- <p>target.DailyProfits = source.DailyProfits,</p>
    642- <p>target.DailyIncomes = source.DailyIncomes,</p>
    643- <p>target.SellerSKUDailyProfitsRate = source.SellerSKUDailyProfitsRate,</p>
    644- <p>target.SellerSKUSevenDayProfitsRate = source.SellerSKUSevenDayProfitsRate,</p>
    645- <p>target.SellerSKUCurrWeekprofits = source.SellerSKUCurrWeekprofits,</p>
    646- <p>target.SellerSKULastWeekprofits = source.SellerSKULastWeekprofits,</p>
    647- <p>target.SevenDayProfits = source.SevenDayProfits,</p>
    648- <p>target.SevenDaySaleNumber = source.SevenDaySaleNumber,</p>
    649- <p>target.MonthProfits = source.MonthProfits,</p>
    650- <p>target.MonthSaleNumber = source.MonthSaleNumber,</p>
    651- <p>target.MonthAvgProfits = source.MonthAvgProfits,</p>
    652- <p>target.SevenDayIncomes = source.SevenDayIncomes,</p>
    653- <p>target.MonthIncomes = source.MonthIncomes,</p>
    654- <p>target.DailyAvgRefund = source.DailyAvgRefund,</p>
    655- <p>target.SevenDayAvgRefund = source.SevenDayAvgRefund,</p>
    656- <p>target.MonthAvgRefund = source.MonthAvgRefund,</p>
    657- <p>target.DailyRefund = source.DailyRefund,</p>
    658- <p>target.SevenDayRefund = source.SevenDayRefund,</p>
    659- <p>target.MonthRefund = source.MonthRefund,</p>
    660- <p>target.ThreeMonthProfitRate= source.ThreeMonthProfitRate,</p>
    661- <p>target.ThreeMonthRefundRate= source.ThreeMonthRefundRate,</p>
    662- <p>target.OtherDailyProfits= source.OtherDailyProfits,</p>
    663- <p>target.OtherSellerSKUDailyProfitsRate= source.OtherSellerSKUDailyProfitsRate,</p>
    664- <p>target.OtherSellerSKUSevenDayProfitsRate= source.OtherSellerSKUSevenDayProfitsRate,</p>
    665- <p>target.OtherSellerSKUCurrWeekprofits= source.OtherSellerSKUCurrWeekprofits,</p>
    666- <p>target.OtherSellerSKULastWeekprofits= source.OtherSellerSKULastWeekprofits,</p>
    667- <p>target.OtherSevenDayProfits= source.OtherSevenDayProfits,</p>
    668- <p>target.OtherMonthProfits= source.OtherMonthProfits,</p>
    669- <p>target.OtherMonthAvgProfits= source.OtherMonthAvgProfits,</p>
    670- <p>target.OtherThreeMonthProfitRate= source.OtherThreeMonthProfitRate,</p>
    671- <p>target.UpdateTime = GETDATE()</p>
    672- <p>WHEN NOT MATCHED BY TARGET THEN</p>
    673- <p>INSERT</p>
    674- <p>(</p>
    675- <p>CustomerID,</p>
    676- <p>SKU,</p>
    677- <p>SellerSKU,</p>
    678- <p>OrderSourceID,</p>
    679- <p>OrderSourceName,</p>
    680- <p>ProductNameCN,</p>
    681- <p>ShipDate,</p>
    682- <p>DailyProductNum,</p>
    683- <p>DailyProfits,</p>
    684- <p>DailyIncomes,</p>
    685- <p>SellerSKUDailyProfitsRate,</p>
    686- <p>SellerSKUSevenDayProfitsRate,</p>
    687- <p>SellerSKUCurrWeekprofits,</p>
    688- <p>SellerSKULastWeekprofits,</p>
    689- <p>SevenDayProfits,</p>
    690- <p>SevenDaySaleNumber,</p>
    691- <p>MonthProfits,</p>
    692- <p>MonthSaleNumber,</p>
    693- <p>MonthAvgProfits,</p>
    694- <p>BusinessAdminID,</p>
    695- <p>SevenDayIncomes,</p>
    696- <p>MonthIncomes,</p>
    697- <p>OrderSourceType,</p>
    698- <p>DailyAvgRefund,</p>
    699- <p>SevenDayAvgRefund,</p>
    700- <p>MonthAvgRefund,</p>
    701- <p>DailyRefund,</p>
    702- <p>SevenDayRefund,</p>
    703- <p>MonthRefund,</p>
    704- <p>ThreeMonthProfitRate,</p>
    705- <p>ThreeMonthRefundRate,</p>
    706- <p>OtherDailyProfits,</p>
    707- <p>OtherSellerSKUDailyProfitsRate,</p>
    708- <p>OtherSellerSKUSevenDayProfitsRate,</p>
    709- <p>OtherSellerSKUCurrWeekprofits,</p>
    710- <p>OtherSellerSKULastWeekprofits,</p>
    711- <p>OtherSevenDayProfits,</p>
    712- <p>OtherMonthProfits,</p>
    713- <p>OtherMonthAvgProfits,</p>
    714- <p>OtherThreeMonthProfitRate,</p>
    715- <p>UpdateTime</p>
    716- <p>)</p>
    717- <p>VALUES</p>
    718- <p>(CustomerID, SKU, SellerSKU, OrderSourceID, OrderSourceName, ProductNameCN, ShipDate, DailyProductNum,</p>
    719- <p>DailyProfits, DailyIncomes, SellerSKUDailyProfitsRate, SellerSKUSevenDayProfitsRate, SellerSKUCurrWeekprofits,</p>
    720- <p>SellerSKULastWeekprofits, SevenDayProfits, SevenDaySaleNumber, MonthProfits, MonthSaleNumber, MonthAvgProfits,</p>
    721- <p>BusinessAdminID, SevenDayIncomes, MonthIncomes, OrderSourceType, DailyAvgRefund, SevenDayAvgRefund,</p>
    722- <p>MonthAvgRefund, DailyRefund, SevenDayRefund, MonthRefund,ThreeMonthProfitRate,ThreeMonthRefundRate,</p>
    723- <p>OtherDailyProfits,</p>
    724- <p>OtherSellerSKUDailyProfitsRate,</p>
    725- <p>OtherSellerSKUSevenDayProfitsRate,</p>
    726- <p>OtherSellerSKUCurrWeekprofits,</p>
    727- <p>OtherSellerSKULastWeekprofits,</p>
    728- <p>OtherSevenDayProfits,</p>
    729- <p>OtherMonthProfits,</p>
    730- <p>OtherMonthAvgProfits,</p>
    731- <p>OtherThreeMonthProfitRate, GETDATE())</p>
    732- <p>WHEN NOT MATCHED BY SOURCE AND target.ShipDate &gt; CurrMaxShipDate THEN</p>
    733- <p>DELETE;</p>
    734- <p>--OUTPUT action,</p>
    735- <p>-- inserted.*,</p>
    736- <p>-- deleted.*;</p>
    737- <p><br /></p>
    738- <p><br /></p>
    739- <p>DROP TABLE SalesProductProfit</p>
    740- <p><br /></p>
    741- <p>--初始化SKU产品利润统计数据</p>
    742- <p>INSERT INTO RB_Report_SKUProductProfitStastic</p>
    743- <p>(</p>
    744- <p>CustomerID,</p>
    745- <p>SKU,</p>
    746- <p>ProductNameCN,</p>
    747- <p>DailyProfits,</p>
    748- <p>DailyIncomes,</p>
    749- <p>DailyAvgSalePrice,</p>
    750- <p>DailySaleNumber,</p>
    751- <p>ShipDate,</p>
    752- <p>DailyAvgProfits,</p>
    753- <p>SevenDayAvgProfits,</p>
    754- <p>SKUCurrWeekprofits,</p>
    755- <p>SKULastWeekprofits,</p>
    756- <p>DailyRefund, --日总退款</p>
    757- <p>SevenDayRefund, --七天总退款</p>
    758- <p>MonthRefund, --月总退款</p>
    759- <p>DailyAvgRefund, --日平均退款率</p>
    760- <p>SevenDayAvgRefund, --七天平均退款率</p>
    761- <p>MonthAvgRefund --月平均退款率</p>
    762- <p>)</p>
    763- <p>SELECT CustomerID,</p>
    764- <p>SKU,</p>
    765- <p>ProductNameCN,</p>
    766- <p>SUM(DailyProfits),</p>
    767- <p>SUM(DailyIncomes),</p>
    768- <p>CASE</p>
    769- <p>WHEN SUM(DailyProductNum) = 0 THEN</p>
    770- <p>0</p>
    771- <p>ELSE</p>
    772- <p>CASE</p>
    773- <p>WHEN SUM(DailyIncomes) &lt; 0 THEN</p>
    774- <p>SUM(DailyIncomes) / ABS(SUM(DailyProductNum))</p>
    775- <p>ELSE</p>
    776- <p>SUM(DailyIncomes) / SUM(DailyProductNum)</p>
    777- <p>END</p>
    778- <p>END,</p>
    779- <p>SUM(DailyProductNum),</p>
    780- <p>ShipDate,</p>
    781- <p>CASE</p>
    782- <p>WHEN SUM(DailyIncomes) = 0 THEN</p>
    783- <p>0</p>
    784- <p>ELSE</p>
    785- <p>CASE</p>
    786- <p>WHEN SUM(DailyIncomes) &lt; 0 THEN</p>
    787- <p>SUM(DailyProfits) / ABS(SUM(DailyIncomes))</p>
    788- <p>ELSE</p>
    789- <p>SUM(DailyProfits) / SUM(DailyIncomes)</p>
    790- <p>END</p>
    791- <p>END,</p>
    792- <p>0,</p>
    793- <p>0,</p>
    794- <p>0,</p>
    795- <p>SUM(DailyRefund), --日总退款</p>
    796- <p>0,</p>
    797- <p>0,</p>
    798- <p>CASE</p>
    799- <p>WHEN SUM(DailyIncomes) = 0 THEN</p>
    800- <p>0</p>
    801- <p>ELSE</p>
    802- <p>CASE</p>
    803- <p>WHEN SUM(DailyIncomes + ABS(DailyRefund)) = 0 THEN</p>
    804- <p>0</p>
    805- <p>ELSE</p>
    806- <p>SUM(DailyRefund) / SUM(DailyIncomes + ABS(DailyRefund))</p>
    807- <p>END</p>
    808- <p>END, --日平均退款率</p>
    809- <p>0,</p>
    810- <p>0</p>
    811- <p>FROM RB_Report_SalesProductProfit (NOLOCK)</p>
    812- <p>WHERE ShipDate &gt; CurrMaxShipDate</p>
    813- <p>GROUP BY SKU,</p>
    814- <p>CustomerID,</p>
    815- <p>ShipDate,</p>
    816- <p>ProductNameCN</p>
    817- <p>ORDER BY ShipDate;</p>
    818- <p><br /></p>
    819- <p><br /></p>
    820- <p>--更新RB_Report_SKUProductProfitStastic表(渠道SKU七天总利润)</p>
    821- <p>UPDATE rspp</p>
    822- <p>SET SevenDayProfits = o.SevenDayProfits,</p>
    823- <p>SevenDaySaleNumber = o.SevenDaySaleNumber,</p>
    824- <p>SevenDayIncomes = o.SevenDayIncomes,</p>
    825- <p>SevenDayAvgProfits = CASE</p>
    826- <p>WHEN o.SevenDayIncomes = 0 THEN</p>
    827- <p>0</p>
    828- <p>ELSE</p>
    829- <p>(CASE</p>
    830- <p>WHEN o.SevenDayProfits &lt; 0 THEN</p>
    831- <p>o.SevenDayProfits / ABS(o.SevenDayIncomes)</p>
    832- <p>ELSE</p>
    833- <p>o.SevenDayProfits / o.SevenDayIncomes</p>
    834- <p>END</p>
    835- <p>)</p>
    836- <p>END,</p>
    837- <p>SevenDayRefund = o.SevenDayRefund, --七天总退款</p>
    838- <p>SevenDayAvgRefund = CASE --七天平均退款率</p>
    839- <p>WHEN o.SevenDayIncomes = 0 THEN</p>
    840- <p>0</p>
    841- <p>ELSE</p>
    842- <p>CASE</p>
    843- <p>WHEN o.SevenDayIncomes + ABS(o.SevenDayRefund) = 0 THEN</p>
    844- <p>0</p>
    845- <p>ELSE</p>
    846- <p>o.SevenDayRefund / (o.SevenDayIncomes + ABS(o.SevenDayRefund))</p>
    847- <p>END</p>
    848- <p>END</p>
    849- <p>FROM RB_Report_SKUProductProfitStastic AS rspp</p>
    850- <p>INNER JOIN</p>
    851- <p>(</p>
    852- <p>SELECT a.ID,</p>
    853- <p>SUM(rb.DailyProfits) AS SevenDayProfits,</p>
    854- <p>SUM(rb.DailySaleNumber) AS SevenDaySaleNumber,</p>
    855- <p>SUM(ISNULL(rb.DailyIncomes, 0)) AS SevenDayIncomes,</p>
    856- <p>SUM(rb.DailyRefund) AS SevenDayRefund</p>
    857- <p>FROM RB_Report_SKUProductProfitStastic AS a</p>
    858- <p>INNER JOIN RB_Report_SKUProductProfitStastic rb (NOLOCK)</p>
    859- <p>ON rb.CustomerID = a.Custo</p>
  5. 2025-12-17 10:32:01, 由 林很 编辑。
    修改了 每月价值量，旧值为 "5000"，新值为 "8000"。
    修改了 ROI ，旧值为 "3.75"，新值为 "6.00"。
  6. 2025-12-18 10:36:40, 由 张明磊 编辑。
    修改了 预计工时，旧值为 "16"，新值为 "8"。
    修改了 成本 ，旧值为 "8000"，新值为 "4000"。
    修改了 ROI ，旧值为 "6.00"，新值为 "12.00"。
    工时：8H
    1.分析存储过程输出优化方案 4H
    2.新增时间入参分批按时间段更新数据？？ 4H
  7. 2025-12-18 11:16:14, 由 张明磊 记录评审结果，结果为 确认通过。
  8. 2025-12-18 11:18:56, 由 周杰 关联到迭代 主干_12月V5版本_20251230 。
  9. 2025-12-18 19:34:03, 由 周杰 编辑。
    修改了 计划验收时间，旧值为 "2025-12-30"，新值为 "2026-1-9"。
  10. 2025-12-23 11:41:00, 由 曾倩 编辑。
    修改了 测试人员，旧值为 ""，新值为 "zengqian"。
  11. 2025-12-30 19:17:22, 由 曾倩 编辑。
    修改了 所处阶段 ，旧值为 "tested"，新值为 "released"。
    修改了 设置阶段者，旧值为 ""，新值为 "zengqian"。
  12. 2025-12-31 09:24:49, 由 李乐同 编辑。
    修改了 所处阶段 ，旧值为 "released"，新值为 "bepending"。
    修改了 设置阶段者，旧值为 "zengqian"，新值为 "liletong"。
  13. 2026-01-12 10:59:19, 由 周杰 关闭，原因为 已完成 。
    修改了 由谁关闭，旧值为 ""，新值为 "zhoujie"。
    修改了 关闭日期，旧值为 "0000-00-00 00:00:00"，新值为 "2026-01-12 10:59:18"。
    修改了 指派给 ，旧值为 "zhangminglei"，新值为 "closed"。
    修改了 当前状态，旧值为 "active"，新值为 "closed"。
    修改了 所处阶段，旧值为 "bepending"，新值为 "closed"。
    修改了 关闭原因，旧值为 ""，新值为 "done"。
返回
迭代版本
提报业务部门 技术研发中心
提报人员 林很
需求来源 产品自主提报需求
所属项目 技术中心-需求池
所属模块 财务   财务管理
所属计划
业务价值
开发组 财务开发组
受益业务部门 技术研发中心
验收人员 许瑞丰
业务目标类型
中心业务目标
业务价值 控制风险
每月价值量 8000
每月节省人数 0
优先级 P2
价值类型 长期价值
预计工时 8
成本 4000
ROI 12.00
操作手册 否
高价值 -
主被动 被动接收
复杂度 一般性
工作流ID
工作流创建时间 0000-00-00 00:00:00
更新点
是否高危 否
评分
当前状态 已关闭
所处阶段 已关闭
关键词
评审次数 一次
计划上线 2025-12-30
上线时间 2025-12-30
计划验收时间 2026-01-09
业务验收时间
满意度
价值验收
满意度分值
业务回填价值
验收达成价值
验收达成ROI
抄送给 周杰  
由谁创建 林很 于 2025-12-17 10:09:43
指派给 Closed 于 2026-01-12 10:59:18
由谁评审 张明磊
评审时间 2025-12-18
由谁关闭 周杰 于 2026-01-12 10:59:18
关闭原因 已完成
最后修改 周杰 于 2026-01-12 10:59:18
来源Bug
相关Bug
相关用例
相关需求
细分需求