Database

24-Sep-10 11:07

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

Luc Pattyn24-Sep-10 11:29

Luc Pattyn

24-Sep-10 11:29

Do you really expect anyone to read and study all that, while you are not even providing a decent description of what it does, what it is about, why you think it should run faster, and what you tried to get it any faster?

Unsure | :~

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

programmervb.netc++24-Sep-10 11:35

24-Sep-10 11:35

Actually it runs fine.
There was a post in the lounge regarding this particular statement and rather than post it in the lounge I posted it here and linked it.
What is does is get information for a facesheet which is basically all information on a client.
Like I said performance is not a problem there was just a conversation about the code being incorrect if it was so many lines of code.

Humble Programmer

Mycroft Holmes24-Sep-10 15:32

Mycroft Holmes

24-Sep-10 15:32

Create a view so no other poor bugger has to look at this code again.

I would probably break it into a number of different procs servicing different parts of the profile. Supporting this query would be nasty.

What happens when the dietician want the name and diet details, does he get all of the face sheet.

Never underestimate the power of human stupidity
RAH

programmervb.netc++24-Sep-10 19:09

24-Sep-10 19:09

In the case of the dietician needs info we would generally do that on a different report with only the information needed by them.
I do think that breaking the SQL out to bring in only parts of the profile is a good idea then we could more easily reuse them.
However a concern of mine in doing that would be that if we have to tie all of these back together would that not be a pretty big performance hit?
I would think you would have to do that with multiple statements which is in turn more network traffic.
We try to do as much as we can with minimal hits to our DB because we have some users that are on very slow connections.
Thanks for the ideas and look forward to more.

Humble Programmer

programmervb.netc++27-Sep-10 13:50

27-Sep-10 13:50

I do have another question.
As you can see we try to do as much logic as possible in the SQL so that we do not have do manipulate the dataset once it is returned....
Because from my understanding the server will be able to optimize much more efficiently than you will most likely be able to do in your program.
For example rather than retrieve first and last name then concat as needed for the result just write it into the SQL so that your result set will already be correct.

Humble Programmer

Mycroft Holmes27-Sep-10 15:45

Mycroft Holmes

27-Sep-10 15:45

programmervb.netc++ wrote:
first and last name then concat as needed for the result just write it into the SQL

That is definitely reasonable, however you need to make a judgment call on how much formatting you do in the query. EG you may note the guy who wants to add CRLF to the data in the query, this is totally invalid whereas concat of the names is good.

It also depends on the volume of data you are delivering, if you are doing this profile for 1 patient then this query is almost valid (I would still split it out for support reasons) doing this for 1000s is not valid.

Never underestimate the power of human stupidity
RAH

programmervb.netc++29-Sep-10 18:35

29-Sep-10 18:35

Why would the number of clients really matter if your indexes are correct?
I understand that the record set has an affect on total time but I don't see how that really relates to a CONCAT for example.
If you are going to have to CONCAT last and first name on 1000 clients what benefit would you have from doing it locally instead of the server?
Also in either case the concatenation I think the concatenation would be happening in memory so in theory the server should have more...
and I would think that the CONCAT statement written in my case mySQL is probably more efficient than the equivalent code in .NET.
Thanks for your thoughts I do appreciate your input just want to have a full understanding of your reasoning.

Humble Programmer

Richard MacCutchan24-Sep-10 23:34

Richard MacCutchan

24-Sep-10 23:34

programmervb.netc++ wrote:
Ideas of how to improve this query

Shoot the person who wrote it!

Just say 'NO' to evaluated arguments for diadic functions! Ash

programmervb.netc++27-Sep-10 13:47

how to upload sql database on remote database [moved]

27-Sep-10 13:47

Very constructive thanks.

Humble Programmer

DBBackup Error

MWRivera24-Sep-10 5:53

MWRivera

24-Sep-10 5:53

Hi All,

We are trying to run the following bdbackup command, on the command line from one of our virtual machines:

dbbackup -y -c "uid=xxx;pwd=xxx;dsn=DSN NAME" C:\xxx\Applications\xxx\Backup

only we are recieving the following error message:

"Parse error: DSN 'DSN NAME' does not exist"

The DSN is setup as a System DSN, which is what we are wanting the dbbackup to look at. The backup does work however when we set the DSN up as a User DSN.

Is there a way we can get the DBbackup to look at the System DSN instead of the User DSN?

Thank you,
Mel

Rohit from Delhi24-Sep-10 0:39

Rohit from Delhi

24-Sep-10 0:39

i m trying to upload my sql database . but can't access the remote server.

naveen

modified on Friday, September 24, 2010 7:38 AM

^1.21 Gigawatts24-Sep-10 0:42

^1.21 Gigawatts

24-Sep-10 0:42

Psssst....

Wrong forum!!

Quick, otherwise they'll set the dogs on you!!

"Benjamin is nobody's friend. If Benjamin were an ice cream flavor, he'd be pralines and dick." ~ Garth Algar

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." ~ Paul Neal "Red" Adair

LunaticFringe24-Sep-10 0:43

LunaticFringe

24-Sep-10 0:43

Too late.

Here, JSOP... here, JSOP... good boy.

Sic 'em.

Big Grin | :-D

L u n a t i c F r i n g e

Nagy Vilmos24-Sep-10 0:53

Nagy Vilmos

24-Sep-10 0:53

I haven't seen it. Ask Bob.

Panic, Chaos, Destruction.
My work here is done.

or "Drink. Get drunk. Fall over." - P O'H