Link to home
Start Free TrialLog in
Avatar of biotec
biotec

asked on

SQL query or SP that combines patient meds, allergies and problems

I need an output file(s) that selects from a single Intake table/template in our EHR from a single most recent visit that equals @CurrentDate. However this single row return also needs to include all the meds, allergies and problems they have. Can this be done as a single output file? Once I have this I need to utilize it in an SSIS package to loop through all patients that meet the criteria.


I can include detail of the tables but for now just not sure how to even go about this. A UNION, or nested select statements? Not sure.  This can be done as separate files as well that breaks out meds, allergies etc. so that each one saves to a csv file with the unique patient identifier tying them together. Maybe a temp table could work as well.

Appreciate any help.
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Can u send the input data and the expected output.
Avatar of biotec
biotec

ASKER

In what format? I can send the table fields I need as output and sample data that would be the input. Sorry, not all that familiar with protocol here. Thanks
Excel pls. If you get the create table and insert scripts that would be more perfect
Avatar of biotec

ASKER

I uploaded the tables used and the queries I wrote to pull the different data. My first issue is that I need to use the appointments table and only pull patients who have an appointment that equals the current date. I want to use the GetDate() function to compare to either a timestamp or YYYYMMDD (appt_date) so it only uses that patient if they had a visit today.

Beyond that, I think most of my work will be in SSIS as I need to basically run each of the queries in the Queries used tab and save the data to a csv file. I probably need to post a different question related to SSIS though I realize.
Queries_tables.xlsx
sample rows from all the tables and the expected output is missing. Where is the appointments table?
Avatar of biotec

ASKER

I did figure out the getdate() part.  I've uploaded the remaining queries and tables used in a new Excel file. Thanks
Queries_tables_rev2.xlsx
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.