Getting last 4 months data from given date column some months data is midding-CodePudding

I have below data

Record_date   ID
28-feb-2022    xyz
31-Jan-2022    ABC
30-nov-2022    jkl
31-oct-2022    dcs

I want to get last 3 months data from given date column. We don't have to consider the missing month.

Output should be:
Record_date   ID
28-feb-2022    xyz
31-Jan-2022    ABC
30-nov-2022    jkl

In the last 3 months Dec is missing but we have to ignore it as the data is not available. Tried many things but not working.

Any suggestions?

CodePudding user response：

Assuming you are using Oracle then you can use Oralce ADD_MONTHS function and filter the data.

--- untested -- Assumption Record_date is a date column

SELECT * FROM table1 
where Record_date > ADD_MONTHS(SYSDATE, -3)

CodePudding user response：

To get the data for the three months that are latest in the table, you can use:

SELECT record_date,
       id
FROM   (
  SELECT t.*,
         DENSE_RANK() OVER (ORDER BY TRUNC(Record_date, 'MM') DESC) AS rnk
  FROM   table_name t
)
WHERE  rnk <= 3;

Which, for the sample data:

CREATE TABLE table_name (Record_date, ID) AS
SELECT DATE '2022-02-28', 'xyz' FROM DUAL UNION ALL
SELECT DATE '2022-01-31', 'ABC' FROM DUAL UNION ALL
SELECT DATE '2022-11-30', 'jkl' FROM DUAL UNION ALL
SELECT DATE '2022-10-31', 'dcs' FROM DUAL;

Outputs:

RECORD_DATE ID

2022-11-30 00:00:00 jkl

2022-10-31 00:00:00 dcs

2022-02-28 00:00:00 xyz

RECORD_DATE	ID
2022-11-30 00:00:00	jkl
2022-10-31 00:00:00	dcs
2022-02-28 00:00:00	xyz

db<>fiddle here