Mysql : Previous Row data if data on another row is equal to data on current row

Question

So I have the following membership history table

User_ID  |  Start date  |  End Date  |  Type(0-7) |  
--------------------------------------------------
    2    |   date1      |  date2     |    0       |  
--------------------------------------------------
    2    |   date3      |  date4     |    3       |  
--------------------------------------------------
    2    |   date5      |  date6     |    3       |  
--------------------------------------------------
    2    |   date7      |  date8     |    1       |  
--------------------------------------------------
    3    |   date9      |  date10    |    6       |  
--------------------------------------------------
    3    |   date11     |  date12    |    0       |  
--------------------------------------------------

I am trying to create the mysql query with the result:

    User_ID  |  Start date  |  End Date  |  Type(0-7) |PrevEnd-CurStart| Prevtype    
    ----------------------------------------------------------------------------------
        2    |   date1      |  date2     |    0       |  null OR 0     | null OR 0
    ----------------------------------------------------------------------------------
        2    |   date3      |  date4     |    3       |  Date3-Date2   |  0
    ----------------------------------------------------------------------------------
        2    |   date5      |  date6     |    3       |  Date5-Date4   |  3
    ----------------------------------------------------------------------------------
        2    |   date7      |  date8     |    1       |  Date7-Date6   |  3
    ----------------------------------------------------------------------------------
        3    |   date9      |  date10    |    6       |  null Or 0     |  null OR 0
    ----------------------------------------------------------------------------------
        3    |   date11     |  date12    |    0       |  Date11-Date10 |  6
    ----------------------------------------------------------------------------------

I put in Null or 0 because I would be able to work with it either way from that point on

How would I go about writing the mysql query for that? this is just a sub-query for a report I need, and I've been stuck on it all day yesterday, last night and today. Would really appreciate the help. Run time has little relevance, as I need to run this just once per month.

EDIT: To get my current output, I use the following. The 'group by 1,2,3,4' is because of a lot of duplicates that I need to get rid of

select 
 mh.account_id, 
 mh.start_date, 
 mh.end_date, 
 mh.`type`
from tbl_membership_history mh join tbl_membership m on mh.account_id=m.account_id
group by 1,2,3,4
order by 1,2,3,4
limit 100

eggyal · Accepted Answer · 2012-10-19 14:41:43Z

2

You can make a self-join on your table with earlier records, then aggregate to find the latest of those earlier records' end dates (which gives the previous end). One must then join that result to the table a further time in order to find the Type of the previous record:

SELECT   res.*, prv.Type AS prvType
FROM (
  SELECT   cur.*, MAX(bef.endDate) AS prvEnd
  FROM     history AS cur LEFT JOIN history AS bef
        ON bef.User_ID = cur.User_ID AND bef.endDate <= cur.startDate
  GROUP BY cur.User_ID, cur.startDate, cur.endDate, cur.Type
) res LEFT JOIN history AS prv
        ON prv.User_ID = res.User_ID AND prv.endDate = res.prvEnd

See it on sqlfiddle.

answered Oct 19, 2012 at 14:41

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AdrianBR Over a year ago

You Sir are a genius! I had an offline weekend(not by choice) but I just ran it! AMAZING! thank you!

Collectives™ on Stack Overflow

Mysql : Previous Row data if data on another row is equal to data on current row

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related