CODEWITHSUNDEEP
mysql
Akshay Patwa
Akshay Patwa

Reputation: 13

getting date difference for each customer for particular period in SQL

I have two tables let say it is customers and repair_orders.

question related columns on table :

customers table :

repair_orders table :

My question is I want difference of "max closed (date)" and "second most max closed(date)" for each id from customers table when i select customers for particular time of period.

Currently I'm able to get id and and max closed (date) and related Id but If possible I want all three "id , max closed (date) and second_max closed (date) "in one result.

Query I used to get id and max closed (date) is :

SELECT c.id, max(r.closed)
FROM customers c
INNER JOIN  repair_orders  r
ON c.id = r.customer_id
WHERE   c.last_seen >= '2017-01-01'
AND c.last_seen < '2017-02-01' group by c.id ;

Upvotes: 0

Views: 54

Answers (1)

GoodWasHere
GoodWasHere

Reputation: 138

select r.id, o1.id, o2.id
from (
    select c.id,
        max(r1.closed) as date1,
        max(r2.closed) as date2
    from customers c
        join repair_orders r1 on c.id = r1.customer_id
        join repair_orders r2 on c.id = r2.customer_id and r1.closed > r2.closed
    group by 1
) r
    join repair_orders o1 on r.id = o1.customer_id and r.date1 = r1.closed
    join repair_orders o2 on r.id = o2.customer_id and r.date2 = r2.closed

but it too slow without index on closed

Upvotes: 0

Related Questions