Hello,
I am trying to modify a VS 2005 Report to include all Phone Numbers for a client. Currently it only includes Home. The below is the SQL in the Stored procedure for the Dataset used. After much trial and error I got ,what I thought, ws close with the below except that with Top 1 on (which was put there by the original developer) I get the Home Number but I get NULL for the expected Cell Number. If I remove the Top 1 I get two rows returned that are Identical except that in one Home Number is filled and Cell Number is NULL and the other is the opposite.
I am trying to figure out how to get all of the phone numbers for one client (PersonID) returned but maintain one row.
Please let me know how I can help you help me and thank you for any guidance!
CODE
SELECT TOP 1 CMO.dbo.tblAdminMaintItem.ItemDescription as TargetGroupDesc,
dbo.tblPlan.*,
Connect.dbo.tblPerson.*,
Connect.dbo.tblAddress.*,
(CASE WHEN Connect.dbo.tblPhone.PhoneTypeID = 'H' THEN Connect.dbo.tblPhone.PhoneNumber ELSE NULL END) AS 'Home Phone Number',
(CASE WHEN Connect.dbo.tblPhone.PhoneTypeID = 'B' THEN Connect.dbo.tblPhone.PhoneNumber ELSE NULL END) AS 'Business Phone Number',
(CASE WHEN Connect.dbo.tblPhone.PhoneTypeID = 'C' THEN Connect.dbo.tblPhone.PhoneNumber ELSE NULL END) AS 'Cell Phone Number',
(CASE WHEN Connect.dbo.tblPhone.PhoneTypeID = 'O' THEN Connect.dbo.tblPhone.PhoneNumber ELSE NULL END) AS 'Other Phone Number',
CMO.dbo.tblCMOClientInfo.MentalHealth,
tblDemographics.FirstName + ' ' + tblDemographics.LastName as SWName,
tblDemographics.Phone as SWPhone,
tblDemographics_1.FirstName + ' ' + tblDemographics_1.LastName as NurseName,
tblDemographics_1.Phone as NursePhone,
tblSupportWorker.FirstName + ' ' + tblSupportWorker.LastName as ESWName,
tblSupportWorker.Phone as ESWPhone,
Connect.dbo.tblPersonInsurance.MedicaidNumber,
Connect.dbo.tblPersonInsurance.MedicareNumber
FROM dbo.tblPlan LEFT OUTER JOIN
Connect.dbo.tblPerson ON dbo.tblPlan.ClientID = Connect.dbo.tblPerson.PersonID LEFT OUTER JOIN
Connect.dbo.tblAddress ON Connect.dbo.tblPerson.PersonID = Connect.dbo.tblAddress.PersonID and Connect.dbo.tblAddress.AddressTypeID = 'M' LEFT OUTER JOIN
Connect.dbo.tblPhone ON Connect.dbo.tblPerson.PersonID = Connect.dbo.tblPhone.PersonID LEFT OUTER JOIN
CMO.dbo.tblCMOEnrollment ON dbo.tblPlan.EnrollmentID = CMO.dbo.tblCMOEnrollment.EnrollmentID LEFT OUTER JOIN
CMO.dbo.tblCMOClientInfo ON CMO.dbo.tblCMOEnrollment.ClientID = CMO.dbo.tblCMOClientInfo.ClientID LEFT OUTER JOIN
CMO.dbo.tblAdminMaintItem ON CMO.dbo.tblCMOClientInfo.TargetGroupID = CMO.dbo.tblAdminMaintItem.ItemID LEFT OUTER JOIN
aspnetdb.dbo.tblDemographics ON CMO.dbo.tblCMOEnrollment.CMOSocialWorkerID = aspnetdb.dbo.tblDemographics.UserID LEFT OUTER JOIN
aspnetdb.dbo.tblDemographics AS tblDemographics_1 ON CMO.dbo.tblCMOEnrollment.CMONurseID = tblDemographics_1.UserID LEFT OUTER JOIN
CMO.dbo.tblSupportWorker ON CMO.dbo.tblCMOEnrollment.EconomicSupportWorkerID = CMO.dbo.tblSupportWorker.SupportWorkerID LEFT OUTER JOIN
Connect.dbo.tblPersonInsurance ON dbo.tblPlan.ClientID = Connect.dbo.tblPersonInsurance.PersonID
WHERE (dbo.tblPlan.PlanID = 658)
19 Jun, 2008 - 10:18 AM
| 