B4J Question SQL query

le_toubib · Nov 16, 2019

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 ..

B4X:

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 · Nov 16, 2019

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 · Nov 16, 2019

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

Jorge M A · Nov 16, 2019

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

B4X:

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 · Nov 16, 2019

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

le_toubib · Nov 16, 2019

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 · Nov 16, 2019

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

B4X:

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 · Nov 17, 2019

le_toubib said:
-------------------------------------------------------
|Admission_Date | Discharge_Date | UniqueID |
-------------------------------------------------------

This does not correspond to the first query you post.

le_toubib said:
this query is very slow (27 seconds )

B4X:

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.

B4X:

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 · Nov 17, 2019

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 · Nov 17, 2019

@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 · Nov 17, 2019

speed significantly improved after specifying necessary fields and omitting the rest

B4J Question SQL query

le_toubib

Active Member

Jorge M A

Well-Known Member

le_toubib

Active Member

Jorge M A

Well-Known Member

Jorge M A

Well-Known Member

le_toubib

Active Member

le_toubib

Active Member

Jorge M A

Well-Known Member

le_toubib

Active Member

le_toubib

Active Member

le_toubib

Active Member

Similar Threads

B4J Question SQL query

Active Member

Well-Known Member

Active Member

Well-Known Member

Well-Known Member

Active Member

Active Member

Well-Known Member

Active Member

Active Member

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency