create one temp table from nested subqueries 1

waubain · Feb 27, 2014

I am using Server 2012 and 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 I named #cohort. It only contains PatientSID (85 records).

I want one row per patient with a list of about 10+ labs, vitals, etc as columns. Three items are the most recent labs with value and date. All labs are stored in the same table. 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 (for now). All temp tables are joined with PatientSID to build the final table for export.

I tried to do something for just 2 labs, but it is not working. There could be nulls values for labs 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. Thanks. Since I have other queries that are very similar, I am open to how to do this more efficiently.

Here is a convoluted attempt at 2 labs. The question: For each patient in #cohort list the most recent A1c% and LDL values with their respective dates, if they exist.

SQL:

IF OBJECT_ID ('TEMPDB..#lab') IS NOT NULL DROP TABLE #lab

SELECT

	cohort.PatientSID
	,SubQuery1.LabChemResultNumericValue			AS 'A1c%'
	,SubQuery1.LabChemCompleteDateTime			AS 'A1c% Date'
	,SubQuery2.LabChemResultNumericValue			AS 'LDL'
	,SubQuery2.LabChemCompleteDateTime			AS 'LDL Date'

INTO #lab

FROM

	#cohort		AS cohort

	INNER JOIN SubQuery1		ON cohort.PatientSID = SubQuery1.PatientSID
	INNER JOIN SubQuery2		ON cohort.PatientSID = SubQuery2.PatientSID


		(SELECT
			S1.PatientSID
			,s1.LabChemResultNumericValue			AS 'A1c%'
			,s1.LabChemCompleteDateTime			AS 'A1c% Date'
		FROM
			(SELECT 
	
			lab1.PatientSID
			,lab1.LabChemResultNumericValue
			,lab1.LabChemCompleteDateTime
			,RANK() OVER (PARTITION BY lab1.PatientSID ORDER BY lab1.LabChemCompleteDateTime DESC) AS Lab1Rank

			FROM

			#cohort	AS cohort3

			INNER JOIN  Chem.PatientLabChem AS lab1	ON 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.LabChemResultNumericValue			AS 'LDL'
		,S2.LabChemCompleteDateTime			AS '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
			#cohort	AS cohort4		
			INNER JOIN Chem.PatientLabChem AS lab2		ON 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

You don't know what you don't know...

gk53 · Feb 27, 2014

in this case it should be something like

SQL:

IF OBJECT_ID ('TEMPDB..#lab') IS NOT NULL DROP TABLE #lab

SELECT	cohort.PatientSID
		,SubQuery1.LabChemResultNumericValue	AS 'A1c%'
		,SubQuery1.LabChemCompleteDateTime		AS 'A1c% Date'
		,SubQuery2.LabChemResultNumericValue	AS 'LDL'
		,SubQuery2.LabChemCompleteDateTime		AS 'LDL Date'
INTO #lab
FROM #cohort	AS	cohort
	left JOIN 
			(SELECT S1.PatientSID
				,s1.LabChemResultNumericValue	AS 'A1c%'
				,s1.LabChemCompleteDateTime		AS 'A1c% Date'
		FROM
			(SELECT 
	
			lab1.PatientSID
			,lab1.LabChemResultNumericValue
			,lab1.LabChemCompleteDateTime
			,RANK() OVER (PARTITION BY lab1.PatientSID ORDER BY lab1.LabChemCompleteDateTime DESC) AS Lab1Rank
			FROM #cohort	AS cohort3
			INNER JOIN  Chem.PatientLabChem AS lab1	
				ON 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
	ON cohort.PatientSID	=	SubQuery1.PatientSID
	left JOIN (
			SELECT S2.PatientSID
				,S2.LabChemResultNumericValue	AS 'LDL'
				,S2.LabChemCompleteDateTime		AS '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 #cohort	AS	cohort4		
			INNER JOIN Chem.PatientLabChem	AS	lab2
				ON 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 
	ON cohort.PatientSID	=	SubQuery2.PatientSID

first at all left join and second include sub query inside your sql

gmmastros · Feb 27, 2014

Try this:

Code:

; With RecentLabs As
(
  Select  Chem.PatientLabChem.PatientSID,
          Chem.PatientLabChem.LabChemResultNumericValue,
          Chem.PatientLabChem.LabChemCompleteDateTime,
          Chem.PatientLabChem.LabChemTestSID,
          Row_Number() Over (Partition By Chem.PatientLabChem.PatientSid, 
                                          Chem.PatientLabChem.LabChemTestSID
                             Order By Chem.PatientLabChem.LabChemCompleteDateTime DESC) As RowId
  From    Chem.PatientLabChem
          Inner Join #Cohort 
            On Chem.PatientLabChem.PatientSID = #Cohort.PatientSid
  Where   Chem.PatientLabChem.LabChemTestSID In ([!]1000093199,1000087471[/!])
)
Select  RecentLabs.PatientSID,

        -- A1C stuff
        Min(Case When LabChemTestSID = 1000093199 Then LabChemResultNumericValue End ) As [A1c%],
        Min(Case When LabChemTestSID = 1000093199 Then LabChemCompleteDateTime End ) As [A1c% Date],

        -- LDL Stuff
        Min(Case When LabChemTestSID = 1000087471 Then LabChemResultNumericValue End ) As [LDL],
        Min(Case When LabChemTestSID = 1000087471 Then LabChemCompleteDateTime End ) As [LDL Date]
Where   RowId = 1
Group By RecentLabs.PatientSID

If this works properly, then all you should need to do is to modify the part in red to include more Lab SIDs and then repeat the part near the bottom when I have the commends A1C stuff and LDL stuff.

Basically, your originally query was partitioning by patient. This version is partitioning by patient and Lab SID. So each patient will have multiple rows in the first part (the CTE, but since the partitioning is by patient and Lab SID combination, the 1's will be for the newest test for all their tests. The bottom part (after the CTE) is plain vanilla old school pivoting. I think this query will give you the same results but should also perform a lot better.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

gmmastros · Feb 27, 2014

I forgot to put in the part about limiting the tests to the last 365 days.

Code:

; With RecentLabs As
(
  Select  Chem.PatientLabChem.PatientSID,
          Chem.PatientLabChem.LabChemResultNumericValue,
          Chem.PatientLabChem.LabChemCompleteDateTime,
          Chem.PatientLabChem.LabChemTestSID,
          Row_Number() Over (Partition By Chem.PatientLabChem.PatientSid, 
                                          Chem.PatientLabChem.LabChemTestSID
                             Order By Chem.PatientLabChem.LabChemCompleteDateTime DESC
                             ) As RowId
  From    Chem.PatientLabChem
          Inner Join #Cohort 
            On Chem.PatientLabChem.PatientSID = #Cohort.PatientSid
  Where   Chem.PatientLabChem.LabChemTestSID In (1000093199,1000087471)
          And Chem.PatientLabChem.LabChemCompleteDateTime 
              BETWEEN (GETDATE() - 365) AND GETDATE()
)
Select  RecentLabs.PatientSID,

        -- A1C stuff
        Min(Case When LabChemTestSID = 1000093199 
                 Then LabChemResultNumericValue 
                 End) As [A1c%],
        Min(Case When LabChemTestSID = 1000093199 
                 Then LabChemCompleteDateTime 
                 End) As [A1c% Date],

        -- LDL Stuff
        Min(Case When LabChemTestSID = 1000087471 
                 Then LabChemResultNumericValue 
                 End) As [LDL],
        Min(Case When LabChemTestSID = 1000087471 
                 Then LabChemCompleteDateTime 
                 End) As [LDL Date]
Where   RowId = 1
Group By RecentLabs.PatientSID

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

waubain · Feb 27, 2014

I voluntarily jumped into the deep end of the SQL pool, so I very much appreciate your responses.
I do not have access to our data warehouse from home, but will try this out tomorrow. Their is a lot here to read about tonight.

George: This part of the query only retrieves 3 pieces of information out of many. When I want to pull this all together at he end to build the final table, how would I refer back to this query using your example. Can I add an INTO #lab like this?:

SQL:

IF OBJECT_ID ('TEMPDB..#lab') IS NOT NULL DROP TABLE #lab
; With RecentLabs As
(
  Select  Chem.PatientLabChem.PatientSID,
          Chem.PatientLabChem.LabChemResultNumericValue,
          Chem.PatientLabChem.LabChemCompleteDateTime,
          Chem.PatientLabChem.LabChemTestSID,
          Row_Number() Over (Partition By Chem.PatientLabChem.PatientSid, 
                                          Chem.PatientLabChem.LabChemTestSID
                             Order By Chem.PatientLabChem.LabChemCompleteDateTime DESC
                             ) As RowId
INTO #lab

...remainder of your code

You don't know what you don't know...

gmmastros · Feb 27, 2014

waubain,

that would be the wrong place. It would be like this:

Code:

; With RecentLabs As
(
  Select  Chem.PatientLabChem.PatientSID,
          Chem.PatientLabChem.LabChemResultNumericValue,
          Chem.PatientLabChem.LabChemCompleteDateTime,
          Chem.PatientLabChem.LabChemTestSID,
          Row_Number() Over (Partition By Chem.PatientLabChem.PatientSid, 
                                          Chem.PatientLabChem.LabChemTestSID
                             Order By Chem.PatientLabChem.LabChemCompleteDateTime DESC
                             ) As RowId
  From    Chem.PatientLabChem
          Inner Join #Cohort 
            On Chem.PatientLabChem.PatientSID = #Cohort.PatientSid
  Where   Chem.PatientLabChem.LabChemTestSID In (1000093199,1000087471)
          And Chem.PatientLabChem.LabChemCompleteDateTime 
              BETWEEN (GETDATE() - 365) AND GETDATE()
)
Select  RecentLabs.PatientSID,

        -- A1C stuff
        Min(Case When LabChemTestSID = 1000093199 
                 Then LabChemResultNumericValue 
                 End) As [A1c%],
        Min(Case When LabChemTestSID = 1000093199 
                 Then LabChemCompleteDateTime 
                 End) As [A1c% Date],

        -- LDL Stuff
        Min(Case When LabChemTestSID = 1000087471 
                 Then LabChemResultNumericValue 
                 End) As [LDL],
        Min(Case When LabChemTestSID = 1000087471 
                 Then LabChemCompleteDateTime 
                 End) As [LDL Date]
[!]Into    #Labs[/!]
From    RecentLabs
Where   RowId = 1
Group By RecentLabs.PatientSID

However... if you don't need to put the data in to a temp table, then don't. Most of the time, putting data in to a temp table and then querying from it is slower than building a "big ole query". You see, under the covers, SQL Server will put the data in your temp table in to the TempDB and actually write that data to the hard drive. When you build a big query that does not use temp tables, SQL can skip this step and therefore generate the results faster (usually).

There was a time for me (several years ago) when I liked to put a lot of intermediate results in to temp tables. It allows you to think about the final results in smaller chunks, and is therefore easier to build a complex procedure. Did you ever see that show on tv where they tear a house down and rebuild it in a week while the family is vacationing at Disney World? That's kinda what building a big query is like. There's a lot of moving parts that you need to take in to account. Using the temp table method is more like the way you or I would build a house. Dig a hole, pour a foundation, etc... one step at a time. It would take longer than a week, but it would eventually get there. You see, SQL server is like the host on that show. It's really good a coordinating complicated things to get the correct results. It will happily execute queries slower if you tell it to, but it's just as happy to run complex queries.

With SQL server 2005, Microsoft introduced common table expressions. They allow you to write your query in smaller chunks but still have the benefit of not requiring temp tables.

For example, you have a query that puts data in to the #cohorts table, right? The query I show basically has 2 steps. The first step gets the recent lab work, and the second step pivots the data so that all the results for each patient are on the same row. The basic structure of common table expressions is this...

Code:

; With AliasNameForPart1 As
(
    -- Query goes here
), AliasNameForPart2 As
(
    -- Another query goes here
), AliasNameForPart3 As
(
    -- Another query here
)
Select Columns
From   AliasNameForPart1 As Part1
       Inner Join AliasNAmeForPart2 As Part2
          On Part1.Column = Part2.Column
       Inner Join AliasNameForPart3 As Part3
          On Part1.Column = Part3.Column

Basically, the code you use to insert in to temp tables could go in to commented part of the code above, and the common table expressions can build upon them. Something like this:

Code:

; With Cohort As
(
  -- Your query to get the patients
), RecentLabs As
(
  Select  Chem.PatientLabChem.PatientSID,
          Chem.PatientLabChem.LabChemResultNumericValue,
          Chem.PatientLabChem.LabChemCompleteDateTime,
          Chem.PatientLabChem.LabChemTestSID,
          Row_Number() Over (Partition By Chem.PatientLabChem.PatientSid, 
                                          Chem.PatientLabChem.LabChemTestSID
                             Order By Chem.PatientLabChem.LabChemCompleteDateTime DESC
                             ) As RowId
  From    Chem.PatientLabChem
          Inner Join Cohort 
            On Chem.PatientLabChem.PatientSID = #Cohort.PatientSid
  Where   Chem.PatientLabChem.LabChemTestSID In (1000093199,1000087471)
          And Chem.PatientLabChem.LabChemCompleteDateTime 
              BETWEEN (GETDATE() - 365) AND GETDATE()
)
Select  RecentLabs.PatientSID,

        -- A1C stuff
        Min(Case When LabChemTestSID = 1000093199 
                 Then LabChemResultNumericValue 
                 End) As [A1c%],
        Min(Case When LabChemTestSID = 1000093199 
                 Then LabChemCompleteDateTime 
                 End) As [A1c% Date],

        -- LDL Stuff
        Min(Case When LabChemTestSID = 1000087471 
                 Then LabChemResultNumericValue 
                 End) As [LDL],
        Min(Case When LabChemTestSID = 1000087471 
                 Then LabChemCompleteDateTime 
                 End) As [LDL Date]
From    RecentLabs
Where   RowId = 1
Group By RecentLabs.PatientSID

Now, if you look at the last part, there are 5 columns being returned by the final query. This part is only looking at the RecentLabs part, but there's aggregates and a group by. This will effectively take your intermediate results with 3 columns and make 5 columns from it. Since there is only the PatientSID in the group by, you are guaranteed to get just one row per patient.

I admit that this is a complicated query with a couple of advanced features being used. If there's any part that you don't understand, let me know and I will attempt to explain.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

waubain · Feb 28, 2014

George,
Thank you for the detailed explaination. I studied it and ran the inner queries to see what each step did. The query worked perfectly.

I then tried the same query on blood pressure, weight and height and tried to calculate BMI from the height and weight. Including the the CAST statement immediately after the MIN(Case..) did not work. I guess I do not understand exactly when a variable becomes populated. Anyway I got it to work by creating another query using the stucture you recommended and then did the calculation at the very end.

Is this the best way or can it be done in 2 queries? Just trying to develop some best practices.

Thanks again. This was the working statements.

SQL:

; With RecentVitals As
(
  Select  Vital.VitalSign.PatientSID,
		  Vital.VitalSign.Result,
          Vital.VitalSign.ResultNumeric,
          Vital.VitalSign.VitalSignEnteredDateTime,
          Vital.VitalSign.VitalTypeSID,
          Row_Number() Over (Partition By Vital.VitalSign.PatientSID, 
                                          Vital.VitalSign.VitalTypeSID
                             Order By Vital.VitalSign.VitalSignEnteredDateTime DESC
                             ) As RowId
  From    Vital.VitalSign
          INNER JOIN #Cohort 
            ON Vital.VitalSign.PatientSID = #Cohort.PatientSID
  Where   Vital.VitalSign.VitalTypeSID In (1000000382,1000000400,1000000401)
          And Vital.VitalSign.VitalSignEnteredDateTime 
              BETWEEN (GETDATE() - 1095) AND GETDATE()		--Back 3 years
)
, CalcBMI AS
(
Select  RecentVitals.PatientSID,

        -- Height
        Min(Case When VitalTypeSID = 1000000400 
                 Then ResultNumeric 
                 End) As [Height],
        Min(Case When VitalTypeSID = 1000000400  
                 Then VitalSignEnteredDateTime 
                 End) As [Height Date],
		-- Weight
        Min(Case When VitalTypeSID = 1000000401 
                 Then ResultNumeric 
                 End) As [Weight],
        Min(Case When VitalTypeSID = 1000000401  
                 Then VitalSignEnteredDateTime 
                 End) As [Weight Date],
	-- BP
        Min(Case When VitalTypeSID = 1000000382 
                 Then Result 
                 End) As [BP],
        Min(Case When VitalTypeSID = 1000000382  
                 Then VitalSignEnteredDateTime 
                 End) As [BP Date]
From    RecentVitals
Where   RowId = 1
Group By RecentVitals.PatientSID
)
SELECT 
	cohort.PatientSID 
	,vital.[Height]
	,vital.[Weight]
	,CAST((vital.[WEIGHT] * 703) / (vital.[HEIGHT] * vital.[HEIGHT]) AS decimal(38,1))	AS 'BMI'
FROM #cohort AS cohort
	INNER JOIN CalcBMI as vital ON cohort.PatientSID = vital.PatientSID
ORDER BY
	cohort.PatientSID

You don't know what you don't know...

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

create one temp table from nested subqueries 1

waubain

Technical User

gk53

Programmer

gmmastros

Programmer

gmmastros

Programmer

waubain

Technical User

gmmastros

Programmer

waubain

Technical User

Similar threads

Part and Inventory Search

Sponsor