I have a table that has columns visit_no, mr_no, admit_date, admit_time, dischg_date, dischg_time, and other columns that aren't necessary for the query. mr_no is the main identifier of the patient, a patient can have multiple visit_no with their respective admit / discharge dates. I almost found a solution but the query I have so far is inconsistent and giving me multiple visit_no for some entries when there should only be one. Here is an example of the table for a specific patient using their mr_no to query, the table name is chmadmtr:

Query:

SELECT 
    visit_no, mr_no, admit_date, 
    dischg_date, 
FROM
    chmadmtr
WHERE
    mr_no = '508'
ORDER BY admit_date ASC

Now what I would like is a query that checks the previous visit_no, and checks to see if they have been admitted before 30 days of their previous discharge. This query I have adds two columns at the end, one named 'Readmit' that will be either 'Y' (yes, they have been readmitted 30 days before previous discharge), or 'N' (No, they have not been readmitted 30 days before the previous discharge); and a column 'Days_between', that will give a count of how many days in between the admission and previous discharge, if it is a readmission before 30 days.

Here is the query:

SELECT 
    chmadmtr.visit_no, chmadmtr.mr_no, chmadmtr.admit_date,
    chmadmtr.dischg_date,
    IF(DATEDIFF(chmadmtr.admit_date, previ.dischg_date) < 30, 'Y', 'N') 
    Readmit,
    DATEDIFF(chmadmtr.admit_date, previ.dischg_date) Days_Between

FROM chmadmtr 
LEFT JOIN chmadmtr previ  ON chmadmtr.mr_no = previ.mr_no
    AND DATEDIFF(chmadmtr.admit_date, previ.dischg_date) BETWEEN 1 AND 29
WHERE chmadmtr.mr_no = '508'
ORDER BY admit_date ASC

and Output:

For some reason I'm getting some duplicate visit_no columns, the original query has 47 records so this query should also have the same amount, but the second query gives me 73 records. Some visit_no even repeat 3 times. I notice it's always the last of the duplicate columns that is correct with the 'Days_between' column, comparing it to the previous admission. I don't understand why I'm getting duplicate columns if I'm Left Joining the table to itself using an alias. Any insights would be appreciated, maybe a different method might be better.

CodePudding user response：

I took into account you would need to do it with more than one patient at a time.

select *
      ,case when datediff(admit_date,lag(dischg_date) over(partition by mr_no order by dischg_date))  <= 30 then 'Y' else 'N' end  as Readmit
      ,datediff(admit_date,lag(dischg_date) over(partition by mr_no order by dischg_date)) as Days_between 
from t

Fiddle