#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    1
    Rep Power
    0

    Query: 1 table 2 different dates


    Hi Everyone,

    Im trying to get a select query for a daily backup report. In this case a report for date=03.09.2012

    https://dl.dropbox.com/u/93299729/sql.png

    On the left side is my table with the tasks name, date of backup type of backup and the state which says if the backup did run successfully.
    These tasks are running everyday as incremental backup, and in between (mostly full once a week) there is a Full Backup.


    On the right side is the result that i'm trying to get with a query. I want a daily report list for the backups.

    The Last Full Backup column is easy:

    Code:
    select task, max(date) from table where type="Full" and state="OK" group by task
    This query will give me the last date of a succesfully executed Full Backup. But how can i add this column to the rest of the query?


    ty
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,378
    Rep Power
    391
    Code:
    select task,
           <otherColumns>,
           date 
      from t 
     where type = 'Full' 
       and state = 'OK'
      join (select task,
                   max(date) as maxDate
              from t
             where type = 'Full'
               and state = 'OK'
             group
                by task) dt
        on t.task = dt.task
       and t.date = dt.maxDate

IMN logo majestic logo threadwatch logo seochat tools logo