Filtering date With SQL-CodePudding

I have table like this:

customer_id    date    order_id
12           1/11/2021     2
12           22/11/2021    3
12           31/11/2021    5
42           1/11/2021     2
42           15/11/2021    2
42           31/11/2021    2
43           22/11/2021    1
43           25/11/2021    2

Yemen I want to select only the customer_id that are 30 days between their first and last purchase, Then make a join with the product table, which means something like this:

customer_id    date    order_id   Product_name
12           1/11/2021     2         apple
12           22/11/2021    3         car
12           31/11/2021    5         orange
42           1/11/2021     2         apple
42           15/11/2021    2         apple
42           31/11/2021    2         apple

for example:

select customer_id, date, order_id, product_name
left join product on order_id = product_id
where customer_id.max(date) - customer_id.min(date) = 30

CodePudding user response：

Select original rows matching the criteria

select customer_id, date, order_id
from 
    (select customer_id, date, order_id
     , max(date) over (partition by customer_id) dmax
     , min(date) over (partition by customer_id) dmin
     from myTable
    ) t
where date in (dmax, dmin) and datediff(day, dmin, dmax) = 30

and join the output and Products on order_id = product_id

select s.*, p.product_name
from (
    select customer_id, date, order_id
    from 
        (select customer_id, date, order_id
         , max(date) over (partition by customer_id) dmax
         , min(date) over (partition by customer_id) dmin
         from myTable
        ) t
    where date in (dmax, dmin) and datediff(day, dmin, dmax) = 30  
) s
join Product p on s.order_id = p.product_id;

CodePudding user response：

If I understand you correctly and you want to select only the customer's id (not the rows with min and max dates for each customer), a statement using GROUP BY, HAVING and DATEDIFF() is an option:

SELECT customer_id
FROM (VALUES
   (12, CONVERT(date, '20211101'), 2),
   (12, CONVERT(date, '20211122'), 3),
   (12, CONVERT(date, '20211130'), 5),
   (42, CONVERT(date, '20211101'), 2),
   (42, CONVERT(date, '20211231'), 2),
   (43, CONVERT(date, '20211122'), 1)
) t (customer_id, date, order_id)
GROUP BY customer_id
HAVING DATEDIFF(day, MIN(date), MAX(date)) <= 30

Result:

customer_id
12
43