Recent time from the client to the background to write services, for the background database and service writing is a small white, so the recent written certainly not too much technical content.
First put the problems encountered: or that error on the report, the main fields have reported error ID (ErrorID), reported (Reportperson), escalation time (reporttime) accurate to milliseconds, now to do the statistics are: (1) Statistics for a certain period of time " From start to end time is accurate to milliseconds (Begintime,endtime) The number of errors reported by each person per day (2) Statistics The total number of errors reported by each person in a certain period of time from start to end time to month (Begintime,endtime).
The first thought of seeing the problem was to group by Reportperson, can calculate everyone's statistics, but not meet the requirements, but also need to get everyone's daily statistics, for me such a rookie is a little trouble, do not know how to do, in the SQL group to find a master, Tell the need to set the time format, and then limit the time format to days and months, these two issues are resolved.
A number of time functions have been listed in the previous article, and now the cast and convert used in this article are described in detail:
Let's take a look at their grammar:
CAST (expression as data_type [(length)])
CONVERT (data_type [(length)], expression [, style])
which
Expression: Any valid expression.
Data_type: Target data type. This includes XML, bigint, and sql_variant. You cannot use an alias data type.
Length: An optional integer specifying the length of the destination data type. The default value is 30.
Style: Specifies how the CONVERT function converts an integer expression of expression. If the style is NULL, NULL is returned. The scope is determined by data_type.
The main related to this article is the length and expression,length of the target type, which is used to limit the precision of the time, expression is the format used to qualify the output time, such as: Yyyy/mm/dd/yyyy-mm-dd.
The relevant knowledge of express and the use of examples can refer to this blog post:
Http://www.jb51.net/article/33330.htm
Now use this knowledge to solve my problems, the first is the daily Statistics reported, accurate to the date limit: convert (varchar (one), Reporttime, 20) that is YYYY-MM-DD
The group by problem is then resolved with the SQL code:
Copy Code code as follows:
Select Reportperson,convert (varchar, reporttime,) as ' Reportime ', COUNT (*) as reporttotal from Pcr_constructinfo Where (reporttime> ' 2012-11-15 12:11:12.23 ') and (reporttime< ' 2013-1-16 12:11:12.23 ') Group by Reportperson, CONVERT (varchar (one), Reporttime, 20)
The results of the execution are:
Another problem is to make monthly statistics on the amount of error reported by each person, as long as the date is limited to the month, that is: CONVERT (varchar (7), Reporttime,) yyyy-mm execute the SQL statement as:
Copy Code code as follows:
Select Reportperson,convert (varchar (7), Reporttime,) as ' Reporttime ', COUNT (*) as reporttotal from Pcr_constructinfo Where (reporttime> ' 2012-11-1 ') and (reporttime< ' 2013-2-1 ') group by Reportperson,convert (varchar (7), Reporttime , 20)
The results of the execution are:
Well, let's call it a day, if any master has a better way, welcome to message!!!
