topic Re: Number of visits inside given date in Talend Studio

Number of visits inside given date

Anonymous — Mon, 02 Jul 2018 09:45:47 GMT

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.

Re: Number of visits inside given date

TRF — Mon, 02 Jul 2018 10:03:21 GMT

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/124991

Re: Number of visits inside given date

Anonymous — Mon, 02 Jul 2018 10:10:26 GMT

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.

Re: Number of visits inside given date

TRF — Mon, 02 Jul 2018 10:18:16 GMT

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?

Re: Number of visits inside given date

Anonymous — Mon, 02 Jul 2018 10:59:46 GMT

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

Re: Number of visits inside given date

Anonymous — Tue, 03 Jul 2018 10:17:08 GMT

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

Re: Number of visits inside given date

Anonymous — Tue, 03 Jul 2018 10:25:09 GMT

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

Re: Number of visits inside given date

Anonymous — Tue, 03 Jul 2018 12:38:41 GMT

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

Re: Number of visits inside given date

Anonymous — Tue, 03 Jul 2018 12:45:35 GMT

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.

Re: Number of visits inside given date

Anonymous — Tue, 03 Jul 2018 13:26:01 GMT

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

Re: Number of visits inside given date

Anonymous — Tue, 03 Jul 2018 13:27:38 GMT

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

Re: Number of visits inside given date

Anonymous — Tue, 03 Jul 2018 13:30:25 GMT

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

Re: Number of visits inside given date

Anonymous — Tue, 03 Jul 2018 13:34:31 GMT

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

Re: Number of visits inside given date

Anonymous — Tue, 03 Jul 2018 13:37:00 GMT

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

Re: Number of visits inside given date

Anonymous — Tue, 03 Jul 2018 13:46:21 GMT

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

Re: Number of visits inside given date

Anonymous — Wed, 04 Jul 2018 10:36:08 GMT

Show me your query - is it mssql?

Re: Number of visits inside given date

Anonymous — Wed, 04 Jul 2018 10:40:42 GMT

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

Re: Number of visits inside given date

Anonymous — Wed, 04 Jul 2018 11:29:22 GMT

you need to run in tmssqlinput

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

Re: Number of visits inside given date

Anonymous — Wed, 04 Jul 2018 11:30:31 GMT

if you have several queries you can join them in tmap

Re: Number of visits inside given date

Anonymous — Wed, 04 Jul 2018 11:37:30 GMT

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