Query for datediff between rows where value was unchanged, grouped by the value

I am looking to query an invoices table which also keeps track of which “program” customers were on at the time of their purchases (not the best way of storing that but it is what it is and I’m left to deal with it).

I would like to see how long each customer was on each program for.

For example, here’s a history of purchases made by a couple of customers. We can see that the customers were on different programs at different dates. Note that program IDs are not actually sequential (maybe they start on a program with a higher ID than the next program).

invoices table:

cust_id, prog_id, datetime
1,       1,       2020-01-01      < cust 1 first seen on program 1
2,       1,       2020-01-05      < cust 2 first seen on program 1
1,       1,       2020-02-07
1,       2,       2020-02-08      < cust 1 seen on program 2 after 38 days
2,       1,       2020-02-10
2,       2,       2020-02-15      < cust 2 seen on program 2 after 41 days
1,       2,       2020-02-15
2,       2,       2020-02-15
1,       2,       2020-02-22
1,       3,       2020-02-28      < cust 1 seen on program 3 after 20 days
2,       2,       2020-03-05
2,       2,       2020-03-07
2,       3,       2020-03-15      < cust 2 seen on program 3 after 29 days
2,       3,       2020-03-17

I would like to see something like this (done in my head so hopefully the math checks out):

cust_id, prg_id, days_on_prog
1,       1,      38
1,       2,      20
2,       1,      41
2,       2,      29
2,       3,      2

Note that customer 1 was not seen on program 3 more than once, so we don’t have a days_on_prog for them on program 3.

Can you help me build such a query?

Answer

It looks like aggregation should do the job:

select
    cust_id,
    prg_id,
    datediff(max(datetime), min(datetime)) days_on_prog
from invoices
having max(datetime) <> min(datetime)
group by cust_id, prg_id
order by cust_id, prg_id

Advertisement

Answer