This is baffling me. I'm trying to use a case statement to create a column that returns either a one or a zero depending on whether a search of two columns for a hospital site/ward IS NOT in a list of values in a table/variable, and if two columns that represent if a journey to said hospital site/ward was cancelled for whatever reason. This counting column is called [Journeys Other] as it represents all ambulance journeys that took place to anywhere other than one of 4 other specific sites. This is why the case statement below is asking it to check for any hospitals and sites that aren't in the predefined list.
The problem is; the query is not doing as I ask it for the second column; [From hospital text]. It's returning 1's in the counting column where there is a match to an entry in the pre-defined list. It works fine for the first column [Requestee Name] as there are no matches. But the second column search might as well not exist. It's driving me mad.
I've plucked it out of it's much bigger main query to show what I've done so far;
SELECT [Journey ID]
, [Start Date]
, [Requestee Name]
, [From hospital text]
, [Abort Reason Text]
, [CANCEL REASON TEXT]
, CASE
WHEN [Requestee Name] NOT IN (ISNULL(mts.MatchingSite, ''))
AND ([From hospital text] NOT IN (ISNULL(mts.MatchingSite, '')) OR [From hospital text] IS NULL)
AND COALESCE([Abort Reason Text], [CANCEL REASON TEXT]) IS NULL THEN 1
ELSE 0
END AS [JOURNEYS OTHER]
From AE.dbo.PTS_MDSForReporting1415 mds LEFT JOIN @MatchesReq mts
ON mds.[Requestee Name] = mts.MatchingSiteReq
WHERE [ECR Flag]='0'
AND [Service Type Text] = 'Pallative Day'
AND [CCG] in ('Leeds North Ccg','Leeds South And East Ccg','Leeds West Ccg')
AND [Start Date] >= '01-JULY-2014'
So when I run this, the [Journeys Other] column created by the case statement has a 1 against rows that should not qualify for it.
Here are some example rows;
Journey ID Start Date Requestee Name From hospital text Abort Reason Text CANCEL REASON TEXT JOURNEYS OTHER
6630679 2014-07-08 00:00:00.000 Dales Ward St Gemmas Hospice NULL NULL 1
6633083 2014-07-10 00:00:00.000 Moors Ward St Gemmas Hospice NULL NULL 1
6644723 2014-07-12 00:00:00.000 J84 Thoracic's St James Hospital Leeds NULL NULL 1
6676438 2014-07-22 00:00:00.000 Day Unit Wheatfields Hospice NULL NULL 1
6675840 2014-07-22 00:00:00.000 Discharge Wheatfields Hospice NULL NULL 1
6694026 2014-07-28 00:00:00.000 Dales Ward St Gemmas Hospice NULL NULL 1
6693591 2014-07-28 00:00:00.000 Day Unit Bexley Wing (St James) NULL NULL 1
6693590 2014-07-28 00:00:00.000 Day Unit Wheatfields Hospice NULL NULL 1
6695586 2014-07-28 00:00:00.000 Day Unit Wheatfields Hospice NULL NULL 1
All these rows have a hospital/site in [From hospital text] that are in the list of hospitals/sites in my table variable. As there is a match with that column, they should be flagged with a 0 as per the ELSE condition of the case statement.
If it's important; there are a lot more values in the table variable than I have demonstrated in the few rows of results, and some of them are picked up properly by the query and marked as zero where there's a match, so it seems to be completely hit and miss. The only constantly correct rows are where the match occurs in [Requestee Name] and then the 2nd AND condition either is true or not (IS NULL or is NOT NULL when coalesced together.) The minute it has to consider a value in BOTH columns, it doesn't always work for the 2nd of the two.
I cannot for the life of me figure out why the case statement doesn't behave as expected to when it's comparing the table variable to the [From hospital text] column. Any advice would be greatly appreciated!!
