MySQL data statistics based on stored procedure statistics template by day, week, month

The stored procedure Developer_count is based on the incoming parameter SearchType decision is to use that query method, the other parameters contained in this stored procedure is {start time: Startime, End time: Endtime}

[HTML]View PlainCopy

2. CREATE PROCEDURE Developer_count
4. (
6. SearchType int,
8. StartTime varchar (64),
10. EndTime varchar (64)
12. )
14. BEGIN
16. /* Define Variable days */
18. declare day_num int;
22. If searchtype = 1 Then
24. /* Data query this week */
26. Select count (d.acct_id), d.acct_old_time from developer D where 1=1 and Date_sub (Curdate (), INTERVAL 7 day) &L  t;= Date (d.acct_old_time) GROUP by D.acct_old_time;
28. End If;
30. If searchtype = 2 Then
32. /* Data query this month */
34. Select count (d.acct_id), d.acct_old_time from developer D where 1=1 and Date_sub (Curdate (), INTERVAL-day) &  lt;= Date (d.acct_old_time) GROUP by D.acct_old_time;
36. End If;
38. If searchtype = 3 Then
40. /* Data query for the last three months */
42. Select count (d.acct_id), Date_format (D.acct_old_time, '%x year-%v week ') as weeks from developer D where 1=1 and Date_sub (Curdate (), INTERVAL Day)  <= Date (d.acct_old_time) GROUP by weeks;
44. End If;
46. If searchtype = 4 Then
48. /* Data statistics by Month */
50. Select DateDiff (StartTime, endTime) into Day_num;
52. If Day_num <=7 Then
54. Select count (d.acct_id), d.acct_old_time from developer D where 1=1 and Date_sub (Curdate (), INTERVAL Day_num Day)  <= Date (d.acct_old_time) GROUP by D.acct_old_time;
56. End If;
58. If Day_num >7 && day_num <=
60. Select count (d.acct_id), d.acct_old_time from developer D where 1=1 and Date_sub (Curdate (), INTERVAL Day_num Day)  <= Date (d.acct_old_time) GROUP by D.acct_old_time;
62. End If;
64. If Day_num >30 && day_num <=
66. Select count (d.acct_id), Date_format (D.acct_old_time, '%x year-%v week ') as weeks from developer D where 1=1 and Date_sub (Curdate (), INTERVAL Day_num Day)  <= Date (d.acct_old_time) GROUP by weeks;
68. End If;
70. End If;
74. End

Calling a stored procedure method

Call Developer_count (1, ' 2016-06-07 ', ' 2016-06-16 '); Calling a stored procedure

MySQL-based stored procedures for data statistics by day, week, month statistics template