Find the first record for each month

Question

Post reply

Find the first record for each month

N.D

Ten Centuries

Points: 1132
More actions
July 29, 2013 at 8:31 am

#281137

I'm trying to pull out the file size and backup size for each database using the msdb backup history table. I want to get the 1st record for each month (due to the vagaries of the backup system, there may not always be a backup on the 1st - or there may be more than one !)
This is what I have so far (I think I want to use partition by, to get rank #1 of the 1st record and go from there, but am unsure how to express that I want to partition by month ??? )
Any suggestions appreciated !!! (especially if I'm on completely the wrong track :hehe: )
select backup_start_date as [Date], ((backupfile.backup_size/1024)/1024) as [Database Size],
((backupfile.file_size/1024)/1024) as [File Size],
RANK() OVER
(PARTITION BY backup_start_date ORDER BY backup_start_date) AS Rank
from msdb..backupfile, msdb..backupset
where backupset.database_name = 'mydatabasename' and file_type = 'D'
and backupfile.backup_size > 0
and backupfile.backup_set_id = backupset.backup_set_id
order by backup_start_date

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

select

backup_start_date as [Date],

((f.backup_size/1024)/1024) as [Database Size],

((f.file_size/1024)/1024) as [File Size],

RANK() OVER (PARTITION BY backup_start_date ORDER BY backup_start_date) AS Rank,

rn = ROW_NUMBER() OVER (PARTITION BY MONTH(backup_start_date) ORDER BY backup_start_date DESC)

from msdb..backupfile f

INNER JOIN msdb..backupset s

ON f.backup_set_id = s.backup_set_id

where s.database_name = 'mydatabasename'

and file_type = 'D'

and f.backup_size > 0

order by backup_start_date

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

N.D Ten Centuries Points: 1132 More actions · Answer 2

I am not sure how the above worked, but it returned something !

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

There's an extra column in your output. You can filter on it, provided you set up the query as an inner query or CTE of an outer query wielding the filter.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

N.D Ten Centuries Points: 1132 More actions · Answer 4

Ok, I popped the results into a temporary table. It's only giving the results (rn = 1) for either the first or the last year (depending on whether I remove the desk or not) ie. a total of 12 results

This is a little like I got stuck before, I want something like month(date)&year(date) to partition on :ermm:

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

N.D (7/29/2013)
Ok, I popped the results into a temporary table. It's only giving the results (rn = 1) for either the first or the last year (depending on whether I remove the desk or not) ie. a total of 12 results
This is a little like I got stuck before, I want something like month(date)&year(date) to partition on :ermm:

rn = ROW_NUMBER() OVER (PARTITION BY MONTH(backup_start_date), YEAR(backup_start_date) ORDER BY backup_start_date DESC)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

N.D Ten Centuries Points: 1132 More actions · Answer 6

N.D

Ten Centuries

Points: 1132

July 30, 2013 at 2:13 am

#1636871

That's brilliant !

Thanks very much :w00t: