Re: Date_Format Abfrage in Schichtsystem
Hallo Thomas
Vielen Dank für die schnelle Antwort. Echt Super was Du Dir für eine Mühe gemacht hast:
Der zweite Ansatz sieht Vielversprechend aus, nur sollte ein Tag in einer Reihe stehen. Werde das jetzt mal Testen (wenn die Arbeit das zulässt).
Ich hab hier mal die Originaltabelle und ein Paar Originalwerte.
CREATE TABLE `dq500`.`dichtpr_uk` ( `Teilnummer` double NOT NULL ,
`DatumZeit` datetime NOT NULL ,
`Station` int( 2 ) NOT NULL ,
`typ` varchar( 1 ) collate utf8_bin NOT NULL default '0',
`Teil_IO_NIO` varchar( 3 ) collate utf8_bin NOT NULL ,
`1_IO_NIO` varchar( 3 ) collate utf8_bin NOT NULL ,
`1_LR` decimal( 8, 3 ) NOT NULL ,
`2_IO_NIO` varchar( 3 ) collate utf8_bin NOT NULL ,
`2_LR` decimal( 8, 3 ) NOT NULL ,
`3_IO_NIO` varchar( 3 ) collate utf8_bin NOT NULL ,
`3_LR` decimal( 8, 3 ) NOT NULL ,
`4_IO_NIO` varchar( 3 ) collate utf8_bin NOT NULL ,
`4_LR` decimal( 8, 3 ) NOT NULL ,
`5_IO_NIO` varchar( 3 ) collate utf8_bin NOT NULL ,
`5_LR` decimal( 8, 3 ) NOT NULL ,
`6_IO_NIO` varchar( 3 ) collate utf8_bin NOT NULL ,
`6_LR` decimal( 8, 3 ) NOT NULL ,
`7_IO_NIO` varchar( 3 ) collate utf8_bin NOT NULL ,
`7_LR` decimal( 8, 3 ) NOT NULL ,
`8_IO_NIO` varchar( 3 ) collate utf8_bin NOT NULL ,
`8_LR` decimal( 8, 3 ) NOT NULL ,
`Nest` int( 1 ) NOT NULL default '0',
PRIMARY KEY ( `Teilnummer` ) ) ENGINE = MyISAM DEFAULT CHARSET = utf8 COLLATE = utf8_bin;
insert dichtpr_uk(Teilnummer, DatumZeit, Station, typ, Teil_IO_NIO, Nest, 1_IO_NIO, 1_LR, 2_IO_NIO, 2_LR, 3_IO_NIO, 3_LR, 4_IO_NIO, 4_LR, 5_IO_NIO, 5_LR, 6_IO_NIO, 6_LR, 7_IO_NIO, 7_LR, 8_IO_NIO, 8_LR )
values
('120320065840', '2012-03-20 07:02:38', '2', 'R', 'IO', '0', 'IO', '0.54', 'IO', '0.16', 'IO', '0.25', 'IO', '-1.25', 'IO', '0.61', 'IO', '1.12', 'IO', '0.15', 'IO', '0.60'),
('120320065843', '2012-03-20 07:02:41', '1', 'V', 'IO', '0', 'IO', '-0.11', 'IO', '-0.01', 'IO', '0.07', 'IO', '-1.63', 'IO', '-0.10', 'IO', '-0.09', 'IO', '0.18', 'IO', '5.75'),
('120320054445', '2012-03-20 07:03:52', '2', 'R', 'IO', '3', 'IO', '0.01', 'IO', '0.02', 'IO', '0.21', 'IO', '-1.81', 'IO', '0.12', 'IO', '0.23', 'IO', '0.14', 'IO', '0.45'),
('120320070057', '2012-03-21 07:04:55', '1', 'V', 'NIO', '0', 'IO', '-0.09', 'IO', '0.19', 'IO', '0.07', 'NIO', '9999.99', 'IO', '-0.09', 'IO', '-0.08', 'IO', '0.19', 'NIO', '977.78'),
('120320054519', '2012-03-21 07:06:07', '2', 'R', 'IO', '2', 'IO', '0.17', 'IO', '0.55', 'IO', '0.25', 'IO', '6.00', 'IO', '0.14', 'IO', '0.30', 'IO', '0.13', 'IO', '6.75'),
('120320095514', '2012-03-22 09:59:08', '1', 'V', 'IO', '0', 'IO', '0.02', 'IO', '0.04', 'IO', '0.08', 'IO', '7.22', 'IO', '0.20', 'IO', '0.23', 'IO', '0.16', 'IO', '5.25'),
('120320095738', '2012-03-22 10:01:32', '1', 'V', 'IO', '0', 'IO', '0.02', 'IO', '0.28', 'IO', '0.07', 'IO', '6.41', 'IO', '0.20', 'IO', '0.20', 'IO', '0.16', 'IO', '4.97'),
('120320095959', '2012-03-23 10:03:53', '2', 'R', 'IO', '0', 'IO', '0.13', 'IO', '0.14', 'IO', '0.25', 'IO', '6.33', 'IO', '0.33', 'IO', '0.50', 'IO', '0.14', 'IO', '4.33'),
('120320101310', '2012-03-23 10:17:05', '2', 'R', 'IO', '0', 'IO', '0.15', 'IO', '0.18', 'IO', '0.25', 'IO', '6.90', 'IO', '0.36', 'IO', '0.50', 'IO', '0.11', 'IO', '4.40'),
('120320101531', '2012-03-24 10:19:25', '2', 'R', 'GW', '0', 'IO', '0.22', 'GW', '28.70', 'IO', '0.33', 'IO', '5.84', 'IO', '0.39', 'GW', '22.50', 'IO', '0.14', 'IO', '4.68'),
('120320101752', '2012-03-24 10:21:46', '1', 'V', 'IO', '0', 'IO', '0.09', 'IO', '0.07', 'IO', '0.07', 'IO', '6.95', 'IO', '0.32', 'IO', '0.28', 'IO', '0.23', 'IO', '7.51')
Die Tabelle sollte dann etwa so aussehen
Datum IO NIO GW
20.03. 3 0 0
21.03. 1 1 0
22.03. 2 0 0
23.03. 2 0 0
24.03 1 0 1
Gruß Frank