Return of date difference from the same table column in SQL Server

Question

I tried to get date difference in days from the same table column. For example customer 1234 first order date with the same date should be zero while the next diff orders (days) will be the difference in days between the first initial order date. Same will apply to customer 4321, his first initial order date will be 0 then the second will be the diff between the first and the third will be the diff between the second and the third.

select 
    a1.order_date, 
    (datediff(day, a2.order_date, a1.order_date)) new_value 
from 
    #tmp a1 
inner join 
    #tmp a2 on a1.customer_id = a2.customer_id   
where 
    a1.customer_id = '1234' 
    and a1.TimeToPreviousOrder is null 
    and a1.stand is null
group by  
    a1.customer_id, a1.order_date, a2.order_date, a2.customer_id

customer_id	order_date	Diff in orders (days)
1234	2019-05-11 00:00:00.000	0
1234	2019-05-11 00:00:00.000	0
1234	2019-05-29 00:00:00.000	18
4321	2019-06-29 00:00:00.000	0
4321	2019-06-29 00:00:00.000	0
4321	2019-06-30 00:00:00.000	1

SQL Fiddle

It looks a little bit unclear for me. First point "For example customer 1234 first order date with the same date should be zero while the next diff orders (days) will be the difference in days between the first initial order date." (in this case initial order_date is '2019-05-11 00:00:00.000' and we can use FIRST_VALUE ) Second "then the second will be the diff between the first and the third will be the diff between the second and the third."(in this case weuse LAG/LEAD-functional) — Sergey, Mar 12 '23 at 16:42

Igor Borisenko · Answer 1 · 2023-03-12T16:55:18.360

You can try this

select  a1.customer_id,
        a1.order_date, 
        DATEDIFF(day, COALESCE(a2.order_date, a1.order_date), a1.order_date) diff_days 
    from 
        [dbo].[items] a1 
    OUTER APPLY(SELECT TOP(1) a2.order_date
                 FROM [dbo].[items] a2 
                 WHERE a2.customer_id = a1.customer_id
                       AND a2.order_date < a1.order_date
                 ORDER BY a2.order_date DESC) a2
ORDER BY  a1.customer_id,
        a1.order_date

or better this for version 2012+

WITH cte AS(
select  a1.customer_id,
        a1.order_date, 
        LEAD(order_date, 1,0) over(partition by a1.customer_id order by a1.order_date DESC) ld
from [dbo].[items] a1)
SELECT c.customer_id,
       c.order_date,
       DATEDIFF(DAY, CASE WHEN c.ld = '19000101'THEN c.order_date ELSE c.ld  END, c.order_date)
FROM cte c
ORDER BY  c.customer_id,
        c.order_date

Return of date difference from the same table column in SQL Server

1 Answers1