Number of visits inside given date

[Anonymous]

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.

Example:-

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.

[TRF]

Check the following post regarding dates comparisons with TalendDate routines

https://community.talend.com/t5/Design-and-Development/Between-AND-check-and-pass-the-value/td-p/124...

[Anonymous]

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.

[TRF]

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?

[Anonymous]

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. 

DAS8.jpg

[Anonymous]

Is the treatment period same as count(visits) between dateA and dateB where patient=A?

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

select patientNo,patientName, count(visitDate) as numberVisits

from mydatabase

where visitDate between '20180101' and '20180630'

groupby patientNo

this will give you all the patientsNo, patientName and the number of visits between the two dates you specify