CODEWITHSUNDEEP
sqlsql-serverdatedatetime
Amin Jebeli
Amin Jebeli

Reputation: 21

Filtering date With SQL

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

Upvotes: 0

Views: 101

Answers (2)

Zhorov
Zhorov

Reputation: 30023

Answer:

If I understand you correctly and you want to select only the customers IDs, a statement using GROUP BY with 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

Update:

It seems, that you need the orders information, not the customers IDs:

SELECT *
FROM Orders o
JOIN Products p ON o.order_id = p.product_id
WHERE EXISTS (
   SELECT 1
   FROM Orders
   WHERE customer_id = o.customer_id
   GROUP BY customer_id
   HAVING DATEDIFF(day, MIN(date), MAX(date)) = 30
)

Upvotes: 2

Serg
Serg

Reputation: 22811

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;

Upvotes: 2

Related Questions