Hi everyone
I would if anyone could give me some pointers as struggling a bit. My SQL query knowledge is not great. I need to tranlate this into DAX. and doing my research but goig around in circles a bit!!
the SQL query is:
select personview.forename, personview.surname,
wlocation.dateanswervalue as "Expiry Date from Last Carer Monitoring",
personOrgRelationshipView.organisationname as "Scheme",
publia.answervalue as "Expiry Date from Pre-Placement Checklist",
personview.personid
from personview
left join formanswerpersonview wlocation
on personview.personid=wlocation.answerforsubjectid
and designguid ='f98ab2f7-7010-4c93-b9de-2801066ccda8'
And controlname='certificateExpiryDate'
and instancestate='COMPLETE'
and wlocation.instanceid= (
select max (x.instanceid)
from formanswerpersonview x
where x.answerforsubjectid = wlocation.answerforsubjectid
and x.designguid = wlocation.designguid
and x.instancestate='COMPLETE'
)
left join formanswerpersonview publia
on personview.personid=publia.answerforsubjectid
and publia.designguid ='3f6d7b80-e1b6-4286-b6a9-803fc569706b'
And publia.controlname='date_21'
and publia.instancestate='COMPLETE'
and publia.instanceid= (
select max (x.instanceid)
from formanswerpersonview x
where x.answerforsubjectid = publia.answerforsubjectid
and x.designguid = publia.designguid
and x.instancestate='COMPLETE'
)
INNER join personOrgRelationshipView
on personview.personid=personOrgRelationshipView.personid
inner join classificationPersonView places
on places.personid=personview.personid
wHERE code='APP'
And status='ACTIVE'
AND endDate IS NULL
order by forename
Clearly the inner joins are fine but it is the left join i am unclear on? any help would be greatly appriciated, as i continue to research
Many thanks
Tim
