Source & Destination - MSSQL
I got to join 3 tables to achieve the number of visits made by a Patients to the hospital between their treatment period.
1. IF Patient-A's treatment is between "01-JAN-2016" and "30-JUN-2016". Then we need to get how many visits he made to the hospital and the most recent visit date during the treatment period.
2. From the same source, IF Patient-B's treatment is between "17-APR-2017" and "18-SEP-2017". Then we need to get how many visits he made to the hospital and the most recent visit date during the treatment period..
How can we achieve this? Please suggest.
Solved! Go to Solution.
Check the following post regarding dates comparisons with TalendDate routines
Thanks TRF for replying back, the question in the the attached link was also raised by me. Its a same issue, not resolved yet. I closed the previous one as that work put on hold. Now it came back again with some extra requirement. Please guide.
You don't give enough details.
Where are you with the job design?
Can you share it?
Why do you need 3 tables?
With which particular point are you stuck?
Attached the graph which i am working on. Since this request is the hard part i am just playing with lots of options to get the result. Once this is done then i will do the rest part. 3 tables bcz, table 1 is having patient details, table-2 is having diagnosis details, and table-3 is having billing details. I will be picking some key fields from all 3 tables but the main part is, to get the number of visits during the treatment period. How to get this if the treatment period are different for every patients? Here i got stuck.
treatment period will have start and end date of treatment. We need to determine how many visits made inside this treatment period by a single patient
I'm confused by 'treatment period'. If it refers to just visits by a patient my query above should work
If treatment period is a period determined by a dr subject to a vist, it should be recorded either by value or obtained by count
Doctor will give patients a 6 month or 1 year treatment procedure. There is a column which records DATE of a patients visit. We have to take the count of those date and display in the output.
select patientNo,patientName, count(visitDate) as numberVisits
where visitDate between '20180101' and '20180630'
this will give you all the patientsNo, patientName and the number of visits between the two dates you specify
absolutely, but this between/and function is not present in Talend. Main thing is, every patient will have different visitDate period. In your example the treatment period is between '20180101' and '20180630'. For some XYZ person, the treatment period will be between '20180417' and '20181017'......
I've used between in talend before
you can use where visitDate > whatever and visitDate < whatever
you can only use one where clause for visitDate per query
Watch the recorded webinar!
Create systems and workflow to manage clean data ingestion and data transformation.
Introduction to Talend Open Studio for Data Integration.
Test drive Talend's enterprise products.