话不多说，直接上码↓
1 IF OBJECT_ID('tempdb..#Table') IS NOT NULL
2 BEGIN
3 DROP TABLE #Table;
4 END;
5
6 DECLARE @tbRangeDate TABLE
7 (
8 name NVARCHAR(10) ,
9 value NVARCHAR(10)
10 );
11 DECLARE @d INT = 7;
12 DECLARE @i INT = 0;
13 DECLARE @TotalCount INT= 5;
14
15 DECLARE @SuccessMoney NVARCHAR(100) = '';
16 DECLARE @PutawayMoney NVARCHAR(100) = '';
17 --记录星期六，星期天的金额数据
18 DECLARE @WeekendPutMoney DECIMAL(18, 2)= 0;
19 DECLARE @WeekendSuccessMoney DECIMAL(18, 2)= 0;
20 --DATEADD(DAY,-7,GETDATE());
21 DECLARE @currentDate DATETIME= GETDATE();
22 DECLARE @topName NVARCHAR(10) = '';
23 DECLARE @countValue INT= 0;
24 --近五天数据
25
26 -- 声明变量
27 DECLARE @name AS NVARCHAR(10) ,
28 @value AS NVARCHAR(10);
29
30 CREATE TABLE #Table
31 (
32 DisplayName VARCHAR(20) ,
33 DayValue VARCHAR(20) ,
34 SuccessMoney DECIMAL(18, 2) , --成交
35 PutawayMoney DECIMAL(18, 2) --上架
36 );
37 WHILE ( @d > 0 )
38 BEGIN
39 SET @d = @d - 1;
40
41 INSERT INTO @tbRangeDate
42 VALUES ( CONVERT(VARCHAR(10), DATENAME(WEEKDAY,
43 DATEADD(DAY, -( @d ),
44 @currentDate)), 23),
45 CONVERT(VARCHAR(10), DATEADD(DAY, -( @d ), @currentDate), 23) );
46 END;
47
48
49 WHILE EXISTS ( SELECT name ,
50 value
51 FROM @tbRangeDate )
52 BEGIN
53 -- 也可以使用top 1
54 SET ROWCOUNT 1;
55 SELECT @name = name ,
56 @value = value
57 FROM @tbRangeDate;
58 SET @SuccessMoney = '';
59 SET @PutawayMoney = '';
60 SET @WeekendPutMoney = 0;
61 SET @WeekendSuccessMoney = 0;
62
63 --统计每天上架资源金额
64 SELECT @PutawayMoney = SUM(a.resourceTotalMoney)
65 FROM ( SELECT r.Id ,
66 r.ResourceCode ,
67 ROW_NUMBER() OVER ( PARTITION BY b.ResourceId ORDER BY b.ResourceId ) AS rowNum ,
68 SUM(b.Number * b.BasePrice) resourceTotalMoney
69 FROM ResourceManage.Resource r
70 JOIN ResourceManage.ResourceItem b ON r.Id = b.ResourceId
71 WHERE ResourcesType IN ( 0, 1, 2, 5 )
72 AND r.IsDeleted = 0
73 AND r.IsPublish = 1
74 AND CONVERT(VARCHAR(10), r.PublishTime, 23) = @value
75 GROUP BY r.Id ,
76 r.ResourceCode ,
77 b.ResourceId
78 ) a;
79 --周末上架金额的数据
80 IF @name = '星期日'
81 OR @name = '星期六'
82 BEGIN
83 SET @WeekendPutMoney = @WeekendPutMoney
84 + CAST(@PutawayMoney AS DECIMAL(18, 2));
85 END;
86
87
88 --统计每天成交金额
89 SELECT @SuccessMoney = SUM(AmountOfMoney)
90 FROM Business.ResourceOrder
91 WHERE OrderSourceType IN ( 0, 1, 2, 5 )
92 AND IsDeleted = 0
93 AND CONVERT(VARCHAR(10), CreationTime, 23) = @value;
94 --周末成交金额的数据
95 IF @name = '星期日'
96 OR @name = '星期六'
97 BEGIN
98 SET @WeekendSuccessMoney = @WeekendSuccessMoney
99 + CAST(@SuccessMoney AS DECIMAL(18, 2));
100 END;
101
102
103 INSERT INTO #Table
104 ( DisplayName ,
105 DayValue ,
106 SuccessMoney ,
107 PutawayMoney
108 )
109 VALUES ( @name ,
110 @value ,
111 @SuccessMoney ,
112 @PutawayMoney
113 );
114
115 SET ROWCOUNT 0;
116 DELETE FROM @tbRangeDate
117 WHERE name = @name;
118 END;
119
120 SELECT TOP 1
121 @topName = DisplayName
122 FROM #Table;
123
124 SELECT @countValue = COUNT(1)
125 FROM #Table
126 WHERE DisplayName = '星期六'
127 OR DisplayName = '星期日';
128
129 WHILE ( @i = 0 )
130 BEGIN
131 SET @i = @i + 1;
132 DELETE FROM #Table
133 WHERE DisplayName = '星期六'
134 OR DisplayName = '星期日';
135 IF @countValue = 2
136 AND ( @topName = '星期二'
137 OR @topName = '星期三'
138 OR @topName = '星期四'
139 OR @topName = '星期五'
140 )
141 BEGIN
142 UPDATE #Table
143 SET PutawayMoney = PutawayMoney + @WeekendPutMoney
144 WHERE DisplayName = '星期一';
145 END;
146 END;
147
148
149 SELECT *
150 FROM #Table;
151
152 IF OBJECT_ID('tempdb..#Table') IS NOT NULL
153 BEGIN
154 DROP TABLE #Table;
155 END;