CODEWITHSUNDEEP
sqlteradata
Taf
Taf

Reputation: 21

SQL- How to select value from different rows based upon a column

Trying to find changes in a customers order based after an interaction.
Orders: 

Customer_ID Start_dt    End_dt      Order_item
1           01/01/2020  14/01/2020  Apples
1           15/01/2020  01/02/2020  Bananas
1           02/02/2020  07/02/2020  Oranges
1           08/02/2020  Null        Grapes

Interactions:

Customer_ID Interaction_Dt
1           02/02/2020

desired output:

Customer_id Interaction_Dt  Order_Pre   Order_Post
1           02/02/2020      Bananas     Oranges

I have tried using the CASE function, but it's only picking up the Order_Post and giving me null for Order_Pre

select interactions.customer_id, interactions.interaction_dt,
case when interactions.interaction_dt - orders.end_dt = 1 then orders.Order_item else 0 end as Order_pre,
case when orders.start_dt - interactions.interaction_dt = 1 then orders.order_item else 0 end as order_post
 from interactions
left join orders
on interactions.customer_id = orders.customer_id

Pretty new to SQL so any help would be greatly appreciated.
The goal of this is to see after a customer interacts with our business what they changed their order to. The actual tables have more customers and interactions, so I'm trying to create a summary on all the dates that customers had an interaction, what they changed their order from to. Edit: updated table names to make it more clear, sorry for any confusion

Upvotes: 2

Views: 59

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

I think you just want a join with lag():

select t2.*, t1.order_pre, t1.order as order_post
from table2 t2 join
     (select t1.*,
             lag(order) over (partition by customer_id order by start_dt) as order_pre
      from table1 t1
     ) t1
     on t2.custoemr_id = t1.customer_id and
        t2.Interaction_Dt >= t1.start_dt and
        t2.Interaction_Dt <= t1.end_dt;

Upvotes: 2

Related Questions