December 5, 2014 at 4:12 am
I have a table (named Data) with the following structure:
Server, Database, File, Type, Size, Modified
which stores sizing information about the databases in our estate for capacity planning. I'm trying to select the size 1 month ago (to report growth) and I'm using:
SELECT
ROW_NUMBER() OVER(PARTITION BY Server, Database ORDER BY Modified DESC) AS rn,
Server,
Database,
SUM(Size)
FROM
Data
WHERE
MODIFIED <= DATEADD(month, -1, CURRENT_TIMESTAMP)
GROUP BY
Server,
Database
I can set this up as a CTE, select where rn=1 which gives me the most recently modified file for each server and database. However the most recently modified file could be ROWS or LOG which gives invalid sizing information. So if my PARTITION includes "Type" I can once again select where rn=1 but I now need to SUM() again. In addition, as this data is being used further down the query I'm having to create another CTE! This results in:
;WITH [Subtotal] AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY Server, Database, Type ORDER BY Modified DESC) AS rn,
Server,
Database,
SUM(Size) AS size
FROM
Data
WHERE
MODIFIED <= DATEADD(month, -1, CURRENT_TIMESTAMP)
GROUP BY
Server,
Database,
Type
), [Total] AS
(
SELECT
Server,
Database,
SUM(size) AS size
FROM
Subtotal
WHERE
rn = 1
GROUP BY
Server,
Database
)
SELECT ...
I'm looking for a way to:
- find the most recent entry for each file type by server/database
- sum the sizes from the above
- return a table containing this data
without
- numerous cascaded CTEs
The code is an extract so hopefully I haven't confused the question but hopefully it'll make sense.
Thanks!
December 5, 2014 at 6:14 am
Can you post up a sample data script please Pete? I can't see why you should have to aggregate twice. Cheers.
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply