CODEWITHSUNDEEP
mysqlsql
Andhika R.K.
Andhika R.K.

Reputation: 436

Show Previous and Next Date Value in Mysql in One Row

I have a table like this, i'm using MySQL and still stuck to show date in previous record.

intDateId |  dateLeave  | dateCreated | intUpdateOrder
1         |  2018-04-01 | 2018-04-01  |  NULL
2         |  2018-04-02 | 2018-04-01  |  NULL
3         |  2018-04-11 | 2018-04-02  |  1
4         |  2018-04-20 | 2018-04-02  |  2

This is my query so far, i'm still unable to show previous date:

    SELECT 
     intDateId, 
     GROUP_CONCAT(dateLeave) AS datePrevious, // i'm stuck here
     GROUP_CONCAT(dateLeave) AS dateCurrentUpdate, 
     intUpdateOrder
    FROM leave_date
    WHERE intUpdateOrder IS NOT NULL
    GROUP BY intUpdateOrder

What i want is like this:

dateCreated | datePrevious             | dateCurrentUpdate   | intUpdateOrder
2018-04-02  | (2018-04-01, 2018-4-02)  | (2018-04-11)        | 1 // datePrevious WHERE intUpdateOrder NULL
2018-04-02  | (2018-04-11)             | (2018-04-20)        | 2 // datePrevious WHERE intUpdateOrder 1

Thank you

Upvotes: 0

Views: 91

Answers (1)

Sergey Menshov
Sergey Menshov

Reputation: 3906

Try the following query

SELECT
  q1.dateCreated,
  q2.datePrevious,
  q1.dateCurrentUpdate,
  q1.intUpdateOrder
FROM
  (
    SELECT
      t.*, 
      @rownum1 := @rownum1 + 1 AS rank
    FROM
      (
        SELECT
          IFNULL(intUpdateOrder,0) intUpdateOrder,
          MAX(dateLeave) dateCurrentUpdate,
          MAX(dateCreated) dateCreated
        FROM leave_date
        GROUP BY intUpdateOrder
      ) t,
      (SELECT @rownum1 := 0) r
    ORDER BY intUpdateOrder
  ) q1
JOIN
  (
    SELECT
      t.*, 
      @rownum2 := @rownum2 + 1 AS rank
    FROM
      (
        SELECT
          IFNULL(intUpdateOrder,0) intUpdateOrder,
          GROUP_CONCAT(dateLeave) datePrevious
        FROM leave_date
        GROUP BY intUpdateOrder
      ) t,
      (SELECT @rownum2 := 0) r
    ORDER BY intUpdateOrder
  ) q2
ON q1.rank=q2.rank+1

SQL Fiddle - http://www.sqlfiddle.com/#!9/006e88/17

Upvotes: 2

Related Questions