SHOW:
|
|
- or go back to the newest paste.
| 1 | select a.id_patient, concat(p.city_code,"/",p.hospital_code) as site, p.patient_number as stcode, | |
| 2 | IF(c.club_type = 1,tm.last_name,ti.last_name) as Nom , | |
| 3 | IF(c.club_type = 1,tm.first_name,ti.first_name) as Prenom, | |
| 4 | IF(c.club_type = 1,"Female",IF(ti.gender=1,"Male",IF(ti.gender=2,"Female","Unknown"))) as sexe, | |
| 5 | IF(c.club_type = 1,tm.dob,ti.dob) as 'Date de naissance', | |
| 6 | c.name as 'Nom du club', | |
| 7 | ct.name as 'Type de club', | |
| 8 | IF(c.club_type=1,tm.telephone, IF(ooc.carer_telephone is null or ooc.carer_telephone ="",ti.carer_telephone, ooc.carer_telephone ) ) as 'no. tÊl', | |
| 9 | IF(c.club_type=1,tm.address, IF(ooc.carer_address is null or ooc.carer_address ="",ti.carer_address, ooc.carer_address ) ) as 'Adresse', | |
| 10 | IF(c.club_type = 1,oop.mother_gps,ooc.gps) as gps, | |
| 11 | IF(c.club_type = 1,oop.location,ooc.location) as location | |
| 12 | from caris_db.club_patient a | |
| 13 | left join caris_db.patient p on a.id_patient=p.id | |
| 14 | left join caris_db.club c on c.id= a.id_club | |
| 15 | left join caris_db.lookup_club_type ct on ct.id= c.club_type | |
| 16 | left join caris_db.tracking_infant ti on p.id=ti.id_patient | |
| 17 | left join caris_db.tracking_motherbasicinfo tm on tm.id_patient=p.id | |
| 18 | left join openfn.odk_child_visit ooc ON ( ooc.patient_code = p.patient_code OR SUBSTRING_INDEX(ooc.caseId,'_',-1) = p.id) | |
| 19 | left join openfn.odk_pregnancy_visit oop ON ( oop.health_id = p.patient_code OR SUBSTRING_INDEX(oop.caseId,'_',-1) = p.id) | |
| 20 | where c.id_hospital in (73,74,75,150,155) and concat(p.city_code,"/",p.hospital_code) is not null | |
| 21 | group by a.id_patient order by c.club_type, concat(p.city_code,"/",p.hospital_code) |
