Solved

Assistance on A Difficult MySQL Query

Posted on 2008-06-13
10
260 Views
Last Modified: 2010-08-05
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.
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;

Open in new window

0
Comment
Question by:tjcarr
  • 5
  • 3
  • 2
10 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21784601
please try this:
select 
   b1.MACHINE_NAME, 
   b1.BACKUP_NAME,
   MAX(case when start_time = '2008-06-12' THEN b1.REMARKS END)as "Result Day -1",
   MAX(case when start_time = '2008-06-11' THEN b1.REMARKS END)as "Result Day -2",
   MAX(case when start_time = '2008-06-10' THEN b1.REMARKS END)as "Result Day -3",
from LAST_WEEK_BACKUP b1 
GROUP BY b1.MACHINE_NAME, b1.BACKUP_NAME
order by  b1.MACHINE_NAME, b1.BACKUP_NAME;

Open in new window

0
 

Author Comment

by:tjcarr
ID: 21785682
That seems to work beautifully!  Thank you.

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?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21786346
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)
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;

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21786352
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...
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21786666
>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...
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 51

Expert Comment

by:Mark Wills
ID: 21787047
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",
0
 

Author Closing Comment

by:tjcarr
ID: 31467144
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.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21790514
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...
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;

Open in new window

0
 

Author Comment

by:tjcarr
ID: 21793721
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.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21794342
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,

0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Query Syntax Error 9 33
ms sql stored procedure 22 77
Install MySQL 5.6 and PHP on Centos Linux 6 51
SQL query 4 29
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now