CODEWITHSUNDEEP
sqlssms
Ivancito
Ivancito

Reputation: 169

Getting the date difference per Product and ID

I have a table like this:

enter image description here

I want to get the date difference between products according to the Product_Order, taking into consideration the ID. Therefore, the last number of Product_Order for a given ID should be NULL.

For example, for ID 1 I would like to get the date difference like this:

Date Difference
From 1 to 2 - 4 years
From 2 to 3 - 5 years

Upvotes: 0

Views: 36

Answers (2)

GMB
GMB

Reputation: 222582

You can use window function LEAD() to get the next date value for the same id (ordered by product_number), and DATEDIFF() to compute the difference:

SELECT
    t.*,
    DATEDIFF(
        year,
        LEAD([date]) OVER(PARTITION BY id ORDER BY product_order),
        [date]
    ) diff
FROM mytable

This will give you the difference in years, which seems to be what you are looking for.

Upvotes: 1

catBoi
catBoi

Reputation: 19

Assuming the name of the table is 'test'. Try -

select a.date - b.date
from test a, test b
where a.id = b.id 
and a.product_order < b.product_order

Upvotes: 0

Related Questions