In SQLite, I have a table where the result date and diagnosis dates have the same dates but have different times. Diagnosis date and time are always after test result date and time. I have tried the code below, but it did not work as it gave me both rows instead of the second row of Table 3. The desired result (after coding) is to keep the second row of Table 3 as the diagnosis date/time and test result date/time are in the correct order. Is using a CASE statement appropriate to use here or would I use a different statement? I hope this question is clear as I am new here.
Table 3
| TEST RESULT_DATE | TEST RESULT_TIME | DIAG_DATE | DIAG_TIME |
|---|---|---|---|
| 2020-09-08 | 2:09:34 | 2020-09-08 | 0:00:00 |
| 2020-03-03 | 9:49:39 | 2020-03-03 | 13:00:09 |
Desired Result (after coding):
| RESULT_DATE | RESULT_TIME | DIAG_DATE | DIAG_TIME |
|---|---|---|---|
| 2020-03-03 | 9:49:39 | 2020-03-03 | 13:00:09 |
/*joined table1 and table2 to create table3*/
CREATE TABLE table3 AS
SELECT *
FROM table2 JOIN table1 ON table2.PTID=table1.PTID AND RESULT_DATE1 BETWEEN DATE(diagdate1,'-7 day') AND diagdate1
AND RESULT_TIME < DIAG_TIME
ORDER BY PTID ASC
;
SELECT
CASE WHEN RESULT_DATE1 = diagdate1
THEN RESULT_TIME < DIAG_TIME
END
FROM table3
;
CodePudding user response：
If I understand your question correctly. You don't need a case statement. A when statement should give you the result you want.
SELECT * from tableA
WHERE TEST_RESULT_DATE = DIAG_DATE
and TEST_RESULT_TIME < DIAG_TIME