[MySQL] Starttijd selecteren in plaats van eindtijd

[_wood_]

Zelf ben ik vrij nieuw met mysql en loop tegen het volgende probleempje aan:

De tabel Jobs.TripID omvat de te verrichten werkzaamheden onderverdeeld in de verschillende taken Jobs.JobID. De tabel Joblogs bevat de status van de Jobs.

Nu heb ik onderstaande query gemaakt:

SQL:

SELECT
    Jobs.TripID,
    Jobs.JobID,
    DATE_FORMAT(Jobs.SortTime, '%T'),
    DATE_FORMAT(DATE_ADD( JobLogs.LogTime ,INTERVAL JobLogs.LogTimeZone MINUTE), '%T'),
    MAX(JobLogs.LogCode)
FROM
    JobLogs, Jobs
WHERE 
    Jobs.JobID = JobLogs.JobID
AND
    ((DATE_FORMAT(Jobs.SortTime, '%Y-%m-%d')) = (DATE_FORMAT(NOW(), '%Y-%m-%d')))
GROUP BY
    Jobs.TripID, Jobs.JobID
ORDER BY
    Jobs.TripID, Jobs.JobID 

Output is zo iets als:

Trip	Job	Geplande tijd	Werkelijke tijd	Status (1000, is toegevoegd, 1500 is gestart, 2000 is afgerond)
1	1	10:00:00	10:30:00	2000
1	2	11:00:00	11:30:00	1500
1	3	12:00:00	12:30:00	1000

Zoals jullie waarschijnlijk al opvalt zal de kolom werkelijke tijd altijd de tijd bevatten van het afronden van de activiteit. Terwijl deze de tijd zal moeten bevatten van het starten van de activiteit. Zelf heb ik al diverse zaken geprobeerd, ik krijg het echter niet van de grond. Wellicht dat iemand mij kan helpen, bedankt

[ Voor 5% gewijzigd door NMe op 26-01-2011 15:18 ]

[alex3305]

Ik stel voor dat je eens gaat kijken naar de MySQL Reference manual. Daarin staan twee artikelen welke hierop van toepassing zijn:

Date calculations
Date and time functions

Daarmee zou je verder moeten kunnen .

[steffex]

SQL:

DATE_FORMAT(DATE_ADD( MAX(JobLogs.LogTime)

wat doe je hier?? hint: het begint met de letter 'M'...

[_wood_]

steffex schreef op woensdag 26 januari 2011 @ 14:40:

SQL:

DATE_FORMAT(DATE_ADD( MAX(JobLogs.LogTime)

wat doe je hier?? hint: het begint met de letter 'M'...

Bedankt! Meteen even aangepast. Hij selecteert echter nog niet de tijd die gelijk is aan 1500, maar pakt nu de tijd die gelijk is aan 1000.

@alex3305 Bedankt, de tijd wordt echter goed weergegeven. Hij selecteert alleen de tijd die gelijk is aan status 1000 ipv 1500.

[ Voor 20% gewijzigd door _wood_ op 26-01-2011 14:47 ]

[_wood_]

Op de volgende manier geeft hij de tijd weer die gelijk is aan status 1500. Dit is echter niet geheel de oplossing omdat er dan géén gebruik meer gemaakt kan worden van status codes tussen 1000 en 1500.

SQL:

SELECT
Jobs.TripID,
Jobs.JobID,
DATE_FORMAT(Jobs.SortTime, '%T'),
DATE_FORMAT(DATE_ADD( JobLogs.LogTime ,INTERVAL JobLogs.LogTimeZone MINUTE), '%T'),
MAX(JobLogs.LogCode)
FROM
JobLogs, Jobs
WHERE
Jobs.JobID = JobLogs.JobID
AND
((DATE_FORMAT(Jobs.SortTime, '%Y-%m-%d')) = (DATE_FORMAT(NOW(), '%Y-%m-%d')))
AND
cwReturnJobLogs.LogCode > '1499'
GROUP BY
Jobs.TripID, Jobs.JobID

[ Voor 10% gewijzigd door NMe op 26-01-2011 15:19 ]

[NMe]

Hoe post je code? / Hoe gebruik je de code tag?
*** Over topictitels in PRG - lezen voor topic openen!!! ***

Lees die topics even door, dan pas ik je topictitel even aan. Zorg jij dan even dat je de code-tag gebruikt zodat je SQL ook daadwerkelijk fatsoenlijk te lezen is?

Verder, inhoudelijk: Programming FAQ - SQL: Hoe werkt dat GROUP BY nu eigenlijk? Hint: DATE_FORMAT is géén aggregate function.

[steffex]

wat wil je nou precies bereiken? uit je laatste post maak ik weer op dat je elke status van elke job wilt hebben, met de daarbij behorende tijden...

[_wood_]

steffex schreef op woensdag 26 januari 2011 @ 15:05:
wat wil je nou precies bereiken? uit je laatste post maak ik weer op dat je elke status van elke job wilt hebben, met de daarbij behorende tijden...

Ik begrijp de verwarring. Mijn doel is om de hoogste status code weer te geven. Hierbij mag de werkelijke tijd echter niet boven de tijd uitkomen die geregistreerd is bij status code 1500.

Wellicht dat onderstaande het verduidelijkt:

(Om 10:30 is de job met id[1] afgerond, hier moet echter de tijd komen te staan wanneer er aan de Job begonnen is.)

Trip	Job	Geplande tijd	Werkelijke tijd	Status (1000, is toegevoegd, 1500 is gestart, 2000 is afgerond)
1	1	10:00:00	10:30:00	2000
1	2	11:00:00	11:30:00	1500
1	3	12:00:00	12:30:00	1000

@NME Bedankt!!!

[ Voor 50% gewijzigd door _wood_ op 26-01-2011 15:23 ]

[NMe]

_wood_ schreef op woensdag 26 januari 2011 @ 15:11:
[...]

@NME Bedankt!!!

offtopic:
Waarom gebruik je dan PHP-tags voor SQL-code? Daarmee had je alsnog alles in één kleur en dus lastig leesbaar. Heb het alsnog even voor je gefixt.

[_wood_]

Om het een en ander te verduidelijken heb ik de input van de query en de output zoals het moet zijn even op papier gezet.

Gegevens:

Jobs.TripID	Jobs.JobID	Jobs.SortTime
1	1	10:00:00
1	2	11:00:00
1	3	12:00:00

JobLogs.JobID	JobsLogs.SortTime	JobsLogs.LogCode
1	09:00:00	1000
1	10:15:00	1500
1	10:30:00	2000
2	09:00:00	1000
2	11:15:00	1500
3	09:00:00	1000

Resultaat:

Trip	Job	Geplande tijd	Werkelijke tijd	Status (1000, is toegevoegd, 1500 is gestart, 2000 is afgerond)
1	1	10:00:00	10:15:00	2000
1	2	11:00:00	11:15:00	1500
1	3	12:00:00	09:00:00	1000

Hopelijk kan iemand me helpen.

[Voutloos]

Voeg een subquery of join toe welke de tijd bij dezelfde Job met Code 1500 opzoekt.

Je kan je ook afvragen of geplande tijd, starttijd en eindtijd niet gewoon eigenschappen zijn van een job en je niet gewoon de hele log tabel de deur uit kan doen.

[_wood_]

Voutloos schreef op woensdag 26 januari 2011 @ 22:19:
Voeg een subquery of join toe welke de tijd bij dezelfde Job met Code 1500 opzoekt.

Je kan je ook afvragen of geplande tijd, starttijd en eindtijd niet gewoon eigenschappen zijn van een job en je niet gewoon de hele log tabel de deur uit kan doen.

Allereerst bedankt, de join heb ik nu toegevoegd:

SQL:

SELECT
Jobs.TripID,
Jobs.JobID,
DATE_FORMAT(Jobs.SortTime, '%T'),
DATE_FORMAT(DATE_ADD( JobLogs.LogTime ,INTERVAL JobLogs.LogTimeZone MINUTE), '%T'),
JobLogs.LogCode
FROM
Jobs
LEFT JOIN
JobLogs
ON
Jobs.JobID=JobLogs.JobID
WHERE
((DATE_FORMAT(Jobs.SortTime, '%Y-%m-%d')) = (DATE_FORMAT(NOW(), '%Y-%m-%d')))
ORDER BY
Jobs.TripID

Dit geeft als resultaat:

Trip	Job	Geplande tijd	Werkelijke tijd	Status (1000, is toegevoegd, 1500 is gestart, 2000 is afgerond)
1	1	10:00:00	09:00:00	1000
1	1	10:00:00	10:15:00	1500
1	1	10:00:00	10:30:00	2000
1	2	11:00:00	09:00:00	1000
1	2	11:00:00	11:15:00	1500
1	3	12:00:00	09:00:00	1000

Nu heb ik al enkele zaken met een subquery geprobeerd ik krijg het echter niet werkend Echter ik blijf botsen tussen de volgende 2 criteria:
- Kolom status moet hoogste code bevatten
- Kolom werkelijke tijd moet de tijd bevatten <= aan status code 1500

[_js_]

_wood_ schreef op donderdag 27 januari 2011 @ 15:40:
Voeg een subquery of join toe welke de tijd bij dezelfde Job met Code 1500 opzoekt.


Allereerst bedankt, de join heb ik nu toegevoegd:

Waar staat in jouw query het deel dat zoekt naar dezelfde job met code 1500? En heb je ondertussen al in de FAQ gelezen wat een JOIN precies is (of ergens een basiscursus SQL opgezocht)? Als je snapt wat het doet, dan kun je ook wat makkelijker die query schrijven, en hoef je niet telkens te gokken en dan te vragen of ie 't al doet.

[_wood_]

_js_ schreef op donderdag 27 januari 2011 @ 16:21:
[...]

Waar staat in jouw query het deel dat zoekt naar dezelfde job met code 1500? En heb je ondertussen al in de FAQ gelezen wat een JOIN precies is (of ergens een basiscursus SQL opgezocht)? Als je snapt wat het doet, dan kun je ook wat makkelijker die query schrijven, en hoef je niet telkens te gokken en dan te vragen of ie 't al doet.

De FAQ vanmiddag nog even rustig doorgenomen en eens rustig op papier zitten tekenen wat de query nu moet doen... Waarna de query is gelukt!

SQL:

SELECT A.TripID,
       A.JobID,
       A.City,
       DATE_FORMAT(A.SortTime, '%T'),
       DATE_FORMAT(DATE_ADD( MAX(C.LogTime) ,INTERVAL C.LogTimeZone MINUTE), '%T'),
       B.LogCode
  FROM Jobs A JOIN (SELECT JobID,
                             LogTime,
                             MAX(LogCode) LogCode
                        FROM JobLogs
                        GROUP BY JobID) B ON (B.JobID = A.JobID),
        JobLogs C
 WHERE 
    ((DATE_FORMAT(A.SortTime, '%Y-%m-%d')) = (DATE_FORMAT(NOW(), '%Y-%m-%d')))
 AND
    A.JobID = C.JobID
 AND
    C.LogCode <= '1500'
 GROUP BY
    A.TripID, A.JobID

Mocht iemand opmerkingen hebben, dan hoor ik het graag. Want of dit de beste/snelste manier voor deze query is, weet ik als beginneling ook niet Hij is nu in ieder geval redelijk traag

Stel dat ik nog een 2e JOIN toe wil voegen om bijvoorbeeld een naam aan een TripID toe te kennen, waar plaats ik deze dan, want als ik hem boven de eerste zet, dan wordt de query ontzettend langzaam

Bedankt

[ Voor 12% gewijzigd door _wood_ op 28-01-2011 19:48 ]