Query to be simplified

Transact-SQL
1

I am using 3 different queries to pull out data, is there a way to get this simplified (it does work but takes lot of time.)

thanks

drop table #tmp1
drop table #tmp2

SELECT
ORD.Name [Order Name],ORD.OrderSetHeading[Orderset Heading],ORD.ORDERSETNAME,oud.OrderGUID,
OUD.ClientGUID,OUD.Value [Medication Name],CV.CareLevelCode,CV.CurrentLocation
into #tmp1
FROM CV3OrderUserData OUD
JOIN CV3Order ORD
ON OUD.OrderGUID = Ord.GUID
AND OUD.ClientGUID = Ord.ClientGUID
join CV3ClientVisit CV on ORD.ClientGUID = CV.ClientGUID and ORD.ChartGUID = CV.ChartGUID
WHERE OUD.UserDataCode = 'RX_BridgingMedicationName'
AND ORD.NAME LIKE 'Bridging%'
AND ORD.CreatedWhen>'2016-09-01'
SELECT
ORD.GUID,ORD.CLIENTGUID,OUD.UserDataCode,OUD.Value [Bridging Reason]
into #tmp2
FROM CV3OrderUserData OUD
JOIN CV3Order ORD
ON OUD.OrderGUID = Ord.GUID
AND OUD.ClientGUID = Ord.ClientGUID
WHERE OUD.UserDataCode = 'RX_Bridgingreason'
AND ORD.NAME LIKE 'Bridging%'
AND ORD.CreatedWhen>'2016-09-01'
Select [Order Name],[Orderset Heading],OrderSetName, [Medication Name], [Bridging Reason],CareLevelCode,CurrentLocation
from #tmp1
join #tmp2 on ORDERGUID=GUID AND #tmp1.ClientGUID=#tmp2.CLIENTGUID

2

Without sample data (and no knowledge of your data), it's difficult to to come up valid solution.

But here my best guess:

select ord.Name as [Order Name]
      ,ord.OrderSetHeading as [Orderset Heading]
      ,ord.OrderSetName
      ,oud1.Value as [Medication Name]
      ,ord.Value as [Bridging Reason]
      ,cv.CareLevelCode
      ,cv.CurrentLocation
  from CV3Order as ord
       inner join CV3OrderUserData as oud1
               on oud1.OrderGUID=ord.GUID
              and oud1.ClientGUID=ord.ClientGUID
              and oud1.UserDataCode='RX_BridgingMedicationName'
       inner join CV3OrderUserData as oud2
               on oud2.OrderGUID=ord.GUID
              and oud2.ClientGUID=ord.ClientGUID
              and oud2.UserDataCode='RX_Bridgingreason'
       inner join CV3ClientVisit as cv
               on cv.ClientGUID=ord.ClientGUID
              and cv.ChartGUID=ord.ChartGUID
 where ord.Name like 'Bridging%'
   and ord.CreatedWhen>cast('2016-09-01' as datetime) /* this should be casted as same field type as CreatedWhen */
;
1 Like
3

Thank you very much :slight_smile: This works perfect, The issue is it takes 3 minutes 39 seconds to pull 384 rows,
I am working on that,

4

Have you set appropiate index's?
What's the datatype of field CreatedWhen in table CV3Order?