Hello,
thx for answering!
edit: I just noticed the topic said "Inner Join" - obviously im talking about a SELF join from table state_history onto itself. Sorry bout that!
@Peter:
Yes - the structure you describe is approximately the structure i want to transfer the data from the original structure to.
But the structure described above is set, i cant change anything about it (maybe i wasn't clear enough about that). Question is, is there a SELECT statement i can use on the first structure to get a result set that resembles the structure you mentioned?
@Rick:
I used one single servicecheck for testing, "12345" in my SELECT statement. My mistake here, as i used a String in the example provided above. Consider "Servicecheck" as an INT(11).
I will have another table "Servicechecks" where i select numerous servicechecks and join them via servicecheck_id to the state_history table. (I just omitted that to keep the question more simple)
The Basic idea with my SELECT is:
I have to start_date at CRITICAL HARD and end_date at the
next OK HARD (per servicecheck)
I select the datetime when the servicecheck went CRITICAL HARD
start.status="Critical" AND start.status_type="Hard"
Then i self join (servicecheckid=servicecheckid) it with all entries *after* that datetime
end.datetime>start.datetime
that are HARD OK
end.status="ok" AND end.status_type="hard"
By now, i have a start_date, and all possible end_dates afterwards - also those that "belong" to other start_dates.
So now, i choose the MIN of all these end_dates, because it is the nearest to my start_date
MIN(end.datetime) AS end_datetime,
GROUP BY start.datetime
(Keep in mind, there can be numerous entries of "UNKNOWN" / "WARNING" between first occurence of CRITICAL HARD and next OK HARD)
This actually works (i think), i tested it with ~20 specific servicechecks.
But the performance is quite bad (as Peter said, SQL is not good with date/times) and it ... feels kinda makeshift / forced.
Hope i could clarify!
Regards
Edited 2 time(s). Last edit at 02/04/2014 02:09AM by Pantheos Max.