Menu
What I have is:
| Equipment Name | Event | Time of Event |
-------------------------------------------------------------
| EX001 | Brake Active | 2014-09-29 20:05:10 000 |
| EX001 | Brake Active | 2014-09-29 20:07:10 000 |
| EX001 | Brake Active | 2014-09-30 06:07:10 000 |
| EX001 | Brake Active | 2014-09-30 18:05:10 000 |
| EX001 | Brake Inactive | 2014-09-29 20:05:45 000 |
| EX001 | Brake Inactive | 2014-09-29 20:06:10 000 |
| EX001 | Brake Inactive | 2014-09-29 23:01:10 000 |
and so on
I want to find out the number of Events in each date from first it happened count to last time it happened and show the date and the count of it. i have a query to count it in days but unable to show the fist and the last time it happened.
What I want is:
| Equipment Name | Event | First Occurrence | Last Occurrence | Event Count |
-----------------------------------------------------------------------------------------
| EX001 | Brake Active | 2014-09-29 20:05 | 2014-09-30 18:05 | 4 |
| EX001 | Brake Inactive | 2014-09-29 20:45 | 2014-09-29 23:01 | 3 |
and so on
All from one table
Please help guys.
445
To get the first and last record, use UNION. LIMIT is also used to get the number of records you want.
The logic is very simple. The first part @DATE-DAY(@DATE) results to the Last day of a previous month and adding 1 to it will result on the first day of current month. The second part EOMONTH(@DATE) makes use of SYSTEM function EOMONTH which results to the last day of the given date.
Try this:
SELECT MIN(timeevent), MAX(timeevent), COUNT(*)
FROM equipment
GROUP BY equipmentname, event
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
