Złączenie rekordów z takimi samymi polami Opcje

[webdice]

Witajcie,

mam problem z oznaczeniem zmian pracownika które są obok siebie (tych których data rozpoczęcia jest taka sama jak data zakończenia poprzedniej). Posiadam dwie tabele - zmiany z długością jej trwania oraz zmiany przypisane użytkownikowi wraz z datą i godziną rozpoczęcia.

Kod

mysql> describe shifts;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| ID       | int(10)          | NO   | PRI | NULL    | auto_increment |
| DURATION | time             | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+

Kod

mysql> describe employees_shifts;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| ID          | int(10)          | NO   | PRI | NULL    | auto_increment |
| SHIFT_ID    | int(10)          | NO   |     | NULL    |                |
| DATE        | datetime         | NO   |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

Dla lepszego zobrazowania przykład:

Kod

od 2012-02-20 15:00:00 do 2012-02-20 18:00:00
od 2012-02-22 10:00:00 do 2012-02-22 20:00:00 - data zakończenia tej zmiany jest taka sama jak data rozpoczęcia następnej zmiany
od 2012-02-22 20:00:00 do 2012-02-22 21:00:00 - data rozpoczęcia tej zmiany jest taka sama jak data zakończenia poprzedniej zmiany
od 2012-02-25 15:00:00 do 2012-02-25 18:00:00

Chodzi o oznaczenie dwóch środkowych zmian. Oznaczenie pierwszej zmiany nie jest problem:

[SQL] pobierz, plaintext 
SELECT
 
`ES`.`DATE` AS `ES_START_DATE`, -- Czas rozpoczęcia zmiany.
ADDTIME( `ES`.`date`, `S`.`duration` ) AS `ES_END_DATE`, -- Czas zakończenia zmiany.
`ES2`.`ID` AS `ES2_ID` -- Wartość inna niż NULL oznacza że data zakończenia tej zmiany jest taka sama jak data rozpoczęcia następnej.
 
FROM `EMPLOYEES_SHIFTS` AS `ES`
LEFT JOIN `SHIFTS` AS `S` ON( `ES`.`SHIFT_ID` = `S`.`ID` )
LEFT JOIN `EMPLOYEES_SHIFTS` AS `ES2` ON( ADDTIME( `ES`.`date`, `S`.`duration` ) = `ES2`.`DATE` )
[SQL] pobierz, plaintext 

Kod

+---------------------+---------------------+--------+
| ES_START_DATE       | ES_END_DATE         | ES2_ID |
+---------------------+---------------------+--------+
| 2012-02-20 15:00:00 | 2012-02-20 18:00:00 |   NULL |
| 2012-02-22 10:00:00 | 2012-02-22 20:00:00 |      3 |
| 2012-02-22 20:00:00 | 2012-02-22 21:00:00 |   NULL | <- Muszę oznaczyć jeszcze ten rekord.
| 2012-02-25 15:00:00 | 2012-02-25 18:00:00 |   NULL |
+---------------------+---------------------+--------+

Problem pojawia się przy próbie oznaczenia zmiany której data rozpoczęcia jest taka sama jak data zakończenia poprzedniej. Spowodowane jest to brakiem możliwości obliczenia daty zakończenia zmiany w łączonej tabeli. Mi nic konkretnego do głowy nie przychodzi.

Strukturę tabel oraz przykładowe dane można pobrać tutaj.

PS. Przepraszam za tytuł, ale kompletnie nie mam pomysłu .

[pmir13]

Jak miałoby takie oznaczenie wyglądać? Jako dodatkowa kolumna? Bo jesli dany rekord miałby zarówno "poprzednika" jak i "następnika" to trudno byłoby oznaczyć go w ten sposób w jednej kolumnie. Możliwe też, że gdyby wiadomo było nieco więcej na temat do czego to oznaczanie miałoby służyć to być może znalazł by się inny, prostszy na to sposób. Czy chodzi o połączenie tych zmian, czy może o znalezienie ciągłych bloków? Jeśli miałaby to być tylko lista rekordów z ewentualnymi znacznikami typu "ten rekord ma poprzednika" albo "ten rekord ma następnika" to należałoby myśleć w kategorii tablicy, w której mamy obie daty, rozpoczęcia i zakończenia zmiany (w zapytaniu podstawiając w to miejsce w razie potrzeby podzapytanie) i łączyć tą stworzoną w ten sposób niby tabelę ze sobą w obie strony.
Czyli przykładowo:

[SQL] pobierz, plaintext 
SELECT 
`ESD1`.`ES_START_DATE`,  -- Czas rozpoczęcia zmiany.
`ESD1`.`ES_END_DATE`,  -- Czas zakończenia zmiany.     
`ES1`.`ID` AS NEXT_ID,  -- Wartość inna niż NULL oznacza że data zakończenia tej zmiany jest taka sama jak data rozpoczęcia następnej.
`ESD2`.`ID` AS PREV_ID -- Wartość inna niż NULL oznacza że data rozpoczęcia tej zmiany jest taka sama jak data zakończenia poprzedniej.
FROM
  (
    SELECT `ES`.`ID` AS `ID`,
    `ES`.`DATE` AS `ES_START_DATE`,
    ADDTIME( `ES`.`date`, `S`.`duration` ) AS `ES_END_DATE` 
    FROM `EMPLOYEES_SHIFTS` AS `ES`
    LEFT JOIN `SHIFTS` AS `S` ON `ES`.`SHIFT_ID` = `S`.`ID`  ) ESD1
LEFT JOIN `EMPLOYEES_SHIFTS` AS `ES1`
ON `ESD1`.`ES_END_DATE` = `ES1`.`date`
LEFT JOIN
  (
    SELECT `ES`.`ID` AS `ID`,
    `ES`.`DATE` AS `ES_START_DATE`,
    ADDTIME( `ES`.`date`, `S`.`duration` ) AS `ES_END_DATE`  
    FROM `EMPLOYEES_SHIFTS` AS `ES`
    LEFT JOIN `SHIFTS` AS `S` ON `ES`.`SHIFT_ID` = `S`.`ID`  ) ESD2
ON `ESD1`.`ES_START_DATE` = `ESD2`.`ES_END_DATE`
[SQL] pobierz, plaintext 

[webdice]

Przykład który podałem to może 10% całego zapytania. Twój przykład działa tak jak należy (potrzebuje pobrać datę zakończenia/rozpoczęcia zmiany), ale będzie mi go bardzo ciężko dostosować do mojego zapytania.

[SQL] pobierz, plaintext 
SELECT
 
`ES`.`DATE` AS `ES_START_DATE`, -- Czas rozpoczęcia zmiany.
ADDTIME( `ES`.`date`, `S`.`duration` ) AS `ES_END_DATE`, -- Czas zakończenia zmiany.
`ES2`.`ID` AS `ES2_ID` -- Wartość inna niż NULL oznacza że data zakończenia tej zmiany jest taka sama jak data rozpoczęcia następnej.
 
FROM `EMPLOYEES_SHIFTS` AS `ES`
LEFT JOIN `SHIFTS` AS `S` ON( `ES`.`SHIFT_ID` = `S`.`ID` )
LEFT JOIN `EMPLOYEES_SHIFTS` AS `ES2` ON( ADDTIME( `ES`.`date`, `S`.`duration` ) = `ES2`.`DATE` ) -- To jest odpowiednik Twojego pierwszego podzapytania, szukam czegoś co pobrałoby datę rozpoczęcia dla drugiej zmiany
[SQL] pobierz, plaintext 

Najlepszym wyjściem było by coś w rodzaju:

[SQL] pobierz, plaintext 
LEFT JOIN `EMPLOYEES_SHIFTS` AS `ES3` ON( `ES`.`DATE` = ADDTIME( `ES3`.`date`, `S2`.`duration` ) )
[SQL] pobierz, plaintext 

ale nie mogę się odwołać do S2.duration.

[pmir13]

Ta następna łączona tablica musi mieć charakter podzapytania z obliczonymi datami zakończenia zmiany na podstawie długości trwania zmian odpowiednich właśnie dla niej, a nie dla pierwszej tablicy, do której resztę łączymy.
Na czym polega trudność w dostosowaniu tego do całego zapytania? Może warto napisać coś więcej o tym dużym zapytaniu?

[webdice]

Ta następna łączona tablica musi mieć charakter podzapytania z obliczonymi datami zakończenia zmiany na podstawie długości trwania zmian odpowiednich właśnie dla niej, a nie dla pierwszej tablicy, do której resztę łączymy.

Doskonale o tym wiem, zresztą to napisałem. Dlatego pytam o jakąś alternatywę.

Na czym polega trudność w dostosowaniu tego do całego zapytania? Może warto napisać coś więcej o tym dużym zapytaniu?

Przerabiałem podobne rozwiązanie, nie przejdzie. Uwierz, nie ma sensu żebym to tłumaczył . Poza tym unikam podzapytań jak ognia.