Can I make multiple records display values within the same record-CodePudding

I am using the following query to display records of customers and their disabilites. The query works, but the results show multiple records for the same case and customer where they have multiple disabilities. Is there a way to include all disabilites under one record, so it does not duplicate the entire record for a minor change in the data?

The following is my query in it's current state:

USE MyPersonalSupport_reporting

SELECT 

SC.Name AS 'Sub-Contract',
CSCH.Received AS LiveDate,
CS.ServiceEndDate AS ServiceEndDate,
CS.CaseReference,
CONTACT.FirstName AS 'Forename',
CONTACT.LastName AS 'Surname',
CONTACT.DateOfBirth AS DOB,
CONTACT.DateOfDeath,
CONTACT.Age,
CCAV.ConcatenatedAddress AS 'Full Address',
LK1.Value AS Ethnicity,
LK2.Value AS Sex,
LK3.Value AS Religion,
LK4.Value AS Sexuality,
LK5.Value AS Transgender,
LK6.Value AS Nationality,
LK7.Value AS 'First Language',
SO.Name AS ServiceOffering,
LK.Value AS CaseStatus,
DATEDIFF(day, CSCH.Received, CS.serviceenddate) AS 'Days Occupied',
CONCAT (EMP.FirstName, ' ' , EMP.LastName) AS KeyWorker, 
CASE WHEN CONTACT.HasDisibility = 1 THEN 'YES' ELSE 'NO' END AS HasDisability,
CASE WHEN DV.value = 'Autistic Spectrum Condition' THEN 'YES' ELSE 'NO' END AS AutisticSpectrumCondition,
CASE WHEN DV.value = 'Hearing Impairment' THEN 'YES' ELSE 'NO' END AS 'Hearing Impairment',
CASE WHEN DV.value = 'Learning Disability' THEN 'YES' ELSE 'NO' END AS 'Learning Disability',
CASE WHEN DV.value = 'Mental Health' THEN 'YES' ELSE 'NO' END AS 'Mental Health',
CASE WHEN DV.value = 'Mobility Disability' THEN 'YES' ELSE 'NO' END AS 'Mobility Disability',
CASE WHEN DV.value = 'Progressive Disability / Chronic Illness' THEN 'YES' ELSE 'NO' END AS 'Progressive Disability / Chronic Illness',
CASE WHEN DV.value = 'Visual Impairment' THEN 'YES' ELSE 'NO' END AS 'Visual Impairment',
CASE WHEN DV.value = 'Other' THEN 'YES' ELSE 'NO' END AS 'Other Disability',
CASE WHEN DV.value = 'Does not wish to disclose' THEN 'YES' ELSE 'NO' END AS 'Does not wish to disclose'



FROM [MyPersonalSupport_reporting].[Mps].[Cases] AS CS

INNER JOIN mps.CaseContracts AS CC ON CS.caseid = CC.caseid 
INNER JOIN mps.CaseStatusChangeHistories AS CSCH ON CS.CaseId = CSCH.CaseId
INNER JOIN mps.Contacts AS CONTACT ON CS.CustomerId = CONTACT.ContactId
FULL OUTER JOIN mps.ContactCurrentAddress AS CCAV ON CONTACT.ContactID = CCAV.ContactId
FULL OUTER JOIN mps.LookupItems AS LK ON CSCH.StatusId = LK.LookupItemId
FULL OUTER JOIN mps.LookupItems AS LK1 ON CONTACT.EthnicityId = LK1.LookupItemId
FULL OUTER JOIN mps.LookupItems AS LK2 ON CONTACT.SexId = LK2.LookupItemId
FULL OUTER JOIN mps.LookupItems AS LK3 ON CONTACT.ReligionId = LK3.LookupItemId
FULL OUTER JOIN mps.LookupItems AS LK4 ON CONTACT.SexualityId = LK4.LookupItemId
FULL OUTER JOIN mps.LookupItems AS LK5 ON CONTACT.TransgenderId = LK5.LookupItemId
FULL OUTER JOIN mps.LookupItems AS LK6 ON CONTACT.NationalityId = LK6.LookupItemId
FULL OUTER JOIN mps.LookupItems AS LK7 ON CONTACT.FirstLanguageId = LK7.LookupItemId
FULL OUTER JOIN mps.SubContracts AS SC ON CC.SubContractId = SC.SubContractId
FULL OUTER JOIN mps.ServiceOfferings AS SO ON SC.ServiceOfferingId = SO.ServiceOfferingId
FULL OUTER JOIN mps.Employees AS EMP ON EMP.EmployeeId = CS.KeyWorkerId
FULL OUTER JOIN dbo.disabilitiescrosstab AS DV ON CONTACT.ContactId = DV.EntityID 




WHERE 

CSCH.Received >= '2000-01-01' AND CS.ServiceEndDate <= GETDATE()

AND CSCH.StatusId = 1392

AND CSCH.Archived = 0

AND CONTACT.Archived = 0

ORDER BY CS.CaseId

CodePudding user response：

It appears that what you need is a two-part query. Once to get each "contact" person with their disabilities already pre-aggregated with all disabilities so the result is a single record per contact. THEN join with the cases. Additionally, you have FULL OUTER JOINS for your lookup tables. Its not like someone would be allowed to have multiple lookup values for ethnicity, sex, religion, sexuality -- would they? But in today's age of associating with things, possible? But I think the intent is that a person can only be classified into one category otherwise you would have multiple records for every combination of ethnicity, sexuality, transgender, etc... So, lets take the lookups as intended as only a single value would ever exist. These should just be INNER JOINS to the corresponding lookup table, not FULL OUTER joins.

Notice this query is nothing but associated with the contact and its corresponding lookup tables / disabilities and grouped by just the contact ID. So it will result in a single record no matter how many disablities per contact.

SELECT 
        CONTACT.ContactId,
        max( CONTACT.FirstName ) 'Forename',
        max( CONTACT.LastName ) 'Surname',
        max( CONTACT.DateOfBirth ) DOB,
        max( CONTACT.DateOfDeath ) DateOfDeath,
        max( CONTACT.Age ) Age,
        max( CCAV.ConcatenatedAddress ) 'Full Address',
        max( LK1.Value  ) Ethnicity,
        max( LK2.Value  ) Sex,
        max( LK3.Value  ) Religion,
        max( LK4.Value  ) Sexuality,
        max( LK5.Value  ) Transgender,
        max( LK6.Value  ) Nationality,
        max( LK7.Value  ) 'First Language',
        max( CASE WHEN CONTACT.HasDisibility = 1 THEN 'YES' ELSE 'NO' END ) HasDisability,
        max( CASE WHEN DV.value = 'Autistic Spectrum Condition' THEN 'YES' ELSE 'NO' END ) AutisticSpectrumCondition,
        max( CASE WHEN DV.value = 'Hearing Impairment' THEN 'YES' ELSE 'NO' END ) 'Hearing Impairment',
        max( CASE WHEN DV.value = 'Learning Disability' THEN 'YES' ELSE 'NO' END ) 'Learning Disability',
        max( CASE WHEN DV.value = 'Mental Health' THEN 'YES' ELSE 'NO' END ) 'Mental Health',
        max( CASE WHEN DV.value = 'Mobility Disability' THEN 'YES' ELSE 'NO' END ) 'Mobility Disability',
        max( CASE WHEN DV.value = 'Progressive Disability / Chronic Illness' THEN 'YES' ELSE 'NO' END ) 'Progressive Disability / Chronic Illness',
        max( CASE WHEN DV.value = 'Visual Impairment' THEN 'YES' ELSE 'NO' END ) 'Visual Impairment',
        max( CASE WHEN DV.value = 'Other' THEN 'YES' ELSE 'NO' END ) 'Other Disability',
        max( CASE WHEN DV.value = 'Does not wish to disclose' THEN 'YES' ELSE 'NO' END ) 'Does not wish to disclose'
    FROM 
        mps.Contacts AS CONTACT 
            JOIN dbo.disabilitiescrosstab AS DV 
                ON CONTACT.ContactId = DV.EntityID 
            JOIN mps.ContactCurrentAddress AS CCAV 
                ON CONTACT.ContactID = CCAV.ContactId
            JOIN mps.LookupItems AS LK1 
                ON CONTACT.EthnicityId = LK1.LookupItemId
            JOIN mps.LookupItems AS LK2 
                ON CONTACT.SexId = LK2.LookupItemId
            JOIN mps.LookupItems AS LK3 
                ON CONTACT.ReligionId = LK3.LookupItemId
            JOIN mps.LookupItems AS LK4 
                ON CONTACT.SexualityId = LK4.LookupItemId
            JOIN mps.LookupItems AS LK5 
                ON CONTACT.TransgenderId = LK5.LookupItemId
            JOIN mps.LookupItems AS LK6 
                ON CONTACT.NationalityId = LK6.LookupItemId
            JOIN mps.LookupItems AS LK7
                ON CONTACT.FirstLanguageId = LK7.LookupItemId
    WHERE
        -- put the ARCHIVED FILTER HERE so it only includes active vs everyone in the system.
        CONTACT.Archived = 0
    GROUP BY
        CONTACT.ContactId

Now, you can take the above query and use IT as a sub-select query to get its parts in the final

SELECT 
        SC.Name AS 'Sub-Contract',
        CSCH.Received AS LiveDate,
        CS.ServiceEndDate AS ServiceEndDate,
        CS.CaseReference,
        OneContact.Forename,
        OneContact.Surname,
        OneContact.DOB,
        OneContact.DateOfDeath,
        OneContact.Age,
        OneContact.[Full Address],
        OneContact.Ethnicity,
        OneContact.Sex,
        OneContact.Religion,
        OneContact.Sexuality,
        OneContact.Transgender,
        OneContact.Nationality,
        OneContact.[First Language],
        SO.Name AS ServiceOffering,
        LK.Value AS CaseStatus,
        DATEDIFF(day, CSCH.Received, CS.serviceenddate) AS 'Days Occupied',
        CONCAT (EMP.FirstName, ' ' , EMP.LastName) AS KeyWorker, 
        OneContact.HasDisability,
        OneContact.AutisticSpectrumCondition,
        OneContact.[Hearing Impairment],
        OneContact.[Learning Disability],
        OneContact.[Mental Health],
        OneContact.[Mobility Disability],
        OneContact.[Progressive Disability / Chronic Illness],
        OneContact.[Visual Impairment],
        OneContact.[Other Disability],
        OneContact.[Does not wish to disclose]
    FROM 
        Mps.Cases AS CS
            INNER JOIN mps.CaseContracts AS CC 
                ON CS.caseid = CC.caseid 
            INNER JOIN mps.CaseStatusChangeHistories AS CSCH 
                ON CS.CaseId = CSCH.CaseId
            INNER JOIN 
            ( complete first query above) OneContact
                ON CS.CustomerId = OneContact.ContactId
            JOIN mps.LookupItems LK 
                ON CSCH.StatusId = LK.LookupItemId
            JOIN mps.SubContracts SC 
                ON CC.SubContractId = SC.SubContractId
            JOIN mps.ServiceOfferings SO 
                ON SC.ServiceOfferingId = SO.ServiceOfferingId
            JOIN mps.Employees EMP 
                ON EMP.EmployeeId = CS.KeyWorkerId
    WHERE 
            CSCH.Received >= '2000-01-01' 
        AND CS.ServiceEndDate <= GETDATE()
        AND CSCH.StatusId = 1392
        AND CSCH.Archived = 0
    ORDER BY
        CS.CaseId

Now, one suggestion for column names. I do not recommend having final column names with any spaces or special characters such as you have, especially with the ones like [Progressive Disability / Chronic Illness], and [Does not wish to disclose]. They should be normal, no spaces. The formatting / output should be handled by the output process to put in such headers and such. More headache when you need to explicitly tack quotes/brackets around column names, especially if you miss spaces. Instead, have column name as: ProgressiveDisabilityChronicIllness and ChronicIllness. Your output routines can split the column headings.

One additional point. The first query will process ALL contacts regardless of the cases. But the cases will only pull those that it qualifies for. If you find your query is taking too much time because of ALL contacts, you can update that by limiting the contacts by those cases it qualifies for. At least when querying the contacts, I did include the archived = 0 status there.

CodePudding user response：

Although I don't know your use case exactly but I believe there is no need to do full outer join, you can get away with left join / inner join.

Step 1: You can use STRING_AGG for the "DisibilityName" column. Aggregate all disease for each entity ID

SELECT EntityID, STRING_AGG(DV_VALUE,",") 
FROM 
dbo.disabilitiescrosstab
GROUP BY EntityID

Step 2: then join aggregated table with your base table

Code(have removed few joins but the idea is same):

SELECT 

SC.Name AS 'Sub-Contract',
CSCH.Received AS LiveDate,
CS.ServiceEndDate AS ServiceEndDate,
CS.CaseReference,
CONTACT.FirstName AS 'Forename',
CONTACT.LastName AS 'Surname',
CONTACT.DateOfBirth AS DOB,
CONTACT.DateOfDeath,
CONTACT.Age,
CCAV.ConcatenatedAddress AS 'Full Address',
DV.VALUE AS DisibilityName

FROM [MyPersonalSupport_reporting].[Mps].[Cases] AS CS

LEFT JOIN mps.CaseContracts AS CC ON CS.caseid = CC.caseid 
LEFT JOIN mps.CaseStatusChangeHistories AS CSCH ON CS.CaseId = CSCH.CaseId
LEFT JOIN mps.Contacts AS CONTACT ON CS.CustomerId = CONTACT.ContactId
LEFT JOIN mps.ContactCurrentAddress AS CCAV ON CONTACT.ContactID = CCAV.ContactId
LEFT JOIN (SELECT EntityID, STRING_AGG(DV_VALUE,",") FROM dbo.disabilitiescrosstab GROUP BY EntityID) AS DV ON CONTACT.ContactId = DV.EntityID 


WHERE 
CSCH.Received >= '2000-01-01' AND CS.ServiceEndDate <= GETDATE()
AND CSCH.StatusId = 1392
AND CSCH.Archived = 0
AND CONTACT.Archived = 0
ORDER BY CS.CaseId