Question

lrj1985 on Thu, 09 Mar 2017 07:39:25


Hi,

What do I add to the where clause in my code so that it returns all the data except for where TITLE = 'OPH' AND Urgency = 'EMER'

Each title has 2 urgencies which are ELECT and EMER.

So I want the results to show all titles but only show ELECT for OPH.

SELECT 
		*
	FROM
		vwTMSTheatreSchedule AS S
	LEFT JOIN
		ssrs.vwHospitals AS H ON CASE WHEN H.FACILITY_CODE = 131 THEN 240 ELSE H.FACILITY_CODE END = S.FACILITY_CODE
	WHERE
		CONVERT(DATE, SCH_STRT_TIMESTAMP) BETWEEN '1 FEB 2017' AND '28 FEB 2017'


Sponsored



Replies

0bamɑ on Thu, 09 Mar 2017 07:44:24


Hi Sir,

;with cte as(

select * from  ....

left join ...  

on...

where   ......

)

select * from cte 

where TITLE <> 'OPH' AND Urgency <> 'EMER'


Uri Dimant on Thu, 09 Mar 2017 07:48:06


Can you show CREATE TABLE + INSERT INTO + desire result? Always state what version you are using

RajkumarMSBI5055 on Thu, 09 Mar 2017 10:56:16


There you go
SELECT 
		*
	FROM
		vwTMSTheatreSchedule AS S
	LEFT JOIN
		ssrs.vwHospitals AS H ON CASE WHEN H.FACILITY_CODE = 131 THEN 240 ELSE H.FACILITY_CODE END = S.FACILITY_CODE
	WHERE
		CONVERT(DATE, SCH_STRT_TIMESTAMP) BETWEEN '1 FEB 2017' AND '28 FEB 2017' AND (Title <> 'OPH' or (Title = 'OPH' and Urgency <> 'EMER'))


Sam Zha on Fri, 10 Mar 2017 09:37:41


Hi lrj1985,

You could also use NOT (Transact-SQL) to negate an expression.