Select Single Field in two Column (Mysql)

Question

We are using Mysql Database and we have Single tables that is employee_transaction.

employee_transaction table

id |  employee_id | date            | transaction_type
------------------------------------------------------
1  | 1            | 2015-01-01      | 1
2  | 1            | 2015-07-01      | 1 
3  | 1            | 2015-12-31      | 0
4  | 2            | 2014-02-01      | 1 
5  | 1            | 2016-04-01      | 1
6  | 2            | 2014-11-30      | 0
7  | 1            | 2016-08-01      | 1
8  | 1            | 2016-10-31      | 0 

And so on.

To get the proper Result, the following Conditions need to be satisfied :-

• First transaction_type of any employee is always 1.
• Any Employee cannot have Consecutive transaction_type=0.

• The result of two consecutive transaction_type are below.These results is for one Employee

  1. If both Transaction_type=1. Then start_date=first transaction date, end_date= second transaction date-1 and transaction_type=1.
  2. If first Transaction_type=1 and next transaction_type=0. Then start_date=first transaction date, end_date= second transaction date and transaction_type=1.
  3. If first Transaction_type=0 and next transaction_type=1. Then start_date=first transaction date+1, end_date= second transaction date-1 and transaction_type=0.
  4. If No transaction is found after transaction_type=0. Then start_date=first transaction date+1, end_date= Null and transaction_type=0.

We need the Query of following output from the employee_transaction table.

  employee_id |  start_date     | end_date       | transaction_type
------------------------------------------------------
 1            | 2015-01-01      | 2015-06-30     | 1
 1            | 2015-07-01      | 2015-12-31     | 1 
 1            | 2016-01-01      | 2016-03-31     | 0
 1            | 2016-04-01      | 2016-07-31     | 1
 1            | 2016-08-01      | 2016-10-31     | 1
 1            | 2016-11-01      | (Null)         | 0 
 2            | 2014-02-01      | 2014-11-30     | 1

Thanks in advance.

If you have any concerns/clarification required, revert me.

Answer 1

Here's a query using correlated subqueries to join the sequential date and status information for each employee:

select
    f.emp_id, f.trans_date, f.trans_type,
    (select trans_date from emp_trans
    where emp_id=f.emp_id and trans_date = (select min(trans_date)
                                       from emp_trans where emp_id=f.emp_id
                                       and trans_date > f.trans_date)
    ) as end_trans_date,
    (select trans_type from emp_trans
    where emp_id=f.emp_id and trans_date = (select min(trans_date)
                                       from emp_trans where emp_id=f.emp_id
                                       and trans_date > f.trans_date)
    ) as end_trans_type
from emp_trans as f;

Answer 2

Get the next date, next transaction_type (ordered by date) per employee_id using a correlated subquery. Then use a case expression per the conditions mentioned.

select employee_id
,case when (transaction_type=0 and nxt_t_type=1) or (transaction_type=0 and nxt_t_type is null) then dt+interval '1' day
 else dt
 end as dt
,case when nxt_t_type=1 then nxt_dt-interval '1' day 
      when transaction_type=1 and nxt_t_type=0 then nxt_dt
      when transaction_type=0 and nxt_t_type is null then null
 end as nxt_dt
,transaction_type
from (select t1.*
      ,(select t2.dt from t t2 where t1.employee_id=t2.employee_id and t2.dt > t1.dt 
        order by t2.dt limit 1) as nxt_dt
      ,(select t2.transaction_type from t t2 where t1.employee_id=t2.employee_id and t2.dt > t1.dt 
        order by t2.dt limit 1) as nxt_t_type
      from t t1
     ) x

Sample Demo