Case...Including all nulls
-
Hi guys i have the following tsql script........
select
distinct P.KPAIDS_NO,P.FirstName,P.LastName
,'Age'= Datediff(yy,P.Date_Of_Birth,Getdate())
,CLS.Clinic_Sites
,convert(char(10),P.Registration_Date,110) as 'Registration Date'
,max(Case when CL.Category = 'A' then 'A' else '' end) as 'Category A'
,max(Case when CL.Category = 'B' then 'B' else '' end) as 'Category B'
,max(Case when CL.Category = 'C' then 'C' else'' end) as 'Category C'
,max(Case when CL.Category = 'N' then 'N' else'' end) as 'Category N'
,max(Case when CL.Category = 'O' then 'O' else'' end) as 'Category O'
..........
......
but i also want to include those who have none of these categories to my list.
Help please
thx much
-
MAX(CASE WHEN CL.Category NOT IN ('A', 'B', 'C', 'N', 'O') THEN 'Z' ELSE '' END) AS 'Category Z'
-
Also read this article on how to correctly calculate the age
http://www.sqlteam.com/article/datediff-function-demystified
N 56°04'39.16"
E 12°55'05.25" -
nope didn't work........... i want it to look somtin like the following
KP cat1 cat2 cat3 cat4
1 y no y no
2 y no y no
3 no no no no
-
So Are you saying that example data would have looked like this?
KP Cat
1 1
1 3
2 1
2 3
for you to get this
KP cat1 cat2 cat3 cat4
1 y no y no
2 y no y no
3 no no no no
If so look at IsNull or Coalesce around you MAX(....) lines.
-
MAX(CASE WHEN CL.Category like '[^a-z]' THEN 'y' ELSE 'no' END) AS 'Category Z'
N 56°04'39.16"
E 12°55'05.25" -
SELECT
p.KPAIDS_NO,
p.FirstName,
p.LastName,
dbo.fnYearsApart(p.Date_Of_Birth, CURRENT_TIMESTAMP),
cls.Clinic_Sites,
CONVERT(CHAR(10), p.Registration_Date, 110) AS [Registration Date],
MAX(CASE WHEN cl.Category = 'A' THEN 'y' ELSE 'no' END) AS [Category A],
MAX(CASE WHEN cl.Category = 'B' THEN 'y' ELSE 'no' END) AS [Category B],
MAX(CASE WHEN cl.Category = 'C' THEN 'y' ELSE 'no' END) AS [Category C],
MAX(CASE WHEN cl.Category = 'N' THEN 'y' ELSE 'no' END) AS [Category N],
MAX(CASE WHEN cl.Category = 'O' THEN 'y' ELSE 'no' END) AS [Category O],
MAX(CASE WHEN cl.Category LIKE '[^ABCNO]' THEN 'y' ELSE 'no' END) AS [Category Z]
...
N 56°04'39.16"
E 12°55'05.25" -
Ok, srry if i didn't make myself clear in initial posting.
What is happening with the resultset of this query is that Clients are missing from the list when i filtered by category, but doing a background check i realise tht persons who were missing don't have any category, but i still want them on the list regardless.
.......
Should there be somthing in the where clause tht will accept those who don't have a category?
-
Use LEFT JOIN instead of INNER JOIN.
You could try to post complete query, so we don't have to play guessing games.
N 56°04'39.16"
E 12°55'05.25" -
The case statements are all fine and have nothing to do with NULLs not being pulled during the query. This will depend on the Join statement (if multiple tables) and the where clause. If you are using a where clause that filters for specific categories without including NULLs then you will not get any records where the category is null.
James.
-
select
distinct P.KPAIDS_NO,P.FirstName,P.LastName
,'Age'= Datediff(yy,P.Date_Of_Birth,Getdate())
,CLS.Clinic_Sites
,convert(char(10),P.Registration_Date,110) as 'Registration Date'
,isnull(max(Case when CL.Category = 'A' then 'A' else '' end),'') as 'Category A'
,isnull(max(Case when CL.Category = 'B' then 'B' else '' end),'') as 'Category B'
,isnull(max(Case when CL.Category = 'C' then 'C' else'' end),'') as 'Category C'
,isnull(max(Case when CL.Category = 'N' then 'N' else'' end),'') as 'Category N'
,isnull(max(Case when CL.Category = 'O' then 'O' else'' end),'') as 'Category O'
,isnull(max(case when CL.Category = '' then '' end),'') as 'None'
,convert(char(10),CD.Date_Diagnosed,110) as 'Date Diagnosed'
,convert(char(10),CP.LMP_Date,110)as 'LMP Date'
,convert(char(10),CP.EDD_Date,110)as 'EDD Date',CP.Parity
,convert(char(10),CP.ARV_Start_Date,110) as 'ARV Start Date'
,CP.ARV_Type, convert(char(10),CP.C13_Test_Date,110) as 'C13 Test Date'
, CP.C13_Test_Method, CP.C13_Test_Result
,convert(char(10),CP.VDRL_Date,110) as 'VDRL Date'
,CP.VDRL_Result,convert(char(10),CP.HB_Date,110) as 'HB Date'
, CP.HB_Result,convert(char(10),CP.Group_RH_Date,110) as 'Group RH Date'
,CP.Group_RH_Result
From
Patient_Demographics P
left
outer join
Patient_CDC_Clinical_Diagnosis CD
on
P
.KPAIDS_NO = CD.KPAIDS_NO
inner
join
(
Select
distinct C.KPAIDS_NO,max(C.Date_Diagnosed)as 'Cdate' from
Patient_CDC_Clinical_Diagnosis C
group by C.KPAIDS_NO ) CDC
on
CDC
.KPAIDS_NO = CD.KPAIDS_NO
and
CDC
.Cdate = CD.Date_Diagnosed
inner
join
CDC_Clinical_Diagnosis_lookup CL
on
CD
.CDC_Diagnosis_Code = CL.Diagnosis_Code
left
outer join
Patient_Current_Pregnancy_details CP
on
P
.KPAIDS_NO = CP.KPAIDS_NO
inner
join
(
Select CM.KPAIDS_NO , Max(CM.Examination_Date) as 'E Date' from
Patient_Current_Pregnancy_details CM
group by CM.KPAIDS_NO) CM1
on
CM1
.KPAIDS_NO = CP.KPAIDS_NO
and
CM1
.[E Date] = CP.Examination_Date
inner
join
Clinic_Sites_Lookup CLS
on
P
.Initiation_Institution = CLS.Clinic_ID
left
outer join
Patient_Pregnancy_Outcome PO
on
P
.KPAIDS_NO = PO.KPAIDS_NO
inner
join
Den_Delivery_Mode_Lookup DL
on
PO
.Mode_of_Delivery = DL.Deliver_Id
left
outer join
[Pregnancy Outcome Only] POO
on
PO
.KPAIDS_NO = POO.Mother_KPAIDS_NO
and
PO
.Date_Delivered = POO.Date_Delivered
where
P.Mother_or_Child = 2
group
by P.KPAIDS_NO,P.FirstName,P.LastName,P.Date_Of_Birth,CD.Date_Diagnosed
,P.Registration_Date
,CD.Date_Diagnosed,CP.LMP_Date,CP.EDD_Date,CP.Parity,CP.ARV_Start_Date
,CP.ARV_Type, CP.C13_Test_Date, CP.C13_Test_Method, CP.C13_Test_Result
,CP.VDRL_Date,CP.VDRL_Result,CP.HB_Date, CP.HB_Result,CP.Group_RH_Date
,CP.Group_RH_Result,CLS.Clinic_Sites,PO.Date_Delivered
,PO.HIV_Treatment_given, PO.AZT,PO.NVP, DL.Delivery_Modes
,POO.Child_KPAIDS_NO, POO.outcome
go
-
Ok, apparently a prob with the inner join......
thx for the help guys, i really appreciate it.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply