November 7, 2008 at 1:05 pm
I would like to pull some datetime values grouped by database and find the minute interval between the most recent two backups
Trick is that I'd really really like to do it in 1 query that gathers some other SLA information, so UDF's are game, etc.
here's what I'm trying to do in pseudocode
select dbname
from --a bunch of already ridiculous subqueries and table valued udfs
where max(last_job_run_time_from_job_log) > SLA_job_run_time
having
max(last_job_run_time_from_job_log) - next to last backup time < SLA_job_run_interval
Getting that next to last time is the trick for me right now.
Any query you have out where you get the max and next max should be sufficient to point me in the right direction.
Thank you,
~BOT
Craig Outcalt
November 7, 2008 at 1:37 pm
with temp1 as (
select dbname, last_job_run_time_from_job_log, row_number() over (partition by dbname order by last_job_run_time_from_job_log desc) as rn
from --a bunch of already ridiculous subqueries and table valued udfs
where max(last_job_run_time_from_job_log) > SLA_job_run_time
)
select a.dbname
from temp1 a inner join temp1 b
on a.orderno = b.orderno and a.rn=1 and b.rn =2
where a.last_job_run_time_from_job_log - b.last_job_run_time_from_job_log > SLA_job_run_interval
November 7, 2008 at 2:01 pm
Brilliant!!
works like a charm.
~BOT
Craig Outcalt
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply