SQL statements to do report statistics

--some basic SQL functions used
--Returns the value of the middle-aged: SQL year function.
--Returns the value of the month in the date: SQL month function.
--Returns the value of the day of the date: SQL time function. --date_add (): function adds a specified time interval to a date.
--Last_day (): Returns the last day of the month for a time value
--Makedate (): Gives the year value and the number of days in a year, returns a date
--CONCAT: stitching date
--ELT matching expressions
--The SQL EXTRACT () function is used to extract values from a date expression (year, month, day, etc.)


-Now (): The current time, in the specific use can be replaced with the date format of any time
--Get current time yyyy-mm--dd hh:mm:ss
SELECT now () as Times
--Get the current date Yyyy-mm-dd
SELECT DATE (now ()) as Times

--Query year quarter month


--Check the last day of the month
SELECT Last_day (now ()) as times;

--Query the first day of the next January
SELECT Date_add (Last_day (now ()), INTERVAL 1 day) as Times

--Query the first day of the month
SELECT Date_add (Date_add (Last_day (now), INTERVAL 1 day), INTERVAL-1 MONTH) as Times


--Check quarterly


--Check the current quarter
SELECT QUARTER (now ()) as Times

--Query the first month of a quarter
SELECT ELT (QUARTER (now ()), 1,4,7,10) as Times

--Get the first day of the quarter
SELECT DATE (CONCAT (now ()), '-', ELT (QUARTER (now ()), 1,4,7,10), '-1 ') as Times


--Get the first day of the year
SELECT makedate (EXTRACT (year from Now ()), 1) as Times

--Get the last day of the quarter
SELECT Last_day (Makedate (EXTRACT (), 1) + INTERVAL QUARTER (now ()) *3-1 MONTH) as Times


--Get the first day of the first month of the quarter
SELECT Date_add (Date_add (Last_day (Makedate (EXTRACT (), 1) + INTERVAL QUARTER (now ()) *3-3 MONTH), INTERVAL 1 Day), INTERVAL-1 MONTH) as Times

--Get the last day of the first month of the quarter
SELECT Last_day (Makedate (EXTRACT (), 1) + INTERVAL QUARTER (now ()) *3-3 MONTH) as Times
--Of course different quarters can be obtained by changing the parameters behind the multiply


--a statistical statement used
--Achieve the first month of the corresponding date quarter sales statistics for individual products (statistics on sales orders) Dataex: For a time variable
SELECT b.productname productnames,sum (b.ordercount) ordercounts,sum (b.subtotal) subtotals from orders A,child_orders b WHERE a.id = b.orders_id and b.productname = ' productnames '
and A.create_date >= Date_add (Date_add (Last_day (makedate (year from ' EXTRACT '), 1) + +dataex+ INTERVAL (' "+ dataex+ "') *3-3 month), INTERVAL 1 day), INTERVAL-1 month) and A.create_date <= Last_day (Makedate (EXTRACT (the year from ' +d ataex+ "'), 1) + INTERVAL QUARTER ('" +dataex+ "') *3-3 MONTH)
GROUP by Productnames ORDER by A.create_date DESC

Note:
Of course, this is only a single query statement, to achieve the same function, of course, there are other statements, here is just what I used to feel that can be the statement, the implementation of statistics of course there is a lot of work to do.
back also need to learn more, there is not the right place, but also hope that the great God to give guidance, Thanks

SQL statements to do report statistics