Select data with the latest table partition that actually holds data Version: SQL Server 2016 

So, I need to be able to select the data within the most recent table partition. I can do this by using the below script but the latest partition doesn't contain any data. What I need is a way to select the latest partition within the table that actually contains data. Does anyone have any ideas. 

There's a new partition created daily, but data doesn't always get inserted within the day. There's thousands of partitions that span many years so simply scrolling through each partition isn't ideal at all. 

This is how I'm currently selecting the most recent partition (or should I say the max partition): 

USE DBName
DECLARE @PartitionValue int
set @PartitionValue = (select max(boundary_id) from sys.partition_range_values)
SELECT * FROM schema.tablename
WHERE $PARTITION.partition_function_name(expression) = @PartitionValue
Try something like this (replace schema.tablename with your object name).  It worked for me in my limited testing.

DECLARE @PartitionValue INT

SET @PartitionValue = (
        SELECT TOP 1 partition_number
        FROM sys.dm_db_partition_stats
        WHERE object_id = object_id('schema.table_name')
            AND row_count &gt; 0
        ORDER BY partition_number DESC
        )

SELECT count(*)
FROM schema.table_name
WHERE $PARTITION.pf_schema_table_name_data(expression) = @PartitionValue