Cfrgtkky

I have a transaction table where I have to find the first and second date of transaction of every customer. Finding first date is very simple where I can use MIN() func to find the first date but the second and in particular finding the difference between the two is getting very challenging and somehow I am not able to find out any feasible way:

select a.customer_id, a.transaction_date, a.Row_Count2

from ( select

       transaction_date as transaction_date,

       reference_no as customer_id,

       row_number() over (partition by reference_no 

                          ORDER BY reference_no, transaction_date) AS Row_Count2

       from transaction_detail 

     ) a

where  a.Row_Count2 < 3

ORDER BY a.customer_id, a.transaction_date, a.Row_Count2

Gives me this :

What I want is , following columns:

||CustomerID|| ||FirstDateofPurchase|| ||SecondDateofPuchase|| ||Diff. b/w Second & First Date ||

You can use window functions LEAD/LAG to return results you are looking for

First try to find all the leading dates by reference number using LEAD, generate row number for each row using your original logic. You can then do difference on dates for row number value 1 row from the result set.

Ex (I'm not excluding same day transactions and treating them as separate and generating row number based on result set from your query above, you can easily change the sql below to consider these as one and remove them so that you get next date as second date):

declare @tbl table(reference_no int, transaction_date datetime)

insert into @tbl

select 1000, '2018-07-11'

UNION ALL

select 1001, '2018-07-12'

UNION ALL

select 1001, '2018-07-12'

UNIOn ALL

select 1001, '2018-07-13'

UNIOn ALL

select 1002, '2018-07-11'

UNIOn ALL

select 1002, '2018-07-15'



select customer_id, transaction_date as firstdate, 

transaction_date_next seconddate, 

datediff(day, transaction_date,  transaction_date_next) diff_in_days

from

(

    select reference_no as customer_id, transaction_date, 

    lead(transaction_date) over (partition by reference_no 

                                   order by transaction_date) transaction_date_next,

    row_number() over (partition by reference_no ORDER BY transaction_date) AS Row_Count

    from @tbl

) src

where Row_Count = 1

You can do this with CROSS APPLY.

SELECT td.customer_id, MIN(ca.transaction_date), MAX(ca.transaction_date),

       DATEDIFF(day, MIN(ca.transaction_date), MAX(ca.transaction_date))

FROM transaction_detail td

CROSS APPLY (SELECT TOP 2 *

             FROM transaction_detail

             WHERE customer_id = td.customer_id

             ORDER BY transaction_date) ca

GROUP BY td.customer_id