Ideas of how to improve this query
SELECT `UniqueNumber-900` AS `ClientNumber`,
(SELECT `AdmitDate`
FROM `cl_clientsvcs_dayhab`
WHERE `ClientCommonNumber` = `cl_client_common`.`UniqueNumber-900`
ORDER BY `AdmitDate` ASC
LIMIT 1)
AS `InitialAdmitDate`,
CONCAT(`cl_client_common`.`LastName`, ', ', `cl_client_common`.`FirstName`, ' ', `cl_client_common`.`MiddleName`)
AS `ClientName`,
`xx_component_master`.`ComponentCode`,
`MedicaidNumber`,
`MedicareNumber`,
CONCAT(`cl_client_common`.`Address1`, IF(`cl_client_common`.`Address2` = '', '', CONCAT(', ', `cl_client_common`.`Address2`)))
AS `Address1`,
CONCAT(`cl_client_common`.`City`, ', ', `client_state`.`StateAbbrev`, ' ', `cl_client_common`.`ZipCode`)
AS `CityStateZip`,
CONCAT(`cl_client_common`.`PhoneNumber`, IF(`cl_client_common`.`PhoneNumber2` <> '', CONCAT('; ', `cl_client_common`.`PhoneNumber2`), ''))
AS `ClientPhone`,
`cl_client_common`.`PhoneNumber2` AS `ClientPhone2`,
`cl_client_common`.`DateOfBirth`,
`Gender`,
`cl_client_common`.`SocialSecurityNumber`,
`DDSNumber`,
LEFT(`xx_marital_status`.`Description`, 1) AS `MaritalStatus`,
LEFT(`xx_race_master`.`Description`, 1) AS `Race`,
`xx_legal_status`.`Description` AS `LegalStatus`,
CONCAT(`xx_sc_master`.`LastName`, ', ', `xx_sc_master`.`FirstName`) AS `ServiceCoordinator`,
`cl_clientsvcs_dayhab`.`AdmitDate`,
`cl_client_guardians`.`SelfGuardian`,
`xx_county_master`.`CountyName`,
CASE `Guardian1`.`NameFormat`
WHEN 1 THEN
CONCAT(`Guardian1`.`FirstName1`, ' ', `Guardian1`.`LastName1`)
WHEN 2 THEN
CONCAT(`Guardian1`.`FirstName1`, ' & ', `Guardian1`.`FirstName2`, ' ', `Guardian1`.`LastName1`)
WHEN 3 THEN
CONCAT(`Guardian1`.`FirstName1`, ' ', `Guardian1`.`LastName1`, ' & ', `Guardian1`.`FirstName2`,
' ', `Guardian1`.`LastName2`)
END
AS `Guardian1Name`,
CONCAT(`Guardian1`.`Address1`, ', ',
IF(`Guardian1`.`Address2` = '', '', CONCAT(`Guardian1`.`Address2`, ', ')), `Guardian1`.`City`,
', ', `guardian1_state`.`StateAbbrev`,
' ', `Guardian1`.`ZipCode`)
AS `Guardian1Address`,
`Guardian1`.`HomePhone1` AS `Guardian1Phone`,
CASE `Guardian1`.`NameFormat`
WHEN 1 THEN
`Guardian1`.`WorkPhone1`
ELSE
CONCAT(IF(`Guardian1`.`WorkPhone1` <> '', CONCAT(`Guardian1`.`FirstName1`, ': ', `Guardian1`.`WorkPhone1`), ''),
IF(`Guardian1`.`WorkPhone1` <> ''
AND `Guardian1`.`WorkPhone2` <> '',
'; ',
''),
IF(`Guardian1`.`WorkPhone2` <> '', CONCAT(`Guardian1`.`FirstName2`, ': ', `Guardian1`.`WorkPhone2`), ''))
END
AS `Guardian1WorkPhone`,
CASE `Guardian1`.`NameFormat`
WHEN 1 THEN
`Guardian1`.`CellPhone1`
ELSE
CONCAT(IF(`Guardian1`.`CellPhone1` <> '', CONCAT(`Guardian1`.`FirstName1`, ': ', `Guardian1`.`CellPhone1`), ''),
IF(`Guardian1`.`CellPhone1` <> ''
AND `Guardian1`.`CellPhone2` <> '',
'; ',
''),
IF(`Guardian1`.`CellPhone2` <> '', CONCAT(`Guardian1`.`FirstName2`, ': ', `Guardian1`.`CellPhone2`), ''))
END
AS `Guardian1Cell`,
CASE `Guardian1`.`NameFormat`
WHEN 1 THEN
`Guardian1`.`WorkPlace1`
ELSE
CONCAT(IF(`Guardian1`.`WorkPlace1` <> '', CONCAT(`Guardian1`.`FirstName1`, ': ', `Guardian1`.`WorkPlace1`), ''),
IF(`Guardian1`.`WorkPlace1` <> ''
AND `Guardian1`.`WorkPlace2` <> '',
'; ',
''),
IF(`Guardian1`.`WorkPlace2` <> '', CONCAT(`Guardian1`.`FirstName2`, ': ', `Guardian1`.`WorkPlace2`), ''))
END
AS `WorkPlace1`,
CASE `Guardian1`.`NameFormat`
WHEN 1 THEN
`Guardian1`.`WorkHours1`
ELSE
CONCAT(IF(`Guardian1`.`WorkHours1` <> '', CONCAT(`Guardian1`.`FirstName1`, ': ', `Guardian1`.`WorkHours1`), ''),
IF(`Guardian1`.`WorkHours1` <> ''
AND `Guardian1`.`WorkHours2` <> '',
'; ',
''),
IF(`Guardian1`.`WorkHours2` <> '', CONCAT(`Guardian1`.`FirstName2`, ': ', `Guardian1`.`WorkHours2`), ''))
END
AS `WorkHours1`,
`Guardian1Relation`.`Description` AS `Guardian1Relation`,
CASE `Guardian2`.`NameFormat`
WHEN 1 THEN
CONCAT(`Guardian2`.`FirstName1`, ' ', `Guardian2`.`LastName1`)
WHEN 2 THEN
CONCAT(`Guardian2`.`FirstName1`, ' & ', `Guardian2`.`FirstName2`, ' ', `Guardian2`.`LastName1`)
WHEN 3 THEN
CONCAT(`Guardian2`.`FirstName1`, ' ', `Guardian2`.`LastName1`, ' & ', `Guardian2`.`FirstName2`,
' ', `Guardian2`.`LastName2`)
END
AS `Guardian2Name`,
CONCAT(`Guardian2`.`Address1`, ', ',
IF(`Guardian2`.`Address2` = '', '', CONCAT(`Guardian2`.`Address2`, ', ')), `Guardian2`.`City`,
', ', `guardian2_state`.`StateAbbrev`,
' ', `Guardian2`.`ZipCode`)
AS `Guardian2Address`,
`Guardian2`.`HomePhone1` AS `Guardian2Phone`,
CASE `Guardian2`.`NameFormat`
WHEN 1 THEN
`Guardian2`.`WorkPhone1`
ELSE
CONCAT(IF(`Guardian2`.`WorkPhone1` <> '', CONCAT(`Guardian2`.`FirstName1`, ': ', `Guardian2`.`WorkPhone1`), ''),
IF(`Guardian2`.`WorkPhone1` <> ''
AND `Guardian2`.`WorkPhone2` <> '',
'; ',
''),
IF(`Guardian2`.`WorkPhone2` <> '', CONCAT(`Guardian2`.`FirstName2`, ': ', `Guardian2`.`WorkPhone2`), ''))
END
AS `Guardian2WorkPhone`,
CASE `Guardian2`.`NameFormat`
WHEN 1 THEN
`Guardian2`.`CellPhone1`
ELSE
CONCAT(IF(`Guardian2`.`CellPhone1` <> '', CONCAT(`Guardian2`.`FirstName1`, ': ', `Guardian2`.`CellPhone1`), ''),
IF(`Guardian2`.`CellPhone1` <> ''
AND `Guardian2`.`CellPhone2` <> '',
'; ',
''),
IF(`Guardian2`.`CellPhone2` <> '', CONCAT(`Guardian2`.`FirstName2`, ': ', `Guardian2`.`CellPhone2`), ''))
END
AS `Guardian2Cell`,
CASE `Guardian2`.`NameFormat`
WHEN 1 THEN
`Guardian2`.`WorkPlace1`
ELSE
CONCAT(IF(`Guardian2`.`WorkPlace1` <> '', CONCAT(`Guardian2`.`FirstName1`, ': ', `Guardian2`.`WorkPlace1`), ''),
IF(`Guardian2`.`WorkPlace1` <> ''
AND `Guardian2`.`WorkPlace2` <> '',
'; ',
''),
IF(`Guardian2`.`WorkPlace2` <> '', CONCAT(`Guardian2`.`FirstName2`, ': ', `Guardian2`.`WorkPlace2`), ''))
END
AS `WorkPlace11`,
CASE `Guardian2`.`NameFormat`
WHEN 1 THEN
`Guardian2`.`WorkHours1`
ELSE
CONCAT(IF(`Guardian2`.`WorkHours1` <> '', CONCAT(`Guardian2`.`FirstName1`, ': ', `Guardian2`.`WorkHours1`), ''),
IF(`Guardian2`.`WorkHours1` <> ''
AND `Guardian2`.`WorkHours2` <> '',
'; ',
''),
IF(`Guardian2`.`WorkHours2` <> '', CONCAT(`Guardian2`.`FirstName2`, ': ', `Guardian2`.`WorkHours2`), ''))
END
AS `WorkHours2`,
`Guardian2Relation`.`Description` AS `Guardian2Relation`,
IF(`cl_client_guardians`.`SurrogateNum` = 0, 'None', CONCAT(`xx_surrogate_master`.`LastName`, ', ', `xx_surrogate_master`.`FirstName`))
AS `SurrogateName`,
CONCAT(`xx_surrogate_master`.`Address1`,
', ',
IF(`xx_surrogate_master`.`Address2` = '', '', CONCAT(`xx_surrogate_master`.`Address2`, ', ')),
`xx_surrogate_master`.`City`,
', ',
`guardian1_state`.`StateAbbrev`,
' ',
`xx_surrogate_master`.`ZipCode`)
AS `SurrogateAddress`,
`xx_surrogate_master`.`Phone1` AS `SurrogatePhone`,
`PHC`.`Description` AS `PrimaryHandiCond`,
`SHC`.`Description` AS `SecondaryHandiCond`,
CASE `cl_client_medical`.`Seizures`
WHEN 'Y' THEN 'Yes'
WHEN 'N' THEN 'No'
WHEN 'R' THEN 'At Risk'
END
AS `Seizures`,
`xx_language_master`.`Description` AS `PrimaryLanguage`,
`cl_client_medical`.`Allergies`,
`cl_client_medical`.`Equipment`,
`cl_client_medical`.`Medications`,
CONCAT(CONVERT(`SpecialDiet`, CHAR),
IF(CHAR_LENGTH(`SpecialDiet`) > 0, '; ', ''),
IF(`TubeFed` = 0, '', 'Tube Fed'),
IF(`TubeFed` <> 0, IF(`TFServedMeals` = 0, '-Not Served Meals; ', '-Served Meals; '), ''),
IF(`SDBottleFeed` = 1, 'Bottle Feed; ', ''),
IF(`SDPureed` = 1, 'Pureed; ', ''),
IF(`SDChopped` = 1, 'Chopped; ', ''),
IF(`SDGround` = 1, 'Ground; ', ''),
IF(`SDPureedChopped` = 1, 'Half Pureed/Half Chopped; ', ''),
IF(`SDHalfPureedGround` = 1, 'Half Pureed/Half Ground; ', ''),
IF(`LiquidConsistancy` = '',
'',
CONCAT('Liquid: ',
CASE `LiquidConsistancy`
WHEN 'T' THEN 'Thin; '
WHEN 'N' THEN 'Nectar; '
WHEN 'H' THEN 'Honey; '
WHEN 'P' THEN 'Pudding; '
WHEN 'E' THEN 'Nectar Plus; '
END)))
AS `SpecialDiet`,
IF(`ThickenerUsed` = 'N',
'',
CONCAT('Thickener Used: ',
CASE `ThickenerUsed`
WHEN 'S' THEN 'Simply Thick'
WHEN 'R' THEN 'Rice Cereal'
WHEN 'T' THEN 'Thick It'
WHEN 'O' THEN CONCAT('Other: ', `ThickenerUsedOther`)
END,
IF(`AmountMethod` = ''
OR `AmountMethod` IS NULL,
'',
CONCAT(' Amount/Method: ', `AmountMethod`))))
AS `ThickenerUsedInfo`,
`cl_client_dietary`.`Reason`,
`cl_client_dietary`.`DietLimitations`,
IF(`cl_client_medical`.`PrimaryPhysicianNum` = 0, 'None', CONCAT(`primaryphysician`.`LastName`, ', ', `primaryphysician`.`FirstName`))
AS `PrimaryPhysicianName`,
CONCAT(`primaryphysician`.`Address1`,
', ',
IF(`primaryphysician`.`Address2` = '', '', CONCAT(`primaryphysician`.`Address2`, ', ')),
`primaryphysician`.`City`,
', ',
`primaryphysician_state`.`StateAbbrev`,
' ',
`primaryphysician`.`ZipCode`)
AS `PrimaryPhysicianAddress`,
`primaryphysician`.`Phone1` AS `PrimaryPhysicianPhone`,
IF(`cl_client_medical`.`SecondPhysicianNum` = 0, 'None', CONCAT(`secondphysician`.`LastName`, ', ', `secondphysician`.`FirstName`))
AS `secondphysicianName`,
CONCAT(`secondphysician`.`Address1`,
', ',
IF(`secondphysician`.`Address2` = '', '', CONCAT(`secondphysician`.`Address2`, ', ')),
`secondphysician`.`City`,
', ',
`secondphysician_state`.`StateAbbrev`,
' ',
`secondphysician`.`ZipCode`)
AS `secondphysicianAddress`,
`secondphysician`.`Phone1` AS `SecondPhysicianPhone`,
IF(`cl_client_medical`.`HospitalNum` = 0, 'None', `xx_hospital_master`.`HospitalName`) AS `HospitalName`,
`xx_hospital_master`.`Phone1` AS `HospitalPhone`,
CASE `emergencycontact`.`NameFormat`
WHEN 1 THEN
CONCAT(`emergencycontact`.`FirstName1`, ' ', `emergencycontact`.`LastName1`)
WHEN 2 THEN
CONCAT(`emergencycontact`.`FirstName1`, ' & ', `emergencycontact`.`FirstName2`, ' ', `emergencycontact`.`LastName1`)
WHEN 3 THEN
CONCAT(`emergencycontact`.`FirstName1`, ' ', `emergencycontact`.`LastName1`, ' & ',
`emergencycontact`.`FirstName2`, ' ', `emergencycontact`.`LastName2`)
END
AS `EmergencyName`,
CONCAT(`emergencycontact`.`Address1`,
', ',
IF(`emergencycontact`.`Address2` = '', '', CONCAT(`emergencycontact`.`Address2`, ', ')),
`emergencycontact`.`City`,
', ',
`emergency_state`.`StateAbbrev`,
' ',
`emergencycontact`.`ZipCode`)
AS `EmergencyAddress`,
CONCAT(IF(`emergencycontact`.`HomePhone1` = '', '', CONCAT('Home:', `emergencycontact`.`HomePhone1`, ' ')), IF(`emergencycontact`.`WorkPhone1` = '', '', CONCAT('Work:', `emergencycontact`.`WorkPhone1`, ' ')), IF(`emergencycontact`.`CellPhone1` = '', '', CONCAT('Cell:', `emergencycontact`.`CellPhone1`)))
AS `EmergPhoneNumbers1`,
CONCAT(IF(`emergencycontact`.`HomePhone2` = '', '', CONCAT('Home:', `emergencycontact`.`HomePhone2`, ' ')), IF(`emergencycontact`.`WorkPhone2` = '', '', CONCAT('Work:', `emergencycontact`.`WorkPhone2`, ' ')), IF(`emergencycontact`.`CellPhone2` = '', '', CONCAT('Cell:', `emergencycontact`.`CellPhone2`)))
AS `EmergPhoneNumbers2`,
`emergencyrelation`.`Description` AS `EmergencyRelation`,
CASE `cl_clientsvcs_extcare`.`AttendsEC` WHEN 1 THEN 'Yes' ELSE 'No' END AS `ExtendedCare`,
CONCAT(`xx_component_master`.`Description`, IF(`xx_classroom_master`.`ClassroomName` IS NULL, '', CONCAT('/', `xx_classroom_master`.`ClassroomName`)))
AS `ComponentClassroomBreak`,
CAST(CONCAT(`cl_client_common`.`LastName`, `cl_client_common`.`FirstName`, `cl_client_common`.`MiddleName`, `cl_client_common`.`UniqueNumber-900`) AS CHAR)
AS `ClientNameBreak`,
`HabFunding`.`Description` AS `HabFunding`,
'BRIANR' AS `PrintedBy`
FROM`cl_client_common`
LEFT JOIN `cl_clientsvcs_dayhab` AS `cl_clientsvcs_dayhab`
ON `cl_clientsvcs_dayhab`.`ClientCommonNumber` = `cl_client_common`.`UniqueNumber-900`
AND `cl_clientsvcs_dayhab`.`ServicesLocale` = 'HS'
AND `cl_clientsvcs_dayhab`.`CurrentRecord` = 1
AND `cl_clientsvcs_dayhab`.`AdmitDate` IS NOT NULL
AND (`cl_clientsvcs_dayhab`.`DischargeDate` IS NULL
OR `cl_clientsvcs_dayhab`.`DischargeDate` > '2010-09-24')
LEFT JOIN `xx_component_master`
ON `xx_component_master`.`UniqueNumber-003` = `cl_clientsvcs_dayhab`.`ComponentNum`
LEFT JOIN `xx_classroom_master`
ON `xx_classroom_master`.`UniqueNumber-037` = `cl_clientsvcs_dayhab`.`ClassroomNum`
LEFT JOIN `xx_state_master` AS `client_state`
ON `client_state`.`UniqueNumber-030` = `cl_client_common`.`StateNum`
LEFT JOIN `xx_marital_status`
ON `xx_marital_status`.`UniqueNumber-015` = `cl_client_common`.`MaritalStatusNumber`
LEFT JOIN `xx_legal_status`
ON `xx_legal_status`.`UniqueNumber-012` = `cl_client_common`.`LegalStatusNumber`
LEFT JOIN `xx_sc_master`
ON `xx_sc_master`.`UniqueNumber-024` = `cl_client_common`.`SCNumber`
LEFT JOIN `xx_race_master`
ON `xx_race_master`.`UniqueNumber-019` = `cl_client_common`.`RaceNumber`
LEFT JOIN `cl_client_guardians`
ON `cl_client_guardians`.`ClientCommonNumber` = `cl_client_common`.`UniqueNumber-900`
LEFT JOIN `xx_people_master` AS `Guardian1`
ON `Guardian1`.`UniqueNumber-036` = `cl_client_guardians`.`Guardian1Num`
LEFT JOIN `xx_state_master` AS `guardian1_state`
ON `guardian1_state`.`UniqueNumber-030` = `Guardian1`.`StateNum`
LEFT JOIN `xx_relation_master` AS `Guardian1Relation`
ON `Guardian1Relation`.`UniqueNumber-023` = `cl_client_guardians`.`Guardian1RelationNum`
LEFT JOIN `xx_people_master` AS `Guardian2`
ON `Guardian2`.`UniqueNumber-036` = `cl_client_guardians`.`Guardian2Num`
LEFT JOIN `xx_state_master` AS `guardian2_state`
ON `guardian2_state`.`UniqueNumber-030` = `Guardian2`.`StateNum`
LEFT JOIN `xx_relation_master` AS `Guardian2Relation`
ON `Guardian2Relation`.`UniqueNumber-023` = `cl_client_guardians`.`Guardian2RelationNum`
LEFT JOIN `xx_surrogate_master`
ON `xx_surrogate_master`.`UniqueNumber-031` = `cl_client_guardians`.`SurrogateNum`
LEFT JOIN `xx_state_master` AS `surrogate_state`
ON `surrogate_state`.`UniqueNumber-030` = `xx_surrogate_master`.`StateNum`
LEFT JOIN `cl_client_medical`
ON `cl_client_medical`.`ClientCommonNumber` = `cl_client_common`.`UniqueNumber-900`
LEFT JOIN `xx_hcondition_master` AS `PHC`
ON `PHC`.`UniqueNumber-009` = `cl_client_medical`.`PrimaryHandiCondNum`
LEFT JOIN `xx_hcondition_master` AS `SHC`
ON `SHC`.`UniqueNumber-009` = `cl_client_medical`.`SecondHandiCondNum`
LEFT JOIN `xx_language_master`
ON `xx_language_master`.`UniqueNumber-011` = `cl_client_common`.`LanguageNumber`
LEFT JOIN `cl_client_dietary`
ON `cl_client_dietary`.`ClientCommonNumber` = `cl_client_common`.`UniqueNumber-900`
LEFT JOIN `xx_physician_master` AS `primaryphysician`
ON `primaryphysician`.`UniqueNumber-017` = `cl_client_medical`.`PrimaryPhysicianNum`
LEFT JOIN `xx_state_master` AS `primaryphysician_state`
ON `primaryphysician_state`.`UniqueNumber-030` = `primaryphysician`.`StateNum`
LEFT JOIN `xx_physician_master` AS `secondphysician`
ON `secondphysician`.`UniqueNumber-017` = `cl_client_medical`.`SecondPhysicianNum`
LEFT JOIN `xx_state_master` AS `secondphysician_state`
ON `secondphysician_state`.`UniqueNumber-030` = `secondphysician`.`StateNum`
LEFT JOIN `xx_hospital_master`
ON `xx_hospital_master`.`UniqueNumber-010` = `cl_client_medical`.`HospitalNum`
LEFT JOIN `cl_client_contacts`
ON `cl_client_contacts`.`ClientCommonNumber` = `cl_client_common`.`UniqueNumber-900`
LEFT JOIN `xx_people_master` AS `emergencycontact`
ON `emergencycontact`.`UniqueNumber-036` = `cl_client_contacts`.`ContactNum`
LEFT JOIN `xx_relation_master` AS `emergencyrelation`
ON `emergencyrelation`.`UniquenUmber-023` = `cl_client_contacts`.`RelationNum`
LEFT JOIN `xx_state_master` AS `emergency_state`
ON `emergency_state`.`UniqueNumber-030` = `emergencycontact`.`StateNum`
LEFT JOIN `cl_clientsvcs_extcare`
ON `cl_clientsvcs_extcare`.`ClientCommonNumber` = `cl_client_common`.`UniqueNumber-900`
LEFT JOIN `xx_fundingcode_master` AS `HabFunding`
ON `HabFunding`.`UniqueNumber-008` = `cl_clientsvcs_dayhab`.`FundingNum`
LEFT JOIN `xx_county_master`
ON `xx_county_master`.`UniqueNumber-004` = `cl_client_common`.`CountyOfResidence`
WHERE `cl_clientsvcs_dayhab`.`ServicesLocale` = 'HS'
AND `cl_clientsvcs_dayhab`.`AdmitDate` IS NOT NULL
AND (`cl_clientsvcs_dayhab`.`DischargeDate` IS NULL
OR `cl_clientsvcs_dayhab`.`DischargeDate` > '2010-09-24')
AND `cl_clientsvcs_dayhab`.`CurrentRecord` = 1
AND `cl_clientsvcs_dayhab`.`ComponentNum` IN (16)
AND (`cl_clientsvcs_dayhab`.`ClassroomNum` = 0
OR `cl_clientsvcs_dayhab`.`ClassroomNum` IN
(7, 69, 12, 13, 15, 17, 18, 70, 6, 71, 11, 72, 16, 5, 14, 2, 3, 4, 8, 9, 10, 61, 50))
GROUP BY `ClientName`, `cl_client_common`.`UniqueNumber-900`, `cl_client_contacts`.`ContactNum`
ORDER BY `ClientName`, `cl_client_contacts`.`EmergencyPriority`
Humble Programmer
|