MySQL Forums
Forum List  »  German

Re: Date_Format Abfrage in Schichtsystem
Posted by: Thomas Wiedmann
Date: April 04, 2012 06:48AM

Hallo Frank,

bezüglich dem Schrichtproblem noch folgende Idee dazu:


1. Schritt die Daten umrechnen (Tag abziehen)

00:00 - 06:29 : 1 Tag abziehen
06:30 - 24:00 : unverändert

2. Schritt, die Daten summieren

SELECT DATE_FORMAT(sz.DatumZeit, '%Y-%m-%d') AS tag,
       sz.teil_io_nio,
       COUNT(*) AS anzahl
  FROM ( SELECT DATE_SUB(datumzeit, INTERVAL 1 DAY) AS datumzeit, 
                teil_io_nio
           FROM dichtpr_uk
          WHERE DATE_FORMAT(DatumZeit, '%H-%i') < '06:30'
 
          UNION ALL

         SELECT datumzeit,
                teil_io_nio
           FROM dichtpr_uk
          WHERE DATE_FORMAT(DatumZeit, '%H-%i') >= '06:30'
       ) sz
       
 WHERE sz.DatumZeit BETWEEN '2012-03-28 00:00:00' AND '2012-04-05 23:59:59'
GROUP BY tag, sz.teil_io_nio
ORDER BY tag, sz.teil_io_nio; 
+------------+-------------+--------+
| tag        | teil_io_nio | anzahl |
+------------+-------------+--------+
| 2012-03-31 | GW          |      1 |
| 2012-03-31 | IO          |      1 |
| 2012-03-31 | NIO         |      1 |
| 2012-04-01 | GW          |      1 |
| 2012-04-01 | IO          |      1 |
| 2012-04-01 | NIO         |      1 |
| 2012-04-02 | GW          |      1 |
| 2012-04-02 | IO          |      1 |
| 2012-04-02 | NIO         |      1 |
+------------+-------------+--------+
9 rows in set (0.00 sec)

mysql>

Grüße
Thomas

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Date_Format Abfrage in Schichtsystem
764
April 04, 2012 06:48AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.