biotec
asked on
SQL or SSIS job/stored procedure to extract patient data nightly and send to file
If a patient has a certain type of visit I need to extract all their current data and send it to a file for all patients who have that visit type. I will pull mostly from one table that has their intake info, however I also have to pull all medications, problems and allergies. That makes it something where it would be difficult to just do joins on multiple tables. There could be 20 meds and 5 allergies etc.
What is the best method of doing this. I'm not a SQL expert. Maybe a stored procedure that uses multiple temp tables but that might be beyond my skill level.
I have created SSIS packages that pull data and dump to a file but in this case it gets much tougher because I have to run the job or procedure and loop it until there are no more patients that day that had this visit type and pull have it pull all their data and dump each to a file. I'd like to pull each file and name it with a medical record number and drop it into a folder that houses them all so that it could be pulled via sftp. Thoughts?
What is the best method of doing this. I'm not a SQL expert. Maybe a stored procedure that uses multiple temp tables but that might be beyond my skill level.
I have created SSIS packages that pull data and dump to a file but in this case it gets much tougher because I have to run the job or procedure and loop it until there are no more patients that day that had this visit type and pull have it pull all their data and dump each to a file. I'd like to pull each file and name it with a medical record number and drop it into a folder that houses them all so that it could be pulled via sftp. Thoughts?
Pawan Kumar
Yes database , SQL Server is a the best place to do this. I would recommend that please provide few rows and the expected data you need.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you both. I will try to provide more info or may go the ssis route and look at these links. I know I will have a great deal of questions but probably need to post those in another question once I start to attempt this.
My problem is trying to combine the simple query of a single row table (that sits behind a template in our EHR) with meds, allergies etc. that have many rows?
My problem is trying to combine the simple query of a single row table (that sits behind a template in our EHR) with meds, allergies etc. that have many rows?