SQLTeam.com | Weblogs | Forums

Distinct rows


#1

For the below SQL I am getting 2 rows for PRV and CUR (as the table CVL has 2 rows for the same client ID)
How can I get the values in one row as Previous and Current location ?

Select CVL.Status, CVL.Locationname,
case when cvl.status='prv' AND CVL.LocationName like 'RGH-ED%' then cvl.locationname end as 'Previous Location',
case when cvl.status='cur' then cvl.locationname end as 'Current Location',
CVL.TransferRequestDtm,CV.ClientGUID,CV.ClientDisplayName, OCMI.Name 'Order Entered', CV.IDCode 'RHRN',
O.Entered, CV.VisitIDCode 'Patient Encounter',
O.IDCode 'Order ID', O.OrderStatusCode 'Order Status', O.Entered 'Date Entered', O.RequestedDate 'Order Requested Date'
FROM CV3Clientvisitlocation CVL
JOIN CV3ClientVisit CV ON CV.GUID=CVL.ClientVisitGUID
JOIN CV3Order O ON O.ClientVisitGUID=CV.GUID AND O.ChartGUID=CV.ChartGUID AND O.ClientGUID=CV.ClientGUID
JOIN CV3OrderCatalogMasterItem OCMI ON O.OrderCatalogMasterItemGUID=OCMI.GUID

WHERE
(CVL.Status='PRV' AND CVL.LocationName like 'RGH-ED%' OR
CVL.Status='CUR' AND CVL.LocationName like 'RGH-CCU%') AND
OCMI.CodedTimeGroupCode LIKE 'LAB%' AND (O.OrderStatusCode='AUA1' OR O.OrderStatusCode='PCOL' OR O.OrderStatusCode='PERF' OR O.OrderStatusCode='SPRV')

AND O.Entered > '2017-02-24'


#2

Without the table description and sample data, I can only guess, but this is what I came up with:

select cvl1.status
      ,cvl2.locationname as [Previous Location]
      ,cvl1.locationname as [Current Location]
      ,cvl1.transferrequestdtm
      ,cv.clientguid
      ,cv.clientdisplayname
      ,ocmi.name as [Order Entered]
      ,cv.idcode as rhrn
      ,o.entered
      ,cv.visitidcode as [Patient Encounter]
      ,o.idcode as [Order ID]
      ,o.orderstatuscode as [Order Status]
      ,o.entered as [Date Entered]
      ,o.requesteddate as [Order Requested Date]
  from cv3clientvisitlocation as cvl1
       left outer join cv3clientvisitlocation as cvl2
                    on cvl2.clientguid=cvl1.clientguid
                   and cvl2.status='PRV'
                   and cvl2.locationname like 'RGH-ED%'
       inner join cv3clientvisit as cv
               on cv.guid=cvl1.clientvisitguid
       inner join cv3order as o
               on o.clientvisitguid=cv.guid
              and o.chartguid=cv.chartguid
              and o.clientguid=cv.clientguid
              and o.orderstatuscode in ('AUA1','PCOL','PERF','SPRV')
              and o.entered>cast('2017-02-24' as date)
       inner join cv3ordercatalogmasteritem as ocmi
               on ocmi.guid=o.ordercatalogmasteritemguid
              and ocmi.codedtimegroupcode like 'LAB%'
 where cvl1.status='CUR'
   and cvl1.locationname like 'RGH-CCU%'
;

#3

Thank you ... This works !