Skip navigation links

MySQL Forums :: German :: Date_Format Abfrage in Schichtsystem


Advanced Search

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
Date_Format Abfrage in Schichtsystem 1290 Frank Lipphardt 04/04/2012 03:11AM
Re: Date_Format Abfrage in Schichtsystem 712 Thomas Wiedmann 04/04/2012 05:07AM
Re: Date_Format Abfrage in Schichtsystem 505 Thomas Wiedmann 04/04/2012 06:48AM
Re: Date_Format Abfrage in Schichtsystem 593 Frank Lipphardt 04/05/2012 03:52AM
Re: Date_Format Abfrage in Schichtsystem 533 Thomas Wiedmann 04/05/2012 05:42AM


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.