tjcarr
asked on
Assistance on A Difficult MySQL Query
Hello. I've got a heck of a SQL question. I have a SQL table that contains a list backup job successes and failures for each day - there is one row per job result. I am trying to create a SQL query (I'm running MySQL 5) that will show what the status of each backup job for the past 7 days on one row. This needs to be a dynamic result so that when I talk about "Today's Date" below, it refers to today, and then the next 6 columns show yesterday's date, the day before, the day before that, etc. Also, if there is no result for a certain date, that cell needs to be blank.
Here's how my source table (Last_Week_Backup) is laid out:
Machine Name Backup Job Name Backup Date Result
Machine1 BUJob1 2008-06-12 Success
Machine1 BUJob2 2008-06-12 Failure
Machine1 BUJob1 2008-06-11 Success
Machine1 BUJob1 2008-06-10 Success
Machine1 BUJob2 2008-06-10 Success
Machine2 BUJob1 2008-06-12 Success
Machine2 BUJob1 2008-06-11 Failure
Machine2 BUJob1 2008-06-10 Failure
I'm trying to get a result query that looks like this:
Machine_Name Backup_Name Result Day -1 Result Day-2 Result Day-3 <etc>
Machine1 BUJob1 Success Success Success
Machine1 BUJob2 Failure <Blank> Success
Machine2 BUJob1 Success Failure Failure
I've gotten close (see the code snippet) but that query doesn't return any rows if I'm missing data for any day that I'm trying to grab.
Here's how my source table (Last_Week_Backup) is laid out:
Machine Name Backup Job Name Backup Date Result
Machine1 BUJob1 2008-06-12 Success
Machine1 BUJob2 2008-06-12 Failure
Machine1 BUJob1 2008-06-11 Success
Machine1 BUJob1 2008-06-10 Success
Machine1 BUJob2 2008-06-10 Success
Machine2 BUJob1 2008-06-12 Success
Machine2 BUJob1 2008-06-11 Failure
Machine2 BUJob1 2008-06-10 Failure
I'm trying to get a result query that looks like this:
Machine_Name Backup_Name Result Day -1 Result Day-2 Result Day-3 <etc>
Machine1 BUJob1 Success Success Success
Machine1 BUJob2 Failure <Blank> Success
Machine2 BUJob1 Success Failure Failure
I've gotten close (see the code snippet) but that query doesn't return any rows if I'm missing data for any day that I'm trying to grab.
select
b1.MACHINE_NAME,
b1.BACKUP_NAME,
b1.REMARKS as "Result Day -1",
b2.REMARKS as "Result Day -2",
b3.REMARKS as "Result Day -3",
from
LAST_WEEK_BACKUP b1
Left outer Join
LAST_WEEK_BACKUP b2
on
(b1.MACHINE_NAME = b2.MACHINE_NAME and b1.BACKUP_NAME = b2.BACKUP_NAME)
Left outer Join
LAST_WEEK_BACKUP b3
on
(b1.MACHINE_NAME = b3.MACHINE_NAME and b1.BACKUP_NAME = b3.BACKUP_NAME)
where
b1.start_time = '2008-06-10' and
b2.start_time = '2008-06-09' and
b3.start_time = '2008-06-08'
order by
b1.MACHINE_NAME,
b2.BACKUP_NAME;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OK, because it is a table for backups and volumes shouldn't be too onerous, then, could use in_line queries:
might need to change the date calc to be: date_add(max(b.start_time) , - interval 1 day)
might need to change the date calc to be: date_add(max(b.start_time)
select b.MACHINE_NAME, b.BACKUP_NAME, max(b.start_time) as most_recent,
(select top 1 d1.REMARKS from last_week_backup d1 where d1.machine_name = b.machine_name and d1.backup_name = b.backup_name and d1.start_time = max(b.start_time))as "Results Day - 1",
(select top 1 d1.REMARKS from last_week_backup d1 where d1.machine_name = b.machine_name and d1.backup_name = b.backup_name and d1.start_time = max(b.start_time)-1)as "Results Day - 2",
(select top 1 d1.REMARKS from last_week_backup d1 where d1.machine_name = b.machine_name and d1.backup_name = b.backup_name and d1.start_time = max(b.start_time)-2)as "Results Day - 3"
from LAST_WEEK_BACKUP b
GROUP BY b.MACHINE_NAME, b.BACKUP_NAME
order by b.MACHINE_NAME, b.BACKUP_NAME;
Oh, and if date time, then will need to look at just the date part of start_time, and also forgot an order by d1.start_time desc within the in_line queries...
>mark_wills: the TOP syntax is MS SQL Server, won't work in MySQL :)
I am not sure how I would do that in MySQL, actually...
I am not sure how I would do that in MySQL, actually...
yeah, put "limit 1" at the end of the sub-query not "top 1" straight after the select - fall into that every time...
(select d1.REMARKS from last_week_backup d1 where d1.machine_name = b.machine_name and d1.backup_name = b.backup_name and d1.start_time = max(b.start_time) order by d.start_time desc limit 1)as "Results Day - 1",
(select d1.REMARKS from last_week_backup d1 where d1.machine_name = b.machine_name and d1.backup_name = b.backup_name and d1.start_time = max(b.start_time) order by d.start_time desc limit 1)as "Results Day - 1",
ASKER
Angellll - Thanks. This works for me; I'll run it against a view that only shows the latest entry for each backup job per day.
Hi tjcarr,
No arguments abouts points or anything, would like to know if you tried my query... Here it is again, with the limit and casting start_time just as date to remove time comppnents (not sure if it was necessary, but the column name suggests it is) if you have time, would like some feedback...
No arguments abouts points or anything, would like to know if you tried my query... Here it is again, with the limit and casting start_time just as date to remove time comppnents (not sure if it was necessary, but the column name suggests it is) if you have time, would like some feedback...
select b.MACHINE_NAME, b.BACKUP_NAME, max(b.start_time) as most_recent,
(select d1.REMARKS from last_week_backup d1 where d1.machine_name = b.machine_name and d1.backup_name = b.backup_name and date(d1.start_time) = (max(date(b.start_time))) order by d.start_time desc limit 1)as "Results Day - 1",
(select d1.REMARKS from last_week_backup d1 where d1.machine_name = b.machine_name and d1.backup_name = b.backup_name and date(d1.start_time) = (max(date(b.start_time))-1) order by d.start_time desc limit 1)as "Results Day - 2",
(select d1.REMARKS from last_week_backup d1 where d1.machine_name = b.machine_name and d1.backup_name = b.backup_name and date(d1.start_time) = (max(date(b.start_time))-2) order by d.start_time desc limit 1)as "Results Day - 3"
from LAST_WEEK_BACKUP b
GROUP BY b.MACHINE_NAME, b.BACKUP_NAME
ORDER BY b.MACHINE_NAME, b.BACKUP_NAME;
ASKER
mark_wills:
Thanks for your response and apologies for not responding back to you faster. Unfortunately, the query you provided actually only showed "nulls" for all the "results day-2" and "results day-3" columns. Also, that query took about 3 times as long as angelll's response.
Thanks for your response and apologies for not responding back to you faster. Unfortunately, the query you provided actually only showed "nulls" for all the "results day-2" and "results day-3" columns. Also, that query took about 3 times as long as angelll's response.
Thanks for giving it a run. Kind of expected it to be slower, wasn't overly concerned considering it was a table of backup data - think I mentioned that from the getgo...
There will be an error somewhere with the datetime casting or, subtracting 1 from day - should have used add_date - just being lazy.
Just wanted to confirm some of the syntax, which you were able to do for me. Thanks again for running it...
Cheers,
There will be an error somewhere with the datetime casting or, subtracting 1 from day - should have used add_date - just being lazy.
Just wanted to confirm some of the syntax, which you were able to do for me. Thanks again for running it...
Cheers,
ASKER
One question about this, though. If I have multiple messages for a machine name / backup job combination in one day, is there a way to just select the last one for that day?