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.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Show me your query - is it mssql?

[Anonymous]

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

[Anonymous]

you need to run in tmssqlinput

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

[Anonymous]

if you have several queries you can join them in tmap

[Anonymous · Accepted solution]

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