B4J Question SQL query

[le_toubib]

hi falks
i dunno if this is the right place for this question ..
i m trying to find all hospital admissions that occurred within 1 month from last discharge ; i.e select the records separated by less than one month.
i tried this query but it was very slow and inacurate ..

Select *
            FROM
                interview
            WHERE
            (interview.Date BETWEEN ? AND ?)  and
            interview.INpatient=1 and
            interview.UniqueID IN
            (SELECT interview.UniqueID FROM interview where (interview.DischargeDate BETWEEN ? AND ?)  )
            order by interview.Date

i tried it inside b4J using a recursive function , that worked , but the table can be huge , making the code unusable.

any help with the query ???

 

[Jorge M A]

Which Database are you using? SQLite or MS SQL?

The first approach could be to review the design and include dates in indexes and establish relationships to avoid subquery using INNER JOIN.

 

[le_toubib]

im using mysql
dates are already indexed , i dont get what u mean by relationships ? this is only 1 table

 

[Jorge M A]

Ok, It could be a "logical relation" only with Alias, something like this:

SELECT I.*
FROM   interview AS I
INNER JOIN interview AS D
ON I.uniqueid = D.uniqueid
WHERE  I.date BETWEEN ? AND ?
       AND I.inpatient = 1
       AND D.dischargedate BETWEEN ? AND ?
ORDER  BY I.date

 

[Jorge M A]

Can you post/attach some example data and desired results, please?

 

[le_toubib]

we need all the records in which a patient was readmitted within 1 month


sample data:
interview table

-------------------------------------------------------
|Admission_Date | Discharge_Date | UniqueID |
-------------------------------------------------------
| 2019-02-03 | 2019-03-15 | 5 |
| 2019-02-06 | 2019-02-13 | 2 |
| 2019-02-15 | 2019-04-09 | 3 |
| 2019-02-28 | 2019-05-26 | 2 |
| 2019-06-19 | 2019-10-18 | 5 |
-------------------------------------------------------


patient no 2 was discharged on 2019-02-13 then readmitted on 2019-02-28 (i.e : admitted within less than 1 month )
expected results (sorted by UniqueID) :

-------------------------------------------------------
|Admission_Date | Discharge_Date | UniqueID |
-------------------------------------------------------
| 2019-02-06 | 2019-02-13 | 2 |
| 2019-02-28 | 2019-05-26 | 2 |
-------------------------------------------------------

 

[le_toubib]

this query is very slow (27 seconds ) , and shows double the results (each record is shown twice ) , and also it omitted the first raw .

SELECT I.*
FROM   interview AS I
INNER JOIN interview AS D
ON I.uniqueid = D.uniqueid
WHERE  I.date BETWEEN '2019-01-01' AND '2019-11-01'
       AND I.inpatient = 1
       AND DATEDIFF(I.Date,D.DischargeDate) < 28
ORDER  BY I.UniqueID

 

[Jorge M A]

-------------------------------------------------------
|Admission_Date | Discharge_Date | UniqueID |
-------------------------------------------------------

This does not correspond to the first query you post.

this query is very slow (27 seconds )

WHERE  I.date BETWEEN '2019-01-01' AND '2019-11-01'

You are selecting almost a year, no one month as you say in your first post.
How many records has the table now?

With the information you provide in post #6, and since you require both records involved in the condition, for now I see no other way than to give two passes to the table.

SELECT  A.*
  FROM Interview AS A, Interview AS B
  WHERE A.UniqueID=B.UniqueID
  AND A.Admission_Date < B.Admission_Date
  AND DATEDIFF(d, A.Discharge_Date , B.Admission_Date) <= 28
UNION
SELECT  B.*
  FROM Interview AS A, Interview AS B
  WHERE A.UniqueID=B.UniqueID
  AND B.Admission_Date > A.Admission_Date
  AND DATEDIFF(d, A.Discharge_Date , B.Admission_Date) <= 28

ORDER BY UniqueID, Admission_Date

The definitive structure of your table and the indexes you have defined will remain to be seen, in order to try to optimize the query.
With a little more analysis and effort it might be possible to replace it with a CTE.

MySQL 8 supports CTEs now: http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes/

 

[le_toubib]

You are selecting almost a year, no one month as you say in your first post.
How many records has the table now?

i need a range to test from , in this example it s from january till November , i.e i need to find readmissions separated by <= 1 month (28 days) within this period jan to nov.
This range would make around 100000 records

 

[le_toubib]

@Jorge M A , your query worked very well as intended, still slow though :

SELECT A.* FROM Interview AS A, Interview AS B
WHERE
A.UniqueID=B.UniqueID and
A.Date between '2019-09-01' and '2019-11-15' and
A.INpatient=1 and B.INpatient=1 AND
A.Date < B.Date AND
DATEDIFF( B.Date, A.DischargeDate) <= 28
UNION
SELECT B.* FROM Interview AS A, Interview AS B
WHERE

A.UniqueID=B.UniqueID and
A.Date between '2019-09-01' and '2019-11-15' and
A.INpatient=1 and B.INpatient=1 AND
B.Date > A.Date AND
DATEDIFF(B.Date, A.DischargeDate) <= 28​

ORDER BY `UniqueID`, `Date` ASC

thanks a million

 

[le_toubib]

speed significantly improved after specifying necessary fields and omitting the rest