Komplexe Abfrage aus zwei Tabellen

[Forumsgast]

Hallo,

leider ist mit kein besserer Thread Titel eingefallen. Sorry dafür.

Ich habe zwei Tabellen

Tabelle A:

id INT AUTOINC PRIMARY
jobnumber VARCHAR[50]
start DATETIME
end DATETIME

Tabelle B:

id INT AUTOINC PRIMARY
jobnumber VARCHAR[50]
start DATETIME
end DATETIME


Das sind natürlich nur die für die Frage hier relevanten Spalten.

Beide Tabelle beinhalten also Informationen über einen Job. Es sind zwei Tabelle von zwei unabhängigen Tools und die Daten werden von verschiedenen Leuten eingetragen.
Im Normalfall sollten die Daten der Jobs überein stimmen. Leider kommt es immer mal wieder vor, dass eine der beiden Stellen einen Job ändert aber die anderen Stelle das nicht mitbekommt. Das soll ich herausfinden und dann entsprechend Alarm schlagen.

Das wäre jetzt ziemlich einfach, wenn es da nicht einen entscheidenden Haken gäbe:

In der Tabelle A wird der Job immer mit nur einer Row gespeichert. Also z.B.

0, job1, 2015-07-01 00:00:00, 2015-07-05 00:00:00

Der job1 geht also vom 1-5.7.2015.

In Tabelle B sieht so ein Jobeintrag dann aber so aus:

0, job1, 2015-07-01 08:30:00, 2015-07-01 17:00:00
1, job1, 2015-07-02 08:30:00, 2015-07-02 17:00:00
2, job1, 2015-07-03 08:30:00, 2015-07-03 17:00:00
3, job1, 2015-07-04 08:30:00, 2015-07-04 17:00:00
4, job1, 2015-07-05 08:30:00, 2015-07-05 17:00:00

Ich muss also quasi nun herausfinden ob der Startwert von jobX in Tabelle A == dem kleinsten Startwert von JobX in Tabelle B ist und das gleiche für den Endwert.

Habt ihr da eine Idee?

Gruß

Claus

[Forumsgast]

Wäre es keine Möglichkeit, die Datumswerte via DATE zu normalisieren?

[Forumsgast]

Wäre es keine Möglichkeit, die Datumswerte via DATE zu normalisieren?

Sicher das ist nicht das Problem. Die Uhrzeit interessiert mich eh nicht. Aber wie bekomme ich es hin das er nur die Datensätze liest, in denen die Startzeiten oder die Endzeiten nicht überein stimmen?

Gruß

Claus

[Forumsgast]

SELECT
   ...
FROM
   a LEFT JOIN
   (
      SELECT MIN(DATE(start)) AS start, MAX(DATE(end)) AS end FROM b GROUP BY job
   ) AS t ON (a.job = b.job)
WHERE
   a.start != t.start OR
   b.end != t.end

[Forumsgast]

SELECT
...
FROM
a LEFT JOIN
(
SELECT MIN(DATE(start)) AS start, MAX(DATE(end)) AS end FROM b GROUP BY job
) AS t ON (a.job = b.job)
WHERE
a.start != t.start OR
b.end != t.end

Ein subselect als JOIN. Das ist genial. Darauf wäre ich nie gekommen.

Danke

Claus

[Forumsgast]

SELECT
...
FROM
a LEFT JOIN
(
SELECT job AS bJob, MIN(DATE(start)) AS start, MAX(DATE(end)) AS end FROM b GROUP BY job
) AS t ON (a.job = bJob)
WHERE
a.start != t.start OR
b.end != t.end

Ein kleiner Fehler war drin. Man muss den job mit in den SUBSELECT nehmen sonst quängelt er beim ON das er ihn nicht kennt. Aber sonst scheint es zu funktionieren. Ich teste das jetzt mal ausgiebig.

Gruß

Claus

[Forumsgast]

Alternativ:

SELECT 
    a.jobnumber, 
    a.`start`, 
    a.`end`, 
    b.count as `existing_entries_for_range`, 
    ABS(DATEDIFF(a.`start`, a.`end`)) as `should_have_entries_for_range`
FROM jobs1 as a 
JOIN ( 
    SELECT jobnumber, `start`, `end` , COUNT(jobnumber) as count FROM jobs2 GROUP BY jobnumber
) as b ON ( a.jobnumber = b.jobnumber )
WHERE 
    b.count != DATEDIFF(a.`end`, a.`start`) + 1

zum nachstellen genutzte Daten:

INSERT INTO jobs1 ( `jobnumber`, `start`, `end` )
VALUES
    ( 'job1', '2015-07-01', '2015-07-07' ),
    ( 'job2', '2015-07-08', '2015-07-15' )
;

INSERT INTO jobs2 ( `jobnumber`, `start`, `end` )
VALUES
    ( 'job1', '2015-07-01 08:00:00', '2015-07-01 18:00:00' ),
    ( 'job1', '2015-07-02 08:00:00', '2015-07-02 18:00:00' ),
    ( 'job1', '2015-07-03 08:00:00', '2015-07-03 18:00:00' ),
    ( 'job1', '2015-07-04 08:00:00', '2015-07-04 18:00:00' ),
    ( 'job1', '2015-07-05 08:00:00', '2015-07-05 18:00:00' ),
    ( 'job1', '2015-07-06 08:00:00', '2015-07-06 18:00:00' ),
    ( 'job1', '2015-07-07 08:00:00', '2015-07-07 18:00:00' ),
    ( 'job2', '2015-07-08 08:00:00', '2015-07-08 18:00:00' ),
    ( 'job2', '2015-07-09 08:00:00', '2015-07-09 18:00:00' )
;

[Forumsgast]

Auch eine nette Ide über die Anzahl Tage zu gehen aber dann müßte ich doch auch noch das Startdatum miteinander vergleichen oder nicht?

Gruß

Claus

[Forumsgast]

Auch eine nette Ide über die Anzahl Tage zu gehen aber dann müßte ich doch auch noch das Startdatum miteinander vergleichen oder nicht?

Gruß

Claus

Theoretisch ja, kommt allerdings auch darauf an wie er was wo überhaupt implementiert. Aus dem Grund hab ich das erstmal raus gelassen.

[Forumsgast]

Um es jetzt noch etwas spannender zu machen.

Ich brauche zusätzlich auch noch die Rows aus Tabelle A zu denen es keinen korrespondierenden job in Tabelle B gibt aber nicht anders herum.

Habe versucht das mit einem SUBSELECT mit einem COUNT() zu machen aber ich bin gescheitert.

Gruß

Claus

[Forumsgast]

UNION und danach erneut selektieren ?

[Forumsgast]

Ich habe es gerademal mit einem ganz einfachen

OR b.job IS NULL

probiert. Sieht gar nicht so schlecht aus.