October 7, 2013 at 1:57 pm
Could someone desipher this query that was handed to me? Primarily, how are they joining OeOrders with DOeCategories? I can't understand how you can join tables this way.
Shouldn't there be a where clause somewhere?
SELECT
IP.SourceID
,IP.FacilityID
,IP.AccountNumber
,IP.UnitNumber
,IP.Name AS PatientName
,IP.VisitID
,IP.PatientID
,IP.AdmitDateTime
,IP.DischargeDateTime
,IP.BirthDateTime
,IP.ErServiceDateTime
,IP.PtStatus
,OE.OrderID
,OE.OrderNumber
,DOP.[Name] AS [OrderName]
,OE.OrderDateTime
,OMC.Interface AS CategoryInterface
,OE2.OrdSourceID
/* Whether CPOE was used */
,CASE WHEN DARP.OeDefaultSourceID IS NOT NULL THEN 'Y'
ELSE NULL
END AS CPOE
INTO
dbo.#TotalOrders
FROM
(
dbo.OeOrders AS OE
INNER JOIN dbo.DOeCategories AS OMC
ON OE.SourceID = OMC.SourceID
AND OE.Category = OMC.CategoryID
AND OE.OrderDateTime BETWEEN @FromDate AND @ThruDate
AND (
OMC.Interface IN ( SELECT
ParameterValue
FROM
dbo.#MedInterface )
OR OMC.Interface IN ( SELECT
ParameterValue
FROM
dbo.#LabInterface )
OR OMC.Interface IN ( SELECT
ParameterValue
FROM
dbo.#RadInterface )
)
AND OE.[Status] NOT IN ( 'CANC', 'UNVER' )
INNER JOIN dbo.DOeProcs AS DOP
ON OE.SourceID = DOP.SourceID
AND OE.Category = DOP.CategoryID
AND OE.OrderedProcedure = DOP.ProcedureID
INNER JOIN dbo.OeOrders2 AS OE2
ON OE.SourceID = OE2.SourceID
AND OE.OrderID = OE2.OrderID
LEFT OUTER JOIN (
dbo.DrArraAppReportParameters AS DARP
INNER JOIN dbo.mt_fn_2014_MG_NPRApplicationSourceIDs(@EnvironmentID, 'DR') AS DRSI
ON DARP.SourceID = DRSI.DatabaseSourceID
)
ON OE2.OrdSourceID = DARP.OeDefaultSourceID
)
INNER JOIN dbo.mt_fn_2014_CoreMenuInitialPopulation(@BeginDate, @EndDate, NULL-- Indicates do not use Admit or Discharge DateTime
, CASE WHEN UPPER(@EDMethod) = 'A' THEN 'IN_ER'-- All ED Visits Method
WHEN UPPER(@EDMethod) = 'O' THEN 'IN_INO'-- Observation Services Method
ELSE 'IN_ER'
END, @EnvironmentID) AS IP
ON IP.SourceID = OE.SourceID
AND IP.VisitID = OE.VisitID
October 7, 2013 at 2:06 pm
NineIron (10/7/2013)
Could someone desipher this query that was handed to me? Primarily, how are they joining OeOrders with DOeCategories? I can't understand how you can join tables this way.Shouldn't there be a where clause somewhere?
SELECT
IP.SourceID
,IP.FacilityID
,IP.AccountNumber
,IP.UnitNumber
,IP.Name AS PatientName
,IP.VisitID
,IP.PatientID
,IP.AdmitDateTime
,IP.DischargeDateTime
,IP.BirthDateTime
,IP.ErServiceDateTime
,IP.PtStatus
,OE.OrderID
,OE.OrderNumber
,DOP.[Name] AS [OrderName]
,OE.OrderDateTime
,OMC.Interface AS CategoryInterface
,OE2.OrdSourceID
/* Whether CPOE was used */
,CASE WHEN DARP.OeDefaultSourceID IS NOT NULL THEN 'Y'
ELSE NULL
END AS CPOE
INTO
dbo.#TotalOrders
FROM
(
dbo.OeOrders AS OE
INNER JOIN dbo.DOeCategories AS OMC
ON OE.SourceID = OMC.SourceID
AND OE.Category = OMC.CategoryID
AND OE.OrderDateTime BETWEEN @FromDate AND @ThruDate
AND (
OMC.Interface IN ( SELECT
ParameterValue
FROM
dbo.#MedInterface )
OR OMC.Interface IN ( SELECT
ParameterValue
FROM
dbo.#LabInterface )
OR OMC.Interface IN ( SELECT
ParameterValue
FROM
dbo.#RadInterface )
)
AND OE.[Status] NOT IN ( 'CANC', 'UNVER' )
INNER JOIN dbo.DOeProcs AS DOP
ON OE.SourceID = DOP.SourceID
AND OE.Category = DOP.CategoryID
AND OE.OrderedProcedure = DOP.ProcedureID
INNER JOIN dbo.OeOrders2 AS OE2
ON OE.SourceID = OE2.SourceID
AND OE.OrderID = OE2.OrderID
LEFT OUTER JOIN (
dbo.DrArraAppReportParameters AS DARP
INNER JOIN dbo.mt_fn_2014_MG_NPRApplicationSourceIDs(@EnvironmentID, 'DR') AS DRSI
ON DARP.SourceID = DRSI.DatabaseSourceID
)
ON OE2.OrdSourceID = DARP.OeDefaultSourceID
)
INNER JOIN dbo.mt_fn_2014_CoreMenuInitialPopulation(@BeginDate, @EndDate, NULL-- Indicates do not use Admit or Discharge DateTime
, CASE WHEN UPPER(@EDMethod) = 'A' THEN 'IN_ER'-- All ED Visits Method
WHEN UPPER(@EDMethod) = 'O' THEN 'IN_INO'-- Observation Services Method
ELSE 'IN_ER'
END, @EnvironmentID) AS IP
ON IP.SourceID = OE.SourceID
AND IP.VisitID = OE.VisitID
That join is done right here
dbo.OeOrders AS OE
INNER JOIN dbo.DOeCategories AS OMC
ON OE.SourceID = OMC.SourceID
AND OE.Category = OMC.CategoryID
AND OE.OrderDateTime BETWEEN @FromDate AND @ThruDate
AND (
OMC.Interface IN ( SELECT
ParameterValue
FROM
dbo.#MedInterface )
OR OMC.Interface IN ( SELECT
ParameterValue
FROM
dbo.#LabInterface )
OR OMC.Interface IN ( SELECT
ParameterValue
FROM
dbo.#RadInterface )
)
AND OE.[Status] NOT IN ( 'CANC', 'UNVER' )
A where clause is not needed for a join. The where style join is the ANSI-89 style of join. It has fallen out of favor with ANSI-92 as outlined above.
That being said I don't this is going to be very efficient. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 8, 2013 at 5:28 am
Thanx. Would it be wrong to place a where clause after,
"AND OE.Category = OMC.CategoryID"
and include all of those nested Selects in the where clause?
October 8, 2013 at 5:30 am
So, I understand that a where clause isn't required in a join but, my understanding of a join is "comparing" a column from one table with a column from another column.
October 8, 2013 at 6:08 am
NineIron (10/8/2013)
So, I understand that a where clause isn't required in a join but, my understanding of a join is "comparing" a column from one table with a column from another column.
Isn't that the same thing?
Here's your query reformatted a little. It may be a little quicker too.
I'd recommend you take a peek at those two functions.
SELECT
IP.SourceID
,IP.FacilityID
,IP.AccountNumber
,IP.UnitNumber
,IP.Name AS PatientName
,IP.VisitID
,IP.PatientID
,IP.AdmitDateTime
,IP.DischargeDateTime
,IP.BirthDateTime
,IP.ErServiceDateTime
,IP.PtStatus
,OE.OrderID
,OE.OrderNumber
,DOP.[Name] AS [OrderName]
,OE.OrderDateTime
,OMC.Interface AS CategoryInterface
,OE2.OrdSourceID
/* Whether CPOE was used */
,CASE WHEN DARP.OeDefaultSourceID IS NOT NULL THEN 'Y'
ELSE NULL
END AS CPOE
INTO #TotalOrders
FROM dbo.OeOrders AS OE
INNER JOIN dbo.DOeCategories AS OMC
ON OE.SourceID = OMC.SourceID
AND OE.Category = OMC.CategoryID
AND OE.OrderDateTime BETWEEN @FromDate AND @ThruDate
AND OE.[Status] NOT IN ( 'CANC', 'UNVER' )
INNER JOIN dbo.DOeProcs AS DOP
ON OE.SourceID = DOP.SourceID
AND OE.Category = DOP.CategoryID
AND OE.OrderedProcedure = DOP.ProcedureID
INNER JOIN dbo.OeOrders2 AS OE2
ON OE.SourceID = OE2.SourceID
AND OE.OrderID = OE2.OrderID
LEFT OUTER JOIN (
dbo.DrArraAppReportParameters AS DARP
INNER JOIN dbo.mt_fn_2014_MG_NPRApplicationSourceIDs(@EnvironmentID, 'DR') AS DRSI
ON DARP.SourceID = DRSI.DatabaseSourceID
)
ON OE2.OrdSourceID = DARP.OeDefaultSourceID
INNER JOIN dbo.mt_fn_2014_CoreMenuInitialPopulation(
@BeginDate,
@EndDate,
NULL-- Indicates do not use Admit or Discharge DateTime
, CASE WHEN UPPER(@EDMethod) = 'A' THEN 'IN_ER'-- All ED Visits Method
WHEN UPPER(@EDMethod) = 'O' THEN 'IN_INO'-- Observation Services Method
ELSE 'IN_ER'
END,
@EnvironmentID
) AS IP
ON IP.SourceID = OE.SourceID
AND IP.VisitID = OE.VisitID
WHERE OMC.Interface IN (SELECT ParameterValue
FROM dbo.#MedInterface
UNION ALL
SELECT ParameterValue
FROM dbo.#LabInterface
UNION ALL
SELECT ParameterValue
FROM dbo.#RadInterface)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 8, 2013 at 6:23 am
Thanx.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply