Compare current row value with next row value in php

ID UserID    Payment_Date  Expired_Date      Diff(Payment_Date&Expired_Date)

 1  123   2017-08-01 00:00:00  2018-01-01 00:00:00             
 2  123   2018-02-01 00:00:00  2018-10-01 00:00:00              31    
 3  123   2018-10-05 00:00:00  2018-10-10 00:00:00              4

How to compare first row expiry date with second row payment date . I need to show result like Diff(Payment_Date&Expired_Date) 31,4 .

1 answer

  • answered 2018-05-16 05:58 Thomas G

    No need PHP, You can do this in SQL

    SELECT ID, UserID, Payment_Date, Expired_Date, lastpayement, DATEDIFF(lastpayement, Expired_Date) AS DIFF
    FROM
    (
        SELECT m.ID, m.UserID, m.Payment_Date, m.Expired_Date, 
                (
                    SELECT Payment_Date
                    FROM mytable tt
                    WHERE  m.Expired_Date < tt.Payment_Date AND tt.UserID=m.UserID
                    GROUP BY UserID
                ) AS lastpayement
        FROM mytable m
    ) t 
    

    Results

    | ID | UserID |         Payment_Date |         Expired_Date |         lastpayement |   DIFF |
    |----|--------|----------------------|----------------------|----------------------|--------|
    |  1 |    123 | 2017-08-01T00:00:00Z | 2018-01-01T00:00:00Z | 2018-02-01T00:00:00Z |     31 |
    |  2 |    123 | 2018-02-01T00:00:00Z | 2018-10-01T00:00:00Z | 2018-10-05T00:00:00Z |      4 |
    |  3 |    123 | 2018-10-05T00:00:00Z | 2018-10-10T00:00:00Z |               (null) | (null) |
    

    SQLfiddle