SQL周、日、月、年数据统计

Posted on | Post modified: | Visitors:

本文只是记录在项目中用到的统计的SQL语句，记一笔以防忘了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
/// <summary>
/// 获取统计数据
/// </summary>
/// <param name="CKEY">店面ckey</param>
/// <param name="type">统计类型（日、周、月、年）</param>
/// <returns></returns>
[WebMethod(true)]
public static string GetData3(string CKEY, string type)
{
	StringBuilder strSql = new StringBuilder();
	
	#region SQL语句

	if (type == "0")
	{
		#region 日
		strSql.AppendFormat(" WITH    WeekDate ");
		strSql.AppendFormat("         AS ( SELECT   DATEADD(d, -DAY(GETDATE()) + 1, GETDATE()) AS riqi ");
		strSql.AppendFormat("              UNION ALL ");
		strSql.AppendFormat("              SELECT   riqi + 1 FROM     WeekDate ");
		strSql.AppendFormat("              WHERE    riqi + 1 <= ( SELECT    DATEADD(d, -DAY(GETDATE()), DATEADD(m, 1, GETDATE())) ) ");
		strSql.AppendFormat("            ) ");
		strSql.AppendFormat("   SELECT  CONVERT(CHAR(8), a.riqi, 112) AS 日 ,DAY (CONVERT(CHAR(8), a.riqi, 112)) AS DDay, ");
		strSql.AppendFormat("           ISNULL(tbB.日成交量, 0) AS 日成交量 , ");
		strSql.AppendFormat("           CASE WHEN CONVERT(CHAR(8), a.riqi, 112) > CONVERT(CHAR(8), GETDATE(), 112) ");
		strSql.AppendFormat("                THEN NULL ");
		strSql.AppendFormat("                WHEN CONVERT(CHAR(8), a.riqi, 112) <= CONVERT(CHAR(8), GETDATE(), 112) ");
		strSql.AppendFormat("                THEN ISNULL(tbB.日成交量, 0) ");
		strSql.AppendFormat("           END AS 日成交数量 , ");
		strSql.AppendFormat("           tbB.日实收金额 , ");
		strSql.AppendFormat("           CASE WHEN CONVERT(CHAR(8), a.riqi, 112) > CONVERT(CHAR(8), GETDATE(), 112) ");
		strSql.AppendFormat("                THEN NULL ");
		strSql.AppendFormat("                WHEN CONVERT(CHAR(8), a.riqi, 112) <= CONVERT(CHAR(8), GETDATE(), 112) ");
		strSql.AppendFormat("                THEN ISNULL(tbB.日实收金额, 0) ");
		strSql.AppendFormat("           END AS 日实收金额2 ");
		strSql.AppendFormat("   FROM    WeekDate a ");
		strSql.AppendFormat("           LEFT JOIN ( SELECT  ( SELECT    COUNT(1) ");
		strSql.AppendFormat("                                 FROM      dbo.CustomerBase base ");
		strSql.AppendFormat("                                 WHERE     CKEY = '{0}' ", CKEY);
		strSql.AppendFormat("                                           AND  " + impomo.TotalConsumptionMon + " > 0  ");
		strSql.AppendFormat("                                           AND TargetDate = cus.TargetDate ");
		strSql.AppendFormat("                               ) 日成交量 , ");
		strSql.AppendFormat("                               ISNULL(( SELECT SUM(Total) ");
		strSql.AppendFormat("                                        FROM   ( SELECT    SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
		strSql.AppendFormat("                                                 FROM      PaymentContent AS pay ");
		strSql.AppendFormat("                                                 WHERE     PayDate = cus.TargetDate ");
		strSql.AppendFormat("                                                           AND pay.CKEY = '{0}' ", CKEY);
		strSql.AppendFormat("                                                 UNION ALL ");
		strSql.AppendFormat("                                                 SELECT    SUM(CONVERT(FLOAT, ISNULL(RecMoney, 0))) AS Total ");
		strSql.AppendFormat("                                                 FROM      dbo.CardRecharge8 AS recharge ");
		strSql.AppendFormat("                                                 WHERE     RechargDate = cus.TargetDate ");
		strSql.AppendFormat("                                                           AND recharge.CKEY = '{0}' ", CKEY);
		strSql.AppendFormat("                                                 UNION ALL ");
		strSql.AppendFormat("                                                 SELECT    SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
		strSql.AppendFormat("                                                 FROM      dbo.PaymentSwimming AS payswim ");
		strSql.AppendFormat("                                                 WHERE     PayDate = cus.TargetDate ");
		strSql.AppendFormat("                                                           AND payswim.CKEY = '{0}' ", CKEY);
		strSql.AppendFormat("                                                 UNION ALL ");
		strSql.AppendFormat("                                                 SELECT    SUM(CONVERT(FLOAT, ISNULL(( wp1 + wp2 + wp3 + wp4 + wp5 ), 0))) AS Total ");
		strSql.AppendFormat("                                                 FROM      WarePaymentContent AS ware ");
		strSql.AppendFormat("                                                 WHERE     PayDate = cus.TargetDate ");
		strSql.AppendFormat("                                                           AND ware.CKEY = '{0}' ", CKEY);
		strSql.AppendFormat("                                               ) B ");
		strSql.AppendFormat("                                      ), 0) AS 日实收金额 , ");
		strSql.AppendFormat("                               TargetDate 日 ");
		strSql.AppendFormat("                       FROM    dbo.CustomerBase cus ");
		strSql.AppendFormat("                       WHERE   YEAR(TargetDate) = YEAR(GETDATE()) ");
		strSql.AppendFormat("                               AND MONTH(TargetDate) = MONTH(GETDATE()) ");
		strSql.AppendFormat("                       GROUP BY TargetDate ");
		strSql.AppendFormat("                     ) AS tbB ON CONVERT(CHAR(8), a.riqi, 112) = tbB.日 ");
		#endregion
	}
	else if (type == "1")
	{
		#region 周
		strSql.AppendFormat("  WITH    WeekDate ");
		strSql.AppendFormat("             AS ( SELECT   DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0) AS riqi ");
		strSql.AppendFormat("                  UNION ALL ");
		strSql.AppendFormat("                  SELECT   riqi + 1 FROM     WeekDate ");
		strSql.AppendFormat("                  WHERE    riqi + 1 <= ( SELECT    DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 6) ) ");
		strSql.AppendFormat("                ) ");
		strSql.AppendFormat("       SELECT  CONVERT(CHAR(8), a.riqi, 112) AS 日 , ");
		strSql.AppendFormat("               DATENAME(weekday,CONVERT(CHAR(8), a.riqi, 112)) DDay, ");
		strSql.AppendFormat("               ISNULL(tbB.日成交量, 0) AS 日成交量 , ");
		strSql.AppendFormat("               CASE WHEN CONVERT(CHAR(8), a.riqi, 112) > CONVERT(CHAR(8), GETDATE(), 112) ");
		strSql.AppendFormat("                    THEN NULL ");
		strSql.AppendFormat("                    WHEN CONVERT(CHAR(8), a.riqi, 112) <= CONVERT(CHAR(8), GETDATE(), 112) ");
		strSql.AppendFormat("                    THEN ISNULL(tbB.日成交量, 0) ");
		strSql.AppendFormat("               END AS 日成交数量 , ");
		strSql.AppendFormat("               tbB.日实收金额 , ");
		strSql.AppendFormat("               CASE WHEN CONVERT(CHAR(8), a.riqi, 112) > CONVERT(CHAR(8), GETDATE(), 112) ");
		strSql.AppendFormat("                    THEN NULL ");
		strSql.AppendFormat("                    WHEN CONVERT(CHAR(8), a.riqi, 112) <= CONVERT(CHAR(8), GETDATE(), 112) ");
		strSql.AppendFormat("                    THEN ISNULL(tbB.日实收金额, 0) ");
		strSql.AppendFormat("               END AS 日实收金额2 ");
		strSql.AppendFormat("       FROM    WeekDate a ");
		strSql.AppendFormat("               LEFT JOIN ( SELECT  ( SELECT    COUNT(1) ");
		strSql.AppendFormat("                                     FROM      dbo.CustomerBase base ");
		strSql.AppendFormat("                                     WHERE     CKEY = '{0}'", CKEY);
		strSql.AppendFormat("                                               AND  " + impomo.TotalConsumptionMon + " > 0 ");
		strSql.AppendFormat("                                               AND TargetDate = cus.TargetDate ");
		strSql.AppendFormat("                                   ) 日成交量 , ");
		strSql.AppendFormat("                                   ISNULL(( SELECT SUM(Total) ");
		strSql.AppendFormat("                                            FROM   ( SELECT    SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
		strSql.AppendFormat("                                                     FROM      PaymentContent AS pay ");
		strSql.AppendFormat("                                                     WHERE     PayDate = cus.TargetDate ");
		strSql.AppendFormat("                                                               AND pay.CKEY = '{0}'", CKEY);
		strSql.AppendFormat("                                                     UNION ALL ");
		strSql.AppendFormat("                                                     SELECT    SUM(CONVERT(FLOAT, ISNULL(RecMoney, 0))) AS Total ");
		strSql.AppendFormat("                                                     FROM      dbo.CardRecharge8 AS recharge ");
		strSql.AppendFormat("                                                     WHERE     RechargDate = cus.TargetDate ");
		strSql.AppendFormat("                                                               AND recharge.CKEY = '{0}'", CKEY);
		strSql.AppendFormat("                                                     UNION ALL ");
		strSql.AppendFormat("                                                     SELECT    SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
		strSql.AppendFormat("                                                     FROM      dbo.PaymentSwimming AS payswim ");
		strSql.AppendFormat("                                                     WHERE     PayDate = cus.TargetDate ");
		strSql.AppendFormat("                                                               AND payswim.CKEY = '{0}'", CKEY);
		strSql.AppendFormat("                                                     UNION ALL ");
		strSql.AppendFormat("                                                     SELECT    SUM(CONVERT(FLOAT, ISNULL(( wp1 + wp2 + wp3 + wp4 + wp5 ), 0))) AS Total ");
		strSql.AppendFormat("                                                     FROM      WarePaymentContent AS ware ");
		strSql.AppendFormat("                                                     WHERE     PayDate = cus.TargetDate ");
		strSql.AppendFormat("                                                               AND ware.CKEY = '{0}'", CKEY);
		strSql.AppendFormat("                                                   ) B ");
		strSql.AppendFormat("                                          ), 0) AS 日实收金额 , ");
		strSql.AppendFormat("                                   TargetDate 日 ");
		strSql.AppendFormat("                           FROM    dbo.CustomerBase cus ");
		strSql.AppendFormat("                           WHERE   DATEPART(wk, TargetDate) = DATEPART(wk, GETDATE()) ");
		strSql.AppendFormat("                                   AND DATEPART(yy, TargetDate) = DATEPART(yy, GETDATE()) ");
		strSql.AppendFormat("                           GROUP BY TargetDate ");
		strSql.AppendFormat("                         ) AS tbB ON CONVERT(CHAR(8), a.riqi, 112) = tbB.日 ");
		#endregion
	}
	else if (type == "2")
	{
		#region 月

		strSql.AppendFormat("SELECT  YearMonth.月 , ");
		strSql.AppendFormat("       tb.月成交量 , ");
		strSql.AppendFormat("       CASE WHEN YearMonth.月 > MONTH(GETDATE()) THEN NULL ");
		strSql.AppendFormat("            WHEN YearMonth.月 <= MONTH(GETDATE()) THEN ISNULL(tb.月成交量, 0) ");
		strSql.AppendFormat("       END AS 月成交数量 , ");
		strSql.AppendFormat("       tb.月实收总金额 , ");
		strSql.AppendFormat("       CASE WHEN YearMonth.月 > MONTH(GETDATE()) THEN NULL ");
		strSql.AppendFormat("            WHEN YearMonth.月 <= MONTH(GETDATE()) THEN ISNULL(tb.月实收总金额, 0) ");
		strSql.AppendFormat("       END AS 月实收总金额2 ");
		strSql.AppendFormat(" FROM      ( SELECT 1 AS 月 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 ");
		strSql.AppendFormat("             UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 ");
		strSql.AppendFormat("           ) AS YearMonth ");
		strSql.AppendFormat("       LEFT JOIN ( SELECT  ( SELECT    COUNT(1) ");
		strSql.AppendFormat("                             FROM      dbo.CustomerBase base ");
		strSql.AppendFormat("                             WHERE     CKEY = '{0}' ", CKEY);
		strSql.AppendFormat("                                       AND  " + impomo.TotalConsumptionMon + " > 0  ");
		strSql.AppendFormat("                                       AND MONTH(TargetDate) = MONTH(cus.TargetDate) ");
		strSql.AppendFormat("                           ) 月成交量 , ");
		strSql.AppendFormat("                           ISNULL(( SELECT SUM(Total) ");
		strSql.AppendFormat("                                    FROM   ( SELECT    SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
		strSql.AppendFormat("                                             FROM      PaymentContent AS pay ");
		strSql.AppendFormat("                                             WHERE     MONTH(PayDate) = MONTH(cus.TargetDate) ");
		strSql.AppendFormat("                                                       AND pay.CKEY = '{0}' ", CKEY);
		strSql.AppendFormat("                                             UNION ALL ");
		strSql.AppendFormat("                                             SELECT    SUM(CONVERT(FLOAT, ISNULL(RecMoney, 0))) AS Total ");
		strSql.AppendFormat("                                             FROM      dbo.CardRecharge8 AS recharge ");
		strSql.AppendFormat("                                             WHERE     MONTH(RechargDate) = MONTH(cus.TargetDate) ");
		strSql.AppendFormat("                                                       AND recharge.CKEY = '{0}' ", CKEY);
		strSql.AppendFormat("                                             UNION ALL ");
		strSql.AppendFormat("                                             SELECT    SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
		strSql.AppendFormat("                                             FROM      dbo.PaymentSwimming AS payswim ");
		strSql.AppendFormat("                                             WHERE     MONTH(PayDate) = MONTH(cus.TargetDate) ");
		strSql.AppendFormat("                                                       AND payswim.CKEY = '{0}' ", CKEY);
		strSql.AppendFormat("                                             UNION ALL ");
		strSql.AppendFormat("                                             SELECT    SUM(CONVERT(FLOAT, ISNULL(( wp1 + wp2 + wp3 + wp4 + wp5 ), 0))) AS Total ");
		strSql.AppendFormat("                                             FROM      WarePaymentContent AS ware ");
		strSql.AppendFormat("                                             WHERE     MONTH(PayDate) = MONTH(cus.TargetDate) ");
		strSql.AppendFormat("                                                       AND ware.CKEY = '{0}' ", CKEY);
		strSql.AppendFormat("                                           ) B ");
		strSql.AppendFormat("                                  ), 0) AS 月实收总金额 , ");
		strSql.AppendFormat("                           MONTH(TargetDate) 月 ");
		strSql.AppendFormat("                   FROM    dbo.CustomerBase cus ");
		strSql.AppendFormat("                   WHERE   YEAR(TargetDate) = YEAR(GETDATE()) ");
		strSql.AppendFormat("                   GROUP BY MONTH(cus.TargetDate) ");
		strSql.AppendFormat("                 ) AS tb ON YearMonth.月 = tb.月 ");
		#endregion
	}
	else if (type == "3")
	{
		#region 年
		strSql.AppendFormat("SELECT  ( SELECT    COUNT(1) ");
		strSql.AppendFormat("             FROM      dbo.CustomerBase base ");
		strSql.AppendFormat("             WHERE     CKEY = '{0}' ", CKEY);
		strSql.AppendFormat("                       AND  " + impomo.TotalConsumptionMon + " > 0 ");
		strSql.AppendFormat("                       AND YEAR(TargetDate) = YEAR(cus.TargetDate) ");
		strSql.AppendFormat("           ) 年成交量 , ");
		strSql.AppendFormat("           CONVERT(NVARCHAR(20),CONVERT(DECIMAL(18,2),ISNULL(( SELECT SUM(Total) ");
		strSql.AppendFormat("                    FROM   ( SELECT    SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
		strSql.AppendFormat("                             FROM      PaymentContent AS pay ");
		strSql.AppendFormat("                             WHERE     YEAR(PayDate) = YEAR(cus.TargetDate) ");
		strSql.AppendFormat("                                       AND pay.CKEY = '{0}' ", CKEY);
		strSql.AppendFormat("                             UNION ALL ");
		strSql.AppendFormat("                             SELECT    SUM(CONVERT(FLOAT, ISNULL(RecMoney, 0))) AS Total ");
		strSql.AppendFormat("                             FROM      dbo.CardRecharge8 AS recharge ");
		strSql.AppendFormat("                             WHERE     YEAR(RechargDate) = YEAR(cus.TargetDate) ");
		strSql.AppendFormat("                                       AND recharge.CKEY = '{0}' ", CKEY);
		strSql.AppendFormat("                             UNION ALL ");
		strSql.AppendFormat("                             SELECT    SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
		strSql.AppendFormat("                             FROM      dbo.PaymentSwimming AS payswim ");
		strSql.AppendFormat("                             WHERE     YEAR(PayDate) = YEAR(cus.TargetDate) ");
		strSql.AppendFormat("                                       AND payswim.CKEY = '{0}' ", CKEY);
		strSql.AppendFormat("                             UNION ALL ");
		strSql.AppendFormat("                             SELECT    SUM(CONVERT(FLOAT, ISNULL(( wp1 + wp2 + wp3 + wp4 + wp5 ), 0))) AS Total ");
		strSql.AppendFormat("                             FROM      WarePaymentContent AS ware ");
		strSql.AppendFormat("                             WHERE     YEAR(PayDate) = YEAR(cus.TargetDate) ");
		strSql.AppendFormat("                                       AND ware.CKEY = '{0}' ", CKEY);
		strSql.AppendFormat("                           ) B ");
		strSql.AppendFormat("                  ), 0))) AS 年实收总金额 , ");
		strSql.AppendFormat("           YEAR(TargetDate) 年 ");
		strSql.AppendFormat("   FROM    dbo.CustomerBase cus ");
		strSql.AppendFormat("   GROUP BY YEAR(TargetDate) ");
		#endregion
	}

	#endregion

	DataTable table = DBHelper.GetDateTable(strSql.ToString());
	string rs = Newtonsoft.Json.JsonConvert.SerializeObject(table);
	return rs;
}