CODEWITHSUNDEEP
sqlsql-server-2014
ct14
ct14

Reputation: 77

Recursive query with time difference

This is my first post here even though I am a daily reader. :)

I need to produce an MS SQL Server 2014 report that shows the clients that come back to do business with me in less than or equal to 3 days. I tried with INNER JOINS but I wasn't successful.

The way I thought of the solution is using the below Logic:

If product is same
    and if userId is same
    and if action was donedeal but now is new
    and if date diff <= 3 days
    and if type is NOT same
then show results

e.g of my Data:

id  orderId userId  type    product date        action
1   1001    654     ordered apple   01/05/2016  new
2   1002    889     ordered peach   01/05/2016  new
3   1001    654     paid    apple   01/05/2016  donedeal
4   1002    889     paid    peach   03/05/2016  donedeal
5   1003    654     ordered apple   03/05/2016  new
6   1004    889     ordered peach   04/05/2016  new
7   1005    122     ordered apple   04/05/2016  new
8   1006    978     ordered peach   04/05/2016  new
9   1005    122     paid    apple   04/05/2016  donedeal
10  1007    122     ordered apple   10/05/2016  new

Desired results:

id  orderId userId  type    product date        Diff
3   1001    654     paid    apple   01/05/2016  2 days
4   1002    889     paid    peach   03/05/2016  1 day
5   1003    654     ordered apple   03/05/2016  2 days
6   1004    889     ordered peach   04/05/2016  1 day

Could you please direct me to the functions that can be useful for me to solve this?

Thanks in advance.

#

Update

Gordon Linoff gave me the suggested code below but since the Type had to be different I replicated the code and run it as per below and it worked:

select t.* from (select t.*, max(case when action = 'donedeal' and type='paid' then date end) over (partition by user, product order by date) as last_donedealdate from t ) t where action = 'new' and type='ordered' date < dateadd(day, 3, last_donedealdate)

UNION ALL

select t.* from (select t.*, max(case when action = 'donedeal' and type='ordered' then date end) over (partition by user, product order by date) as last_donedealdate from t ) t where action = 'new' and type='paid' date < dateadd(day, 3, last_donedealdate)

Upvotes: 1

Views: 344

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You can use window functions for this. To get the last done deal date, use max() with partition by and order by. The rest is just where clause logic:

select t.*
from (select t.*,
             max(case when action = 'donedeal' then date end) over
                 (partition by user, product order by date) as last_donedealdate
      from t
     ) t
where action = 'new' and date < dateadd(day, 3, last_donedealdate);

Upvotes: 1

Related Questions