SQL: Unable to filter rows using datediff or date_add using reference from the parent query in the subquery

Question

I am trying to match data where the record_status_id flag is changed from 2 to 1 in more than 90 days. The query should ignore the records which changes from 1 to 2. I have taken help and resources from this question. I have following data sets:

revision    record_id   record_status_id    last_updated_date
239 59  1   2016-01-19 10:18:00
284 59  2   2016-01-19 12:12:00
302 59  2   2016-01-20 15:18:00
304 59  2   2016-01-25 16:26:00
305 59  1   2016-05-24 16:29:00
307 59  2   2016-05-25 11:33:00
309 59  1   2016-07-07 10:15:00
310 59  2   2016-08-07 12:44:00
489 59  1   2016-11-11 02:12:00
492 59  1   2016-11-11 05:25:00
502 59  2   2016-11-12 10:12:00
504 59  2   2016-12-05 11:23:00
509 59  1   2016-12-11 12:24:00
519 59  1   2016-12-11 14:30:00
524 59  1   2016-12-11 15:36:00
674 59  1   2016-12-11 17:45:00

Schema and Data:

CREATE TABLE IF NOT EXISTS record_history (
  `revision` INT(11) NOT NULL,
  `record_id` BIGINT(20) NOT NULL,
  `creation_date` DATETIME NULL DEFAULT NULL,
  `record_status_id` BIGINT(20) NULL DEFAULT NULL,
  `last_updated_date` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY USING BTREE (`record_id`, `revision`))
ENGINE = InnoDB;

INSERT INTO record_history(revision,record_id,creation_date,record_status_id,last_updated_date) VALUES
( 239,59,'2016-01-18 16:23:00',1,'2016-01-19 10:18:00'),
( 284,59,'2016-01-18 16:23:00',2,'2016-01-19 12:12:00'),
( 302,59,'2016-01-18 16:23:00',2,'2016-01-20 15:18:00'),
( 304,59,'2016-01-18 16:23:00',2,'2016-01-25 16:26:00'),
( 305,59,'2016-01-18 16:23:00',1,'2016-05-24 16:29:00'),
( 307,59,'2016-01-18 16:23:00',2,'2016-05-25 11:33:00'),
( 309,59,'2016-01-18 16:23:00',1,'2016-07-07 10:15:00'),
( 310,59,'2016-01-18 16:23:00',2,'2016-08-07 12:44:00'),
( 489,59,'2016-01-18 16:23:00',1,'2016-11-11 02:12:00'),
( 492,59,'2016-01-18 16:23:00',1,'2016-11-11 05:25:00'),
( 502,59,'2016-01-18 16:23:00',2,'2016-11-12 10:12:00'),
( 504,59,'2016-01-18 16:23:00',2,'2016-12-05 11:23:00'),
( 509,59,'2016-01-18 16:23:00',1,'2016-12-11 12:24:00'),
( 519,59,'2016-01-18 16:23:00',1,'2016-12-11 14:30:00'),
( 524,59,'2016-01-18 16:23:00',1,'2016-12-11 15:36:00'),
( 674,59,'2016-01-18 16:23:00',1,'2016-12-11 17:45:00');

So far, I have used following query:

SELECT revision, record_id, record_status_id, last_updated_date FROM record_history AS a
WHERE a.record_status_id = 2
and a.record_status_id <> ( SELECT b.record_status_id FROM record_history AS b
                                WHERE a.record_id = b.record_id
                                AND a.last_updated_date < b.last_updated_date
                                -- AND DATEDIFF(b.last_updated_date, a.last_updated_date) > 90
                                -- and b.last_updated_date > DATE_ADD(a.last_updated_date,INTERVAL 90 DAY)
                                LIMIT 1
                              );

to obtain;

revision    record_id   record_status_id    last_updated_date
304 59  2   2016-01-25 16:26:00
307 59  2   2016-05-25 11:33:00
310 59  2   2016-08-07 12:44:00
504 59  2   2016-12-05 11:23:00

Further down, I want to filter down the records where the date difference between where the record_status_id flag changes from 2 to 1 is greater than 90. I have commented the DATEDIFF and DATE_ADD as it doesn't seem to be working. Ideally, it should have worked for me but its not. My desired result is given below as the flag of revision 304 and 305 is changed from 2 to 1 after 90 days and so is the case for record with revision 310;

revision    record_id   record_status_id    last_updated_date
304 59  2   2016-01-25 16:26:00
310 59  2   2016-08-07 12:44:00

Thanks for your effort. I don't know whats causing the erratic behaviour with DATEDIFF and DATE_ADD. If I uncomment these keyword lines, the filtered records expands instead of narrowing to the desired result. I have created sample data in http://rextester.com/EKHGF57315 along with the query I have used.

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Apr 23 '17 at 07:11
ok thanks. I will be updating with formatted date time stamps as suggested in sqlfiddle or rextester. — nexuscreator, Apr 23 '17 at 07:40
What datatype are the columns? What values do you get when simply fetching the DATEDIFF? Show us 3 columns - the 2 dates and the "erratic" datediff. — Rick James, Apr 23 '17 at 17:47
'last_updated_time' is of type DATETIME. I have used DATEDIFF in the subquery(which is currently commented in this question query block) so I cannot pull it up to the parent query results.. The two datetime columns are present in the parent(a.last_updated_date) and subquery(b.last_updated_date) and datediff is used on the subquery "DATEDIFF(b.last_updated_date, a.last_updated_date) > 90" . I have edited/posted schema along with insert data along in the question and in rextester. — nexuscreator, Apr 24 '17 at 02:42

score 0 · Answer 1 · edited May 23 '17 at 12:18

With the help of this question in stackoverflow, I was able to solve my question. Here's my solution:

SELECT revision, record_id, record_status_id, last_updated_date FROM record_history AS a
WHERE a.record_status_id = 2
and a.record_status_id <> ( SELECT T.record_status_id FROM record_history AS T
                                WHERE a.record_id = T.record_id
                                AND a.last_updated_date < T.last_updated_date
                                LIMIT 1
                            )
and a.revision in (
    SELECT T.revision
    -- SELECT T.revision, T.record_id, T.record_status_id, T.last_updated_date, DATEDIFF(T.NextDate, T.last_updated_date) as days
    FROM (SELECT  T1.revision, T1.record_id, T1.record_status_id, T1.last_updated_date,
                        ( SELECT T2.last_updated_date
                            FROM record_history T2
                            WHERE T2.record_id = T1.record_id
                            AND T2.last_updated_date > T1.last_updated_date limit 1
                        ) AS NextDate
                FROM record_history T1
    ) AS T
    WHERE DATEDIFF(T.NextDate, T.last_updated_date) > 90
);

Hope this will be useful for fellow users.

SQL: Unable to filter rows using datediff or date_add using reference from the parent query in the subquery

1 Answers1