Report Writer - SQL query failing when using 'ranks'

[sachsj]

This query shows me the date/time when the VPN user count reaches its max per day.

I don't have any issues when using this query in Management Studio, but when creating a report using SQL it says it is an invalid query.  

 I removed 'ranks' from the query and it works, but provides all data not just the top count.   Maybe because NPM doesn't recognize the Status_Rank column

Any ideas? 

WITH ranks AS
(
SELECT [Status]
, [DateTime]
, RANK() OVER (PARTITION BY DATEADD(DAY,0, DATEDIFF(DAY,0, [DateTime])) ORDER BY [Status] DESC) status_rank
FROM [SolarWindsOrion].dbo.CustomPollerStatistics_Detail 
WHERE CustomPollerAssignmentID = '98C0CC90-B014-4721-A84A-027A2A064527'
)
SELECT *
FROM ranks WHERE status_rank = 1

Results

Status    DateTime                                status_rank
2288      2020-06-16 12:02:37.837            1
2284      2020-06-17 14:35:21.147            1
2297      2020-06-18 09:35:21.927            1

[sachsj · Accepted solution]

One of our SQL guys helped me sort it out.  

SELECT [Status], [DateTime]
from dbo.CustomPollerStatistics_Detail tm
join (
select CONVERT(Date,[DateTime]) as [Date]
, max([Status]) as [MaxUsers]
from dbo.CustomPollerStatistics_Detail tm
where tm.CustomPollerAssignmentID = '98C0CC90-B014-4721-A84A-027A2A064527'
--and CONVERT(Date,[DateTime]) = dateadd(day,datediff(day,1,GETDATE()),0)
group by CONVERT(Date,[DateTime])
) tm2 ON (tm2.[Date] = CONVERT(Date,[DateTime]) AND tm2.MaxUsers = tm.[Status])

The OID I used to create the poller is   1.3.6.1.4.1.9.9.392.1.3.3

Status        DateTime
2288         2020-06-16 18:02:37.837
2284         2020-06-17 20:35:21.147
2297         2020-06-18 15:35:21.927
2210         2020-06-19 13:55:37.790