Raunak Gupta Raunak Gupta - 2 months ago 6
MySQL Question

MySQL query to compare two date and it will traverse from one column to other

enter image description here

I have a table

pa_acc_status
, I have attached the image of table.

I want to compare 2 date (one 'll be user input and other will be column value) and it will traverse from one column to other in a loop using MYSQL.

For example: first it will check that
step_22_due_date
column is not null and
step_22_due_date
<=
USER_DATE
, if this is true then exit for that row and continue with next row otherwise do same for step_21_complete, then step_21_due_date, then step_1_complete

ie
step_22_due_date
->
step_21_complete
->
step_21_due_date
->
step_1_complete


I want the output in 2 ways

CASE 1: for USER_DATE =
2016-09-03


First Like this

id | step_1_complete | step_21_due_date | step_21_complete | step_22_due_date | user_id
1 | NULL | 2016-03-12 | NULL | NULL | 1
2 | NULL | 2016-03-12 | NULL | NULL | 1
3 | NULL | NULL | NULL | 2016-04-15 | 2
4 | NULL | 2016-03-12 | NULL | NULL | 2
5 | NULL | NULL | 2016-03-12 | NULL | 2


And second one which is the count of the date which is not null and
group by user_id


step_1_complete | step_21_due_date | step_21_complete | step_22_due_date | user_id
0 | 2 | 0 | 0 | 1
0 | 1 | 1 | 1 | 2


CASE 2: for USER_DATE =
2016-03-10


First Like this

id | step_1_complete | step_21_due_date | step_21_complete | step_22_due_date | user_id
1 | 2016-03-08 | NULL | NULL | NULL | 1
2 | 2016-03-08 | NULL | NULL | NULL | 1
3 | 2016-03-08 | NULL | NULL | NULL | 2
4 | 2016-01-03 | NULL | NULL | NULL | 2
5 | NULL | 2016-03-08 | NULL | NULL | 2


And second one which is the count of the date which is not null and
group by user_id


step_1_complete | step_21_due_date | step_21_complete | step_22_due_date | user_id
2 | 0 | 0 | 0 | 1
2 | 1 | 0 | 0 | 2


I don't know it is possible or not using MySQL, any help or suggestion will be helpful.

Answer

Finally I got the solution, of my question,

For Data list

SET @USER_DATE = '2016-03-08';

SELECT *
FROM
  (SELECT `id`,
          `user_id`,
          `step_1_complete`,
          NULL AS `step_21_due_date`,
          NULL AS `step_21_complete`,
          NULL AS `step_22_due_date`
   FROM `pf_acc_status`
   WHERE `step_1_complete` IS NOT NULL
     AND `step_1_complete` <= @USER_DATE
     AND (`step_21_due_date` IS NULL
          OR step_21_due_date >= @USER_DATE)
   UNION ALL SELECT `id`,
                    `user_id`,
                    NULL AS `step_1_complete`,
                    `step_21_due_date`,
                    NULL AS `step_21_complete`,
                    NULL AS `step_22_due_date`
   FROM `pf_acc_status`
   WHERE `step_21_due_date` IS NOT NULL
     AND `step_21_due_date` <= @USER_DATE
     AND (`step_21_complete` IS NULL
          OR step_21_complete >= @USER_DATE)
   UNION ALL SELECT `id`,
                    `user_id`,
                    NULL AS `step_1_complete`,
                    NULL AS `step_21_due_date`,
                    `step_21_complete`,
                    NULL AS `step_22_due_date`
   FROM `pf_acc_status`
   WHERE `step_21_complete` IS NOT NULL
     AND `step_21_complete` <= @USER_DATE
     AND (`step_22_due_date` IS NULL
          OR step_22_due_date >= @USER_DATE)
   UNION ALL SELECT `id`,
                    `user_id`,
                    NULL AS `step_1_complete`,
                    NULL AS `step_21_due_date`,
                    NULL AS `step_21_complete`,
                    `step_22_due_date`
   FROM `pf_acc_status`
   WHERE `step_22_due_date` IS NOT NULL
     AND `step_22_due_date` <= @USER_DATE ) AS `a`
ORDER BY a.`id` ASC

For count data

SET @USER_DATE = '2016-03-08';

SELECT a.`user_id`,
       count(a.`step_1_complete`) AS step_1_complete,
       count(a.`step_21_due_date`) AS step_21_due_date,
       count(a.`step_21_complete`) AS step_21_complete,
       count(a.`step_22_due_date`) AS step_22_due_date
FROM
  (SELECT `id`,
          `user_id`,
          `step_1_complete`,
          NULL AS `step_21_due_date`,
          NULL AS `step_21_complete`,
          NULL AS `step_22_due_date`
   FROM `pf_acc_status`
   WHERE `step_1_complete` IS NOT NULL
     AND `step_1_complete` <= @USER_DATE
     AND (`step_21_due_date` IS NULL
          OR step_21_due_date >= @USER_DATE)
   UNION ALL SELECT `id`,
                    `user_id`,
                    NULL AS `step_1_complete`,
                    `step_21_due_date`,
                    NULL AS `step_21_complete`,
                    NULL AS `step_22_due_date`
   FROM `pf_acc_status`
   WHERE `step_21_due_date` IS NOT NULL
     AND `step_21_due_date` <= @USER_DATE
     AND (`step_21_complete` IS NULL
          OR step_21_complete >= @USER_DATE)
   UNION ALL SELECT `id`,
                    `user_id`,
                    NULL AS `step_1_complete`,
                    NULL AS `step_21_due_date`,
                    `step_21_complete`,
                    NULL AS `step_22_due_date`
   FROM `pf_acc_status`
   WHERE `step_21_complete` IS NOT NULL
     AND `step_21_complete` <= @USER_DATE
     AND (`step_22_due_date` IS NULL
          OR step_22_due_date >= @USER_DATE)
   UNION ALL SELECT `id`,
                    `user_id`,
                    NULL AS `step_1_complete`,
                    NULL AS `step_21_due_date`,
                    NULL AS `step_21_complete`,
                    `step_22_due_date`
   FROM `pf_acc_status`
   WHERE `step_22_due_date` IS NOT NULL
     AND `step_22_due_date` <= @USER_DATE ) AS `a`
GROUP BY a.`user_id`
ORDER BY a.`id` ASC
Comments