Delete the else values within case statement

Question

Delete the else values within case statement

bharatgi

SSChasing Mays

Points: 635
More actions
November 12, 2010 at 7:08 am

#228783

Hi,
I would like to remove the else values generated within the case statement from the following query:
select
[Organisation Code Code of Provider] as ProviderCode,
[OrganisationName] as ProviderName,
[Organisation Code Code of Commissioner] as PCTCode,
PCT_Name,
'SUS' as [Source],
left ([Discharge Date (From Hospital Provider Spell)],6) as Period,
[NON PBR] as [PBR Flag],
[Treatment Function Code],
SpecialtyDescription,
dbo.CIS_Spells_Applied_IP.[HRG Code],
[HRGDescription],
case
when [Admission Method (Hospital Provider Spell)] in ('11','12','13') and
[Patient Classification] = '1' and [NON PBR] ='0'and XBD >'0' then 'ELXBD'
when [Admission Method (Hospital Provider Spell)] in ('31','32','81','82','83')
and [NON PBR] ='0'and XBD >'0' then 'NELNEXBD'
when [Admission Method (Hospital Provider Spell)] in ('21','22','23','24','28')
and [NON PBR] ='0'and XBD >'0'
then 'NELXBD'
ELSE 'OTHER'
END as POD,
sum([XBD]) as 'Activity',
SUM([Final_Cost]) as 'Cost'
from dbo.CIS_Spells_Applied_IP
left join [Partnership_Local].dbo.tblrefTrusts
on [Organisation Code Code of Provider] = [Partnership_Local].dbo.tblrefTrusts.OrganisationCode
LEFT JOIN [Partnership_Local].dbo.PCTs
on [Organisation Code Code of Commissioner] = [Partnership_Local].dbo.PCTs.PCT_Code
left join [Partnership_Local].dbo.tblrefHRG4
on [HRG Code] = [Partnership_Local].dbo.tblrefHRG4.HRGCode
COLLATE Latin1_General_CI_AS
left join [Partnership_Local].dbo.tblrefSpecialty
on [Treatment Function Code] = [Partnership_Local].dbo.tblrefSpecialty.SpecialtyCode
COLLATE Latin1_General_CI_AS
LEFT JOIN [Partnership_Local].dbo.tblrefTariff1011APC
ON [CIS_Spells_Applied_IP].[HRG Code] = [Partnership_Local].dbo.tblrefTariff1011APC.HRGCode
COLLATE Latin1_General_CI_AS
where [Discharge Date (From Hospital Provider Spell)] BETWEEN '20100401' AND '20100930'
--and [Administrative Category] != '02'
and [Organisation Code Code of Provider] LIKE 'RV8%'
and [Organisation Code Code of Commissioner] in ('5K5', '5K6', '5LC', '5LA', '5HY', '5H1', '5HX', '5AT')
--and [POD] !='other'
group by
[Organisation Code Code of Provider],
[OrganisationName],
[Organisation Code Code of Commissioner],
PCT_Name,
left ([Discharge Date (From Hospital Provider Spell)],6),
[NON PBR],
[Treatment Function Code],
SpecialtyDescription,
[HRG Code],
[HRGDescription],
case
when [Admission Method (Hospital Provider Spell)] in ('11','12','13') and
[Patient Classification] = '1' and [NON PBR] ='0'and XBD >'0' then 'ELXBD'
when [Admission Method (Hospital Provider Spell)] in ('31','32','81','82','83')
and [NON PBR] ='0'and XBD >'0' then 'NELNEXBD'
when [Admission Method (Hospital Provider Spell)] in ('21','22','23','24','28')
and [NON PBR] ='0'and XBD >'0'
then 'NELXBD'
ELSE 'OTHER'
END
i have tried and [POD] !='other' within the where clause but it did not recognise the [pod] field.
Any ideas how i could correct this?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Assuming I've understood what you've asked for: -

WHERE [Admission Method (Hospital Provider Spell)] in ('11','12','13','31','32','81','82','83','21','22','23','24','28')

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

bharatgi SSChasing Mays Points: 635 More actions · Answer 2

its fine, i've added this to the where clause:

and XBD >'0'