Я изо всех сил пытался фильтровать, чтобы отображать только строки, которые не имеют NULLS в 2 столбцах.
Мне нужны результаты только для клиентов (строк), которые
have a value in both Disability 1 and Disability 2
.Не хотите отображать клиентов (строки), которые
have a value in Disability 1 and NULL in Disability 2 or a NULL in Disability 1 and value in Disability 2
.
Мои столбцы ClientName, ClientNumber, ClientAge, ClientGender, Disability1, Disability2
Столбцы Disability являются псевдонимами, использующими CASE.
Проблемы, которые я нашел:
нельзя ссылаться на псевдонимы в предложениях Where или Have, что затрудняет фильтрацию.
предложение «Имея» ссылается на D.DiagnosisName для 2 условий, что означает, что я не возвращаю результатов.
Был бы признателен за любые указатели.
Ваше здоровье
Мой код:
SELECT
C.ClientName,
C.ClientNumber,
C.ClientAge,
C.ClientGender,
CASE
WHEN D.DiagnosisName = 'Depression'
OR D.DiagnosisName = 'Anxiety'
OR D.DiagnosisName = 'Drug and Alcohol'
OR D.DiagnosisName = 'Bipolar Disorder'
OR D.DiagnosisName = 'Delusional'
OR D.DiagnosisName = 'Eating Disorder'
OR D.DiagnosisName = 'Obsessive Compulsive Disorder'
OR D.DiagnosisName = 'Personality Disorder'
OR D.DiagnosisName = 'Post Traumatic Stress Disorder'
OR D.DiagnosisName = 'Post-Natal'
OR D.DiagnosisName = 'Schizo-affective disorder'
OR D.DiagnosisName = 'Schizophrenia'
OR D.DiagnosisName = 'Other Psychiatric/Psychological Disorder'
OR D.DiagnosisName = 'BPD'
OR D.DiagnosisName = 'Psychiatric (other)'
THEN D.DiagnosisName
END AS [Disability1],
CASE
WHEN D.DiagnosisName = 'Autism'
OR D.DiagnosisName = 'Intellectual'
OR D.DiagnosisName = 'Specific Learning/ADD'
OR D.DiagnosisName = 'Acquired Brain Injury / Head Injury'
OR D.DiagnosisName = ' Acquired Brain Injury-Head Injury'
OR D.DiagnosisName = 'Neurological'
THEN D.DiagnosisName
END AS [Disability2]
FROM
dbo.FACTClientDiagnosis R
RIGHT OUTER JOIN DimClient C ON R.DimClientID = C.DimClientID
LEFT OUTER JOIN DimDiagnosisType D ON R.DimDiagnosisTypeID = D.DimDiagnosisTypeID
WHERE
ClientStatus = 'Active'
AND ClientType = 'Client'
AND D.DiagnosisName NOT LIKE 'NULL'
GROUP BY
C.ClientName,
C.ClientNumber,
C.ClientAge,
C.ClientGender,
D.DiagnosisName
HAVING
(
D.DiagnosisName = 'Depression'
OR D.DiagnosisName = 'Anxiety'
OR D.DiagnosisName = 'Drug and Alcohol'
OR D.DiagnosisName = 'Bipolar Disorder'
OR D.DiagnosisName = 'Delusional'
OR D.DiagnosisName = 'Eating Disorder'
OR D.DiagnosisName = 'Obsessive Compulsive Disorder'
OR D.DiagnosisName = 'Personality Disorder'
OR D.DiagnosisName = 'Post Traumatic Stress Disorder'
OR D.DiagnosisName = 'Post-Natal'
OR D.DiagnosisName = 'Schizo-affective disorder'
OR D.DiagnosisName = 'Schizophrenia'
OR D.DiagnosisName = 'Other Psychiatric/Psychological Disorder'
OR D.DiagnosisName = 'BPD'
OR D.DiagnosisName = 'Psychiatric (other)'
)
AND
(
D.DiagnosisName = 'Autism'
OR D.DiagnosisName = 'Intellectual'
OR D.DiagnosisName = 'Specific Learning/ADD'
OR D.DiagnosisName = 'Acquired Brain Injury / Head Injury'
OR D.DiagnosisName = ' Acquired Brain Injury-Head Injury'
OR D.DiagnosisName = 'Neurological'
)
ORDER BY
C.ClientName