Questions

Help with a SQL query

+
0 Votes
Locked

Help with a SQL query

jimmy-jam
Wondering if someone can help me write a SQL query. I have a table that contains a bunch of patient data. Each patient has a medical record number assigned to a study. Each study has a unique study reference number which is the primary key. Long story short, I need to write a query that returns one line for each unique medical record number with some other data. Basically like this.

SELECT patient_id, patient_name, DOB from table

this is where I get stuck, I need to somehow incorporate some syntax where I only get one entry per unique or distinct patient id. Hopefully that makes sense.
  • +
    0 Votes
    LocoLobo

    Are you looking strictly for distinct rows for patient ids, names and DOB? Or are you looking for something else?

    +
    0 Votes
    jimmy-jam

    It's a single table. I only want one result per Patient ID.

    +
    0 Votes
    LocoLobo

    Select distinct id, name, DOB from table

    +
    0 Votes
    ispyty

    SELECT patient_id, patient_name, DOB
    FROM table
    GROUP BY patient_id

    Is that what you were looking for?

    +
    0 Votes
    jimmy-jam

    That query wouldn't give me only one line per patient_id.

    +
    2 Votes
    bsongy

    I have no tech advise, but be careful. You are, without the proper permissions, dancing dangerously close to a violation of HIPAA's Patient Privacy rules. The joint commission could potentially see some of this as a violation. Check with your HIM department, or Enterprise/EPIC departments.

    +
    0 Votes
    mychitterman

    It could just be his assignment for school! However, I am very happy that you have identified this. Health Care Info System workers need to be on Top the Biz Analytic path.

    +
    0 Votes
    jimmy-jam

    I appreciate your concern and they are duly noted. I work for a medical imaging company and am well aware of HIPAA and my responsibility to protect the privacy of patients. I would never dream of posting and Personally Identifiable Information on a public forum.

    +
    0 Votes
    AssemblerRookie

    for a customer ID in a table(s) will return multiple records, which it should do, in a pure SQL query. What you need to do is post process this returned data to display in your user interface (front end).
    EG Table "Data"
    Cust_ID Cust_Name Cust_DOB Cust_MisInfo
    1 A Name 5/5/AYear 2 Yr Contract
    1 A Name 5/5/AYear Terminated
    2 B Name 4/4/AYear 1 Yr Contract
    SQL Statement "SELECT Cust_ID,Cust_Name,Cust_DOB,Cust_MisInfo FROM Data WHERE Cust_ID = 1;"
    The statement above will return two records as it appears in the table(s).

    +
    0 Votes
    phil232

    will always output 2 lines in a query involving patient and study data. If you want to list that in a (HTML, f.ex.) table you have to execute one query to get all patients and then for each patient you have to SELECT * FROM Studies WHERE Studies.Cust_ID = Patients.Cust_ID

    But still consider bsongy's comment regarding joining patient data with study results. You may quite fast finish with a big fine whenever (and that's most of the time) the patient's (study) data are protected and have to be kept in a way that the study result remains absolutely anonymous.

    +
    0 Votes
    jimmy-jam

    Thanks, as I stated to bsongy. This is part of a requested report from a customer of mine that happens to be a hospital. I work for the vendor that supplied the equipment. The customer has merely requested that I do some data mining for them. The requesting department already has full access to the data legally as do I. I do appreciate you guys bringing it up though in the off chance that someone else may stumble across this thread. It never hurts to err on the side of caution.

    +
    0 Votes
    phil232

    you make a weird pivot queries that puts all study data on the same line as the patient data.

    Not recommended :)

    +
    0 Votes
    HenryGB

    Hello,
    I use to do data analysis for a large medical research organisation. I assume the database if fully normalised. So you just need to query the patient table for the details and then in the from part of the query use an "exists" or "in" sub-query to only show those patients with a valid medical record number in the study table. Without more details I can't help you further but you may need to use something like a max() value in the sub-query if the sub query returns more than one row. Do not try to join the tables as this will cause problems. Hope this helps.

  • +
    0 Votes
    LocoLobo

    Are you looking strictly for distinct rows for patient ids, names and DOB? Or are you looking for something else?

    +
    0 Votes
    jimmy-jam

    It's a single table. I only want one result per Patient ID.

    +
    0 Votes
    LocoLobo

    Select distinct id, name, DOB from table

    +
    0 Votes
    ispyty

    SELECT patient_id, patient_name, DOB
    FROM table
    GROUP BY patient_id

    Is that what you were looking for?

    +
    0 Votes
    jimmy-jam

    That query wouldn't give me only one line per patient_id.

    +
    2 Votes
    bsongy

    I have no tech advise, but be careful. You are, without the proper permissions, dancing dangerously close to a violation of HIPAA's Patient Privacy rules. The joint commission could potentially see some of this as a violation. Check with your HIM department, or Enterprise/EPIC departments.

    +
    0 Votes
    mychitterman

    It could just be his assignment for school! However, I am very happy that you have identified this. Health Care Info System workers need to be on Top the Biz Analytic path.

    +
    0 Votes
    jimmy-jam

    I appreciate your concern and they are duly noted. I work for a medical imaging company and am well aware of HIPAA and my responsibility to protect the privacy of patients. I would never dream of posting and Personally Identifiable Information on a public forum.

    +
    0 Votes
    AssemblerRookie

    for a customer ID in a table(s) will return multiple records, which it should do, in a pure SQL query. What you need to do is post process this returned data to display in your user interface (front end).
    EG Table "Data"
    Cust_ID Cust_Name Cust_DOB Cust_MisInfo
    1 A Name 5/5/AYear 2 Yr Contract
    1 A Name 5/5/AYear Terminated
    2 B Name 4/4/AYear 1 Yr Contract
    SQL Statement "SELECT Cust_ID,Cust_Name,Cust_DOB,Cust_MisInfo FROM Data WHERE Cust_ID = 1;"
    The statement above will return two records as it appears in the table(s).

    +
    0 Votes
    phil232

    will always output 2 lines in a query involving patient and study data. If you want to list that in a (HTML, f.ex.) table you have to execute one query to get all patients and then for each patient you have to SELECT * FROM Studies WHERE Studies.Cust_ID = Patients.Cust_ID

    But still consider bsongy's comment regarding joining patient data with study results. You may quite fast finish with a big fine whenever (and that's most of the time) the patient's (study) data are protected and have to be kept in a way that the study result remains absolutely anonymous.

    +
    0 Votes
    jimmy-jam

    Thanks, as I stated to bsongy. This is part of a requested report from a customer of mine that happens to be a hospital. I work for the vendor that supplied the equipment. The customer has merely requested that I do some data mining for them. The requesting department already has full access to the data legally as do I. I do appreciate you guys bringing it up though in the off chance that someone else may stumble across this thread. It never hurts to err on the side of caution.

    +
    0 Votes
    phil232

    you make a weird pivot queries that puts all study data on the same line as the patient data.

    Not recommended :)

    +
    0 Votes
    HenryGB

    Hello,
    I use to do data analysis for a large medical research organisation. I assume the database if fully normalised. So you just need to query the patient table for the details and then in the from part of the query use an "exists" or "in" sub-query to only show those patients with a valid medical record number in the study table. Without more details I can't help you further but you may need to use something like a max() value in the sub-query if the sub query returns more than one row. Do not try to join the tables as this will cause problems. Hope this helps.