February 27, 2014 at 9:16 am
I am using Server 2012 and very new to SQL. I have a request from a physician for a list of his patients that meet a criteria. This is stored in a temp table names #cohort.
Using this cohort he wants each row to be one patient with a list of labs, vitals, etc. Three items are the most recent lab value and date. I could query each lab individually and place it into a temp table and then join all temp tables at the end, but I am trying to move past that and have all labs in one temp table. All temp tables are joined with PatientSID.
I tried to do something for just 2 labs, but it is not working. There could be nulls values when joined with the #cohort table.
Individually the SELECT statements pull in the most recent lab value and date, but I cannot get them into a temp table with one row of PatientSID and then the lab value and date if they exist.
Any help would be appreciated.
IF OBJECT_ID ('TEMPDB..#lab') IS NOT NULL DROP TABLE #lab
SELECT
cohort.PatientSID
,SubQuery1.LabChemResultNumericValueAS 'A1c%'
,SubQuery1.LabChemCompleteDateTimeAS 'A1c% Date'
,SubQuery2.LabChemResultNumericValueAS 'LDL'
,SubQuery2.LabChemCompleteDateTimeAS 'LDL Date'
INTO #lab
FROM
#cohortAS cohort
INNER JOIN SubQuery1ON cohort.PatientSID = SubQuery1.PatientSID
INNER JOIN SubQuery2ON cohort.PatientSID = SubQuery2.PatientSID
(SELECT
S1.PatientSID
,s1.LabChemResultNumericValueAS 'A1c%'
,s1.LabChemCompleteDateTimeAS 'A1c% Date'
FROM
(SELECT
lab1.PatientSID
,lab1.LabChemResultNumericValue
,lab1.LabChemCompleteDateTime
,RANK() OVER (PARTITION BY lab1.PatientSID ORDER BY lab1.LabChemCompleteDateTime DESC) AS Lab1Rank
FROM
#cohortAS cohort3
INNER JOIN Chem.PatientLabChem AS lab1ON cohort3.PatientSID = lab1.PatientSID
WHERE
lab1.LabChemCompleteDateTime BETWEEN (GETDATE() - 365) AND GETDATE()
AND lab1.LabChemTestSID = 1000093199) AS S1-- Lab test = A1c%
WHERE
Lab1Rank = 1) AS SubQuery1
,
(SELECT
S2.PatientSID
,S2.LabChemResultNumericValueAS 'LDL'
,S2.LabChemCompleteDateTimeAS 'LDL Date'
FROM
(SELECT
lab2.PatientSID
,lab2.LabChemTestSID
,lab2.LabChemResultNumericValue
,lab2.LabChemCompleteDateTime
,RANK() OVER (PARTITION BY lab2.PatientSID ORDER BY lab2.LabChemCompleteDateTime DESC) AS Lab2Rank
FROM
#cohortAS cohort4
INNER JOIN Chem.PatientLabChem AS lab2ON cohort4.PatientSID = lab2.PatientSID
WHERE
lab2.LabChemCompleteDateTime BETWEEN (GETDATE() - 365) AND GETDATE()
AND lab2.LabChemTestSID = 1000087471) AS S2--Lab test = LDL MEASURED
WHERE
Lab2Rank = 1) AS SubQuery2
February 28, 2014 at 7:37 am
You have a lot going on here, but I'll give this a try anyhow.
My first observation is that I'd probably make your subqueries into CTEs, if just for separation of concerns.
If you leave them as subqueries, I'd observe that there's really no need to join in cohort3 or cohort4. You don't select columns from #cohort there, and you end up joining outside to #cohort anyway. Really, what it looks like you're trying to do is to make sure that the result in Chem.PatientLabChem matches to a PatientSID in #cohort. If you really need that, I'd suggest using EXISTS with a correlated subquery to #cohort.
Digging even further, the only thing you pull from #cohort in your outer query is PatientSID, which is available from either of your other two tables. It APPEARS that all we want from #cohort is to verify that we have a matching PatientSID. This could be accomplished easily, as I mentioned earlier, with an EXISTS clause. Here's my attempt at a query that will do what you want:
IF OBJECT_ID ('TEMPDB..#lab') IS NOT NULL DROP TABLE #lab
-- Table Aliases are great for helping us remember what we're
-- dealing with. SubQuery2 is easy to come up with, but
-- when it comes time to use it, it's not always easy to
-- remember that this table gives us ALC%.
with ALC as (
select PatientSID
, LabChemResultNumericValue
, LabChemCompleteDateTime
, RANK() OVER (PARTITION BY PatientSID ORDER BY LabChemCompleteDateTime DESC) as Rank
from Chem.PatientLabChem
where LabChemCompleteDateTime BETWEEN (GETDATE() - 365) and GETDATE()
and LabChemTestSID = 1000093199
),
LDL as (
select PatientSID
--, LabChemTestSID -- I commented this out because you don't use it later.
, LabChemResultNumericValue
, LabChemCompleteDateTime
, RANK() OVER (PARTITION BY PatientSID ORDER BY LabChemCompleteDateTime DESC) as Rank
from Chem.PatientLabChem
where LabChemCompleteDateTime BETWEEN (GETDATE() - 365) and GETDATE()
and LabChemTestSID = 1000087471
)
select ALC.PatientSID
, ALC.LabChemResultNumericValue as [Alc%]
, ALC.LabChemCompleteDateTime as [Alc% Date]
, LDL.LabChemResultNumericValue as [LDL]
, LDL.LabChemCompleteDateTime as [LDL Date]
into #lab
from ALC
inner join LDL
on ALC.PatientSID = LDL.PatientSID
and ALC.Rank = 1
and LDL.Rank = 1
-- This assumes we don't really care
-- how many rows are in #cohort for this
-- PatientSID, just that there is at least
-- one. Otherwise, we would probably still
-- want an inner join.
where exists (
select 1
from #cohort c
where ALC.PatientSID = c.PatientSID
)
This is much cleaner to read, and should give you the results you're expecting.
Edit: Forgot to add the temp table the first time.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply