Rank the dates in a table for each month-CodePudding

I need to find the last three distinct loaddates for each month in various tables for reporting purposes. Example: If I have data from 2021 February to today: I need the three loaddates of Feb 2021, March 2021 and so on till. Dec 2022

So far, I'm able to create the below query in SQL Server which gives me the result for a particular month that I pass in the where condition.

SELECT ROW_NUMBER() OVER (ORDER BY loaddate desc) AS myrank, loaddate
    FROM <tablename>
    where year(loaddate) = 2022 and month(loaddate) = 6
    group by loaddate

It gives me:

myrank  loaddate
1       2022-08-29 00:00:00.000
2       2022-08-25 00:00:00.000
3       2022-08-18 00:00:00.000
4       2022-08-17 00:00:00.000
5       2022-08-11 00:00:00.000

From this I can easily select the top three dates with the below query:

SELECT myrank, loaddate
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY loaddate desc) AS myrank, loaddate
    FROM <tablename>
    where year(loaddate) = 2022 and month(loaddate) = 6
    group by loaddate
) as daterank
WHERE daterank.myrank <= 3

which outputs:

rank    loaddate
1       2022-08-29 00:00:00.000
2       2022-08-25 00:00:00.000
3       2022-08-18 00:00:00.000

But this is only for one month. I'm manually passing the month number in the where condition. How to make this ranking query give me the the last 3 distinct loaddates for each month of data that exists in the table?

And also, how to do I run such a generic query on list of 400 tables instead of changing the tablename manually for each table in the list?

CodePudding user response：

You just add the PARTITION BY clause to ROW_NUMBER() and partition by month (and year since your data might cross a year boundary).

WITH cte AS (
    SELECT *
        , ROW_NUMBER() OVER (PARTITION BY DATEPART(year, loaddate), DATEPART(month, loaddate) ORDER BY loaddate desc) AS myrank
    FROM #MyTable
)
SELECT *
FROM cte
WHERE myrank <= 3
ORDER BY loaddate;

Note: The CTE is doing the same thing as your sub-query - don't let that confuse you - I just prefer it for neatness.

CodePudding user response：

If I understand you request I think this would help you:

  SELECT myrank, loaddate , monthofyear
    FROM
    (
      SELECT ROW_NUMBER() OVER (partition by  month(loaddate)  ORDER BY loaddate 
       desc) 
        AS myrank, loaddate , month(loaddate) as monthofyear
       FROM Db15.dbo.mytable
      group by loaddate
     ) as daterank
     WHERE daterank.myrank <= 3