Thanks for the in put!

Unfortunately, I need to separate the values so I can do a compare on
them so having them show in the same column doesn't work for me. I am
doing a datediff on them to show interval amounts.

-----Original Message-----
From: Edwards Ed
[mailto:mssqldba-ezmlmshield-x13424535.[Email address protected]
Sent: Monday, July 30, 2007 1:09 PM
To: LazyDBA Discussion
Subject: RE: Getting the 2nd to max date for a table

Below is some more code from "the smart-aleck developer"(Oh my! Phew!)!
hehe.


use msdb
go
SELECT database_name, backup_finish_date
FROM dbo.backupset b
WHERE backup_set_uuid IN
(SELECT TOP 3 backup_set_uuid
FROM dbo.backupset
WHERE database_name = b.database_name
ORDER BY backup_finish_date DESC)
ORDER BY database_name, backup_finish_date DESC
Go









Please take a few minutes to provide feedback on the quality of service
you received from our staff. The Department of Education values your
feedback as a customer. Commissioner of Education Jeanine Blomberg is
committed to continuously assessing and improving the level and quality
of services provided to you.Simply use the link below. Thank you in
advance for completing the survey.


http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]




-----Original Message-----
From: Mann Steve L.
[mailto:mssqldba-ezmlmshield-x52337065.[Email address protected]
Sent: Monday, July 30, 2007 1:53 PM
To: LazyDBA Discussion
Subject: Getting the 2nd to max date for a table

Hello all,

I wanted to get the last three backups and compare the intervals
between them. I was able to do this query on the MSDB database, but it
seems like there should be a MUCH simpler, easier way to do this.
Anyone have any ideas?

Here's my query (that works currently):

SELECT bs1.database_name, max(bs1.backup_finish_date) as lastBU,
(SELECT max(bs2.backup_finish_date)
FROM backupset bs2
WHERE bs2.database_name=bs1.database_name and
bs2.backup_finish_date < (SELECT
max(bs3.backup_finish_Date)
FROM backupset bs3

WHERE
bs3.database_name=bs1.database_name)),
(SELECT max(bs2.backup_finish_date)
FROM backupset bs2
WHERE bs2.database_name=bs1.database_name and
bs2.backup_finish_date < (SELECT
max(bs3.backup_finish_Date)
FROM
backupset bs3
WHERE
bs3.database_name=bs1.database_name and

bs3.backup_finish_date <

(SELECT max(bs4.backup_finish_Date)
FROM backupset bs4
WHERE bs4.database_name=bs1.database_name)))
FROM backupset bs1
WHERE bs1.database_name='Impact_Reporting'
GROUP BY bs1.database_name

Thanks!

Steve


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

MS Sql Server LazyDBA home page