Help with COUNT function in the query
-
Here is the query:
cteUsage30 AS (
SELECT keySystem,
--dtActivitesDuring,
ROW_NUMBER() OVER(ORDER BY id) AS [rowNumber1]
FROM [DB_admin1].[debug].[tblActivitiesOnDate]
WHERE intTotal != 0 AND
dtActivitesDuring BETWEEN DATEADD(DAY, -30, SYSUTCDATETIME()) AND SYSUTCDATETIME()
GROUP BY id, keySystem
)
SELECT
--[Update],
CASE WHEN [Renewal Date] > SYSUTCDATETIME() THEN 'Yes' ELSE 'No' END AS [Renewed],
--cteUsage30.Usage30
MAX(rowNumber1) AS [30 Days Usage]
FROM cteUsageReport
INNER JOIN cteUsage30 ON
cteUsageReport.System = cteUsage30.keySystem
The Result I get:
Problem: The count(30 day usage) for the first system 10003 is 27 which is correct. The rest of the system its not counting correctly. They are all suppose to be less then 30.
If you need anymore information please let me know and any help will be much appreciated.
Thank you
-
I think you should include a PARTITION BY in your ROW_NUMBER() function.
Something like ROW_NUMBER() OVER(PARTITION BY keySystem ORDER BY id) AS [rowNumber1]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Thats exactly what i needed. Thank you so much! 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply