Seven Stars

Number of visits inside given date

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.

 

Tags (1)
1 ACCEPTED SOLUTION

Accepted Solutions
Seven Stars

Re: Number of visits inside given date

I am closing this, as talend doesn't have a proper solution to it. We have solved this issue by developing SQL.

19 REPLIES
Forteen Stars TRF
Forteen Stars

Re: Number of visits inside given date

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...


TRF
Seven Stars

Re: Number of visits inside given date

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.

Forteen Stars TRF
Forteen Stars

Re: Number of visits inside given date

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?


TRF
Seven Stars

Re: Number of visits inside given date

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. 

Five Stars

Re: Number of visits inside given date

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

Seven Stars

Re: Number of visits inside given date

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

Five Stars

Re: Number of visits inside given date

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

Seven Stars

Re: Number of visits inside given date

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.

Five Stars

Re: Number of visits inside given date

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

Five Stars

Re: Number of visits inside given date

ps add all the select items in the group by clause except for the aggregated items ie(count() sum() )

Seven Stars

Re: Number of visits inside given date

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'......

Five Stars

Re: Number of visits inside given date

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

Five Stars

Re: Number of visits inside given date

otherwise you will have to identify the where period for each patient group

Seven Stars

Re: Number of visits inside given date

these "<" and ">" also didn't worked.

Five Stars

Re: Number of visits inside given date

Show me your query - is it mssql?

Seven Stars

Re: Number of visits inside given date

not in mssql, but this should be in tMap's expression. Am i right?

Five Stars

Re: Number of visits inside given date

you need to run in tmssqlinput

intmap you can define what how you want to treat the data that comes from mssqlinput

Five Stars

Re: Number of visits inside given date

if you have several queries you can join them in tmap

Seven Stars

Re: Number of visits inside given date

I am closing this, as talend doesn't have a proper solution to it. We have solved this issue by developing SQL.