I have next data:
mysql> select no,crt_date,tobilling_date,sent_to_client,dop_prov from assistfin limit 20;
+--------+---------------------+---------------------+----------------+------------+
| no | crt_date | tobilling_date | sent_to_client | dop_prov |
+--------+---------------------+---------------------+----------------+------------+
| 50.01 | 2014-02-05 10:28:10 | 2014-02-05 14:42:35 | 2014-04-16 | 2014-09-23 |
| 123.01 | 2014-02-05 19:17:36 | 2014-03-17 18:58:05 | 2014-04-10 | 2014-06-30 |
| 51.01 | 2014-02-06 00:09:32 | 2014-03-20 16:53:46 | 2014-04-10 | 2014-06-30 |
| 124.01 | 2014-02-06 15:29:08 | 2014-03-20 17:04:42 | 2014-04-10 | 2014-06-30 |
| 230.01 | 2014-02-07 22:01:11 | 2014-03-20 16:41:03 | 2014-04-10 | 2014-06-30 |
| 252.01 | 2014-02-08 02:52:33 | 2014-03-20 16:43:03 | 2014-04-10 | 2014-06-30 |
| 123.02 | 2014-02-08 03:00:52 | 2014-03-17 18:58:10 | 2014-04-10 | 2014-06-30 |
| 213.01 | 2014-02-08 04:01:35 | 2014-03-26 19:03:01 | 2014-04-10 | 2014-09-19 |
| 55.01 | 2014-02-08 21:04:45 | 2014-03-07 18:40:46 | NULL | 2014-06-26 |
| 126.01 | 2014-02-08 21:46:58 | 2014-09-02 18:39:36 | 2014-09-09 | 2014-09-26 |
| 284.01 | 2014-02-09 01:52:54 | 2014-06-11 19:11:06 | 2014-07-02 | 2014-07-21 |
| 261.01 | 2014-02-09 02:20:34 | 2014-03-17 20:57:39 | 2014-04-10 | 2014-06-30 |
| 318.01 | 2014-02-09 03:09:28 | 2014-03-17 20:44:25 | 2014-04-10 | 2014-06-30 |
| 225.01 | 2015-02-10 03:21:08 | 2014-03-20 16:57:56 | 2014-04-10 | 2014-06-30 |
| 248.01 | 2014-02-09 03:30:58 | 2014-03-18 18:02:21 | 2014-04-10 | 2014-06-30 |
| 178.01 | 2014-04-05 03:35:25 | 2014-03-21 17:10:12 | 2014-04-10 | 2014-06-30 |
| 184.01 | 2014-04-08 04:01:13 | 2015-03-20 16:38:02 | 2015-04-10 | 2015-06-30 |
| 320.01 | 2014-04-08 05:57:23 | 2015-03-17 20:49:19 | 2015-04-10 | 2015-06-30 |
| 230.02 | 2015-05-08 06:18:15 | 2016-03-20 16:41:08 | 2016-04-10 | 2016-06-06 |
| 325.01 | 2014-05-09 06:23:50 | 2015-03-17 20:42:04 | 2015-04-10 | 2015-06-30 |
+--------+---------------------+---------------------+----------------+------------+
Need to get next data:
+---------+---------+--------+-----------+---------+
| year | Created | Passed | To client | To prov |
+---------+---------+--------+-----------+---------+
| 2016-01 | 1901 | 1879 | 1873 | 1743 |
| 2016-02 | 2192 | 2169 | 2114 | 1912 |
| 2016-03 | 2693 | 2639 | 2539 | 2309 |
| 2016-04 | 2634 | 2574 | 2273 | 1976 |
| 2016-05 | 2593 | 2497 | 1109 | 949 |
| 2016-06 | 471 | 449 | 2 | 78 |
+---------+---------+--------+-----------+---------+
Where year like DATE_FORMAT(curdate(), '%Y-%m')
, next column Count(assistfin.crt_date)
as Created
.
The problem is that crt_date
can be like 2015%
, but sent_to_client
or dop_prov
can be like 2016%
.
How to make correct query?
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…