Days Since Last Help Ticket was Filed

Question

I am trying to create a report to show me the last date a customer filed a ticket.

Customers can file dozens of tickets. I want to know when the last ticket was filed and show how many days it's been since they have done so.

The fields I have are:

Customer,

Ticket_id,

Date_Closed

All from the Same table "Tickets"

I'm thinking I want to do a ranking of tickets by min date? I tried this query to grab something but it's giving me all the tickets from the customer. (I'm using SQL in a product called Domo)

select * from (select *, rank() over (partition by "Ticket_id" 
                order by "Date_Closed" desc) as date_order
from tickets ) zd
where date_order = 1

Answer 1

select Customer, datediff(day, date_closed, current_date) as days_since_last_tkt
from 
(select *, rank() over (partition by Customer order by "Date_Closed" desc) as date_order
from tickets) zd
join tickets t on zd.date_closed = t.date_closed
where zd.date_order = 1

Or you can simply do

select customer, datediff(day, max(Date_closed), current_date) as days_since_last_tkt
from tickets
group by customer

To select other fields

select t.* 
from tickets t
join (select customer, max(Date_closed) as mxdate,
      datediff(day, max(Date_closed), current_date) as days_since_last_tkt
      from tickets
      group by customer) tt 
on t.customer = tt.customer and tt.mxdate = t.date_closed