Automatic query of data in recent months

Question

Hello!

I have a large data table, which is updated every day. It has many columns, one of which is Update_ Date, which will display the date of data update.

I can use the where criteria to filter the data of the last three months, but this is a bit troublesome. It needs to be entered manually, and sometimes I need to view the data of the last six or eight months, which is not fixed.

Is there a convenient query that can realize this function?

Thanks.

Answer 1

Hi @potQ-8127

You can try this query.

create table test(id int,date datetime);
insert into test values
(1,'2022-08-24'),
(2,'2022-09-08'),
(3,'2022-09-30'),
(4,'2022-10-18'),
(5,'2022-11-05'),
(6,'2022-12-09'),
(7,'2022-12-28'),
(8,'2023-01-11'),
(9,'2023-01-23'),
(10,'2023-02-04'),
(11,'2023-03-07');


;with CTE as(
  select *,dense_rank()over(order by eomonth(date) desc) as num from test)
select * from CTE where num < 4;--You can modify here to change the month of the query

The eomonth function returns the last day of the month containing the specified date.

Output:

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

It needs to be entered manually

Entered where?

I need to view the data of the last six or eight months, which is not fixed.

Not fix is a problem.

For a fix date range you can use DATEADD (Transact-SQL) to "add" (substract) e.g. six month to the current date.