Query: 1 table 2 different dates

Dev Shed Forums Sponsor:

September 7th, 2012, 09:01 AM

elcake

Registered User

Join Date: Sep 2012

Posts: 1

Time spent in forums: 28 m 22 sec
Reputation 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

September 7th, 2012, 11:00 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 2,352

Time spent in forums: 1 Month 4 Days 9 h 55 m 58 sec
Reputation Power: 390

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

Viewing: Dev Shed Forums > Databases > MS SQL Development > Query: 1 table 2 different dates

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump