Question 221405 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL comparing each rows date with the past year

I have a database with order_date, product code, and customer like the example here:

Index code customer order_date
1 CCC C 2018-01-01
2 AAA A 2020-01-01
3 CCC C 2021-02-02
4 CCC C 2021-03-03
5 DDD A 2022-04-04
6 FFF F 2023-05-05
7 FFF F 2023-08-08

I need a query to list all orders, where in the last year before the order date there was no order with the same product code and customer. In this example the desired output would be:

Index code customer order_date
1 CCC C 2018-01-01
2 AAA A 2020-01-01
3 CCC C 2021-02-02
5 DDD A 2022-04-04
6 FFF F 2023-05-05

If you look at row with index no. 4 and 7, you can see that in the past year there is another row with the same code and customer.

How is this achievable in mysql?

Tags:

2

Answers


  1. 0

    You can try query like this:

    select *,YEAR(order_date) as order_year from YOUR_TABLE group by code,customer,order_year;

    Login or Signup to reply.
  2. 0

    Try this

    select * 
from (
  select *
    ,lag(order_date)over(partition by code,customer order by order_date) pdt
  from orders
  ) x
where pdt is null or order_date>DATE_add(pdt,INTERVAL 1 YEAR)
order by code,customer,order_date
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search