SQLTeam.com | Weblogs | Forums

Extracting both values


#1

I have the following SQL -
This gives me the results of both the 'echo' and 'holter', Is there a way to take RHRN's which has both ?

select
ocmi.name 'Item Name',
o.Modifier 'Modifier',
cv.idcode 'RHRN',
cv.visitidcode 'Patient Encounter',
cv.currentlocation 'Location',
o.idcode 'Order ID',
o.OrderStatusCode 'Order Status',
o.entered 'Date entered',
o.RequestedDtm 'Order Requested Date',
cuu.DisplayName 'Requesting MD',
cuu.OccupationCode 'Provider Role',
cu.DisplayName 'Entering User',
o.EnterRole 'Entering User Role'
from
cv3order o
join cv3ordercatalogmasteritem ocmi on o.ordercatalogmasteritemguid=ocmi.guid
join CV3OrderStatus cus on o. OrderStatusCode=cus.Code
join cv3clientvisit cv on o.clientvisitguid=cv.guid
join CV3User cu on o.userGUID=cu.guid
join CV3User cuu on o.CareProviderGUID=cuu.guid
where
(ocmi.name like 'holter%' or ocmi.name like 'ECHO%')
and o.entered > '2017-01-01'
and ocmi.active=1 AND CurrentLocation NOT LIKE 'ACH%'
order by
RHRN


#2

Replace the "or" with "and" and wildcard the beginning of the strings.

ocmi.name like '%holter%' and ocmi.name like '%ECHO%'


#3

that does not give me any rows,

below is the SQL

SELECT OCMI.NAME 'Order Item Name',O.MODIFIER 'MODIFIER', CV.IDCODE 'RHRN', CV.VISITIDCODE 'Patient Encounter',CV.CurrentLocation 'Current Location',
O.IDCODE 'Order Id',O.ORDERSTATUSCODE 'Order Status', O.ENTERED 'DATE ENTERED',O.REQUESTEDDTM 'Order Requested Date',
CUU.DISPLAYNAME 'Requesting MD',CUU.OCCUPATIONCODE 'Provider Role',CU.DISPLAYNAME 'ENTERING USER', O.ENTERROLE 'ENTERING USER ROLE'

FROM CV3ORDER O
JOIN CV3ORDERCATALOGMASTERITEM OCMI ON O.ORDERCATALOGMASTERITEMGUID=OCMI.GUID
JOIN CV3ORDERSTATUS CUS ON O. ORDERSTATUSCODE=CUS.CODE
JOIN CV3CLIENTVISIT CV ON O.CLIENTVISITGUID=CV.GUID
JOIN CV3USER CU ON O.USERGUID=CU.GUID
JOIN CV3USER CUU ON O.CAREPROVIDERGUID=CUU.GUID
WHERE (OCMI.NAME = 'Holter Monitor - 24H' or OCMI.NAME ='Holter Monitor - 48H') AND OCMI.NAME LIKE 'ECHO%'

AND O.ENTERED > '2016-07-01'
AND OCMI.ACTIVE=1 AND CURRENTLocation NOT LIKE 'ACH%'
ORDER BY [Order Requested Date]