CODEWITHSUNDEEP
t-sqldatabase-partitioning
Mark C
Mark C

Reputation: 768

How do I get the max date value from each of my partitions

I have created a partition table which is partitioned based on years of a certain field 

e.g. partition 1 - Year 2011 |
     partition 2 - Year 2012 |
     partition 3 - Year 2013

How can i show the max date for each partition

e.g. partition 1 - 2011/12/15 |
     partition 2 - 2012/12/25 |
     partition 3 - 2013/12/16

Upvotes: 0

Views: 1392

Answers (3)

Ricardo
Ricardo

Reputation: 818

Let me try again. Now I think this query below will do the trick:

SELECT year(cast(rv.value as date)) _year,
      p.partition_number,
      max_date

FROM sys.partitions p
JOIN sys.indexes i 
      ON (p.object_id = i.object_id AND p.index_id = i.index_id)
JOIN sys.partition_schemes ps
      ON (ps.data_space_id = i.data_space_id)
JOIN sys.partition_functions f
      ON (f.function_id = ps.function_id)
LEFT JOIN sys.partition_range_values rv   
      ON (f.function_id = rv.function_id AND p.partition_number = rv.boundary_id)
JOIN sys.destination_data_spaces dds
      ON (dds.partition_scheme_id = ps.data_space_id
          AND dds.destination_id = p.partition_number)
JOIN sys.filegroups fg
      ON (dds.data_space_id = fg.data_space_id)
INNER JOIN (
    select year([partitioncol]) year, 
            max([partitioncol]) max_date 
    from TABLE1 group by year([partitioncol]) 
) table1
ON (table1.year=year(cast(rv.value as date)))
WHERE i.index_id < 2
      AND i.object_id = Object_Id('TABLE1')

With my test data (which I will explain how to set up below), I got these results:

_year   partition_number    max_date
2011    1                   2011-12-31 08:00:16.920
2012    2                   2012-12-31 08:00:13.397
2013    3                   2013-10-02 08:00:10.660

The key system table here is sys.partition_range_values. It gives us the range values for each partition that we join with the original agreggation query that returns the max dates per year.

To reproduce my results, follow the instructions in the link below (it's a post about creating partitioned tables). That will create and populate TABLE1.

http://www.mssqltips.com/sqlservertip/2888/how-to-partition-an-existing-sql-server-table/

Then run the query at the beggining of my post. I adapted it from a query presented by the link below:

http://www.sqlsuperfast.com/post/2011/02/22/T-SQL-Get-Partition-Details.aspx

Upvotes: 0

Ricardo
Ricardo

Reputation: 818

Just group the records by the year in the field you used to partition the table. More especifically, you can use this:

select year(my_date_field), max(my_date_field) 
from my_partitioned_table 
group by year(my_date_field)
order by year(my_date_field)

The order by above is not required, but gives you a nicely ordered result set.

Upvotes: 1

Kaf
Kaf

Reputation: 33839

Use max() function (Example).

Select max(y1) y1, max(y2) y2, max(y3) y3
From t

Upvotes: 0

Related Questions