Fetch latest 3 months from records-CodePudding

I need to fetch the latest 3 months records which are available in the table.

Example 1

 id name date
 1  A    2022-04-05
 2  B    2022-04-05
 3  A    2022-03-25
 4  B    2022-03-24
 5  A    2022-02-05
 6  B    2022-02-01
 7  A    2022-01-01
 8  B    2022-01-01

From this table I need to get the latest 3 month records of April,March and February

Example 2

 id name date
 1  A    2022-03-05
 2  B    2022-03-05
 3  A    2022-01-25
 4  B    2022-01-24
 5  A    2021-12-05
 6  B    2021-12-01
 7  A    2021-11-01
 8  B    2021-11-01

From this table I need to get the latest 3 months records of March, January, December.

CodePudding user response：

Use DENSE_RANK along with CONVERT to consider only the year-month of each record:

WITH cte AS (
    SELECT *, DENSE_RANK() OVER (ORDER BY CONVERT(varchar(7), date, 120) DESC) dr
    FROM yourTable
)

SELECT id, name, date
FROM cte
WHERE dr <= 3
ORDER BY date DESC;

CodePudding user response：

First we need to convert the mentioned date to the YYYY-MM format so that we can remove the day from the date column. Then we need to apply the ranking logic and retrieve the last 3 months of data. So finally the query looks like.

Example:

drop table if exists #temp1; 

select 1 as ID ,'A' as Name,'2022-04-05' as date 
into #temp1   union all
select 2,'B','2022-04-05' union all
select 3,'A','2022-03-25' union all
select 4,'B','2022-03-24' union all
select 5,'A','2022-02-05' union all
select 6,'B','2022-02-01' union all
select 7,'A','2022-01-01' union all
select 8,'B','2022-01-01'  
 
;WITH FinalOutput AS (
SELECT *, DENSE_RANK() OVER (ORDER BY CONVERT(varchar(7), date, 120) desc) ranks FROM #temp1
)

SELECT id, name, date FROM FinalOutput WHERE ranks <= 3;

drop table if exists #temp;

select 1 as ID ,'A' as Name,'2022-03-05' as date 
into #temp union all
select 2,'B','2022-03-05' union all
select 3,'A','2022-01-25' union all
select 4,'B','2022-01-24' union all
select 5,'A','2021-12-05' union all
select 6,'B','2021-12-01' union all
select 7,'A','2021-11-01' union all
select 8,'B','2021-11-01' 
 
;WITH FinalOutput AS (
SELECT *, DENSE_RANK() OVER (ORDER BY CONVERT(varchar(7), date, 120) desc) ranks
FROM #temp
) 

SELECT id, name, date FROM FinalOutput WHERE ranks <= 3