修改了 所属计划 ，旧值为 ""，新值为 "2392"。
修改了 计划验收时间，旧值为 "0000-00-00"，新值为 "2025-12-30"。
修改了 所处阶段 ，旧值为 "wait"，新值为 "planned"。

修改了 版本号 ，旧值为 "1"，新值为 "2"。
修改了 需求名称，区别为：

001- 【系统优化】erp本地服务慢sql优化处理
001+ 【系统优化】产品利润管理本地服务慢sql优化处理

001- 【系统优化】erp本地服务慢sql优化处理
001+ 【系统优化】产品利润管理本地服务慢sql优化处理

修改了 版本号 ，旧值为 "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>

修改了 每月价值量，旧值为 "5000"，新值为 "8000"。
修改了 ROI ，旧值为 "3.75"，新值为 "6.00"。

修改了 预计工时，旧值为 "16"，新值为 "8"。
修改了 成本 ，旧值为 "8000"，新值为 "4000"。
修改了 ROI ，旧值为 "6.00"，新值为 "12.00"。

修改了 计划验收时间，旧值为 "2025-12-30"，新值为 "2026-1-9"。

修改了 测试人员，旧值为 ""，新值为 "zengqian"。

修改了 所处阶段 ，旧值为 "tested"，新值为 "released"。
修改了 设置阶段者，旧值为 ""，新值为 "zengqian"。

修改了 所处阶段 ，旧值为 "released"，新值为 "bepending"。
修改了 设置阶段者，旧值为 "zengqian"，新值为 "liletong"。

修改了 由谁关闭，旧值为 ""，新值为 "zhoujie"。
修改了 关闭日期，旧值为 "0000-00-00 00:00:00"，新值为 "2026-01-12 10:59:18"。
修改了 指派给 ，旧值为 "zhangminglei"，新值为 "closed"。
修改了 当前状态，旧值为 "active"，新值为 "closed"。
修改了 所处阶段，旧值为 "bepending"，新值为 "closed"。
修改了 关闭原因，旧值为 ""，新值为 "done"。