Posted: 7/28/2011
Hi,
I have a table, EncounterDiag, with the following fields: Encounter Number, Secondary Diagnosis, Secondary Diag Sequence, and Present on Admisison Flag. I would like to create an SSIS package that reads the first 49 diagnoses and POA flags for each encounter that meets my selection criteria. This is basically a pivot as I need to create columns in the output named Encounter Number, DX1....DX49, POA1....POA49 where the package loops through Secondary Diag Sequence 1 through n up to 49.
I created an Execute SQL Task with the following:
SELECT MAX([SECONDARY DIAG SEQUENCE]) as DXs FROM ENCOUNTERDIAG
on Success, I have a For Loop Container with a Data Flow task inside it. The source is my EncounterDiag table and destination is an empty table in another database. I created two variables intCounter and intNumofDxs. In my OLEDB data source, I've used the following statement:
SELECT [ENCOUNTER NUMBER], [SECONDARY DIAGNOSIS], [SECONDARY DIAG SEQUENCE], [PRESENT ON ADMISSION FLAG] FROM ENCOUNTERDIAG WHERE (NumofDxs>= ?)
However, I receive invalid column name NumofDxs. Am I using the proper approach to loop through the diagnoses 1-49 for each encounter? If not, how do I modify my SSIS package to create the desired table layout below:
Encounter Number DX1 DX2 DX3....DX49 POA1 POA2 POA3....POA49 1111 999.9 777.7 888.38 N Y Y
Thanks,
Sid
Well, my response is going to be a bit long, so please bear with me.
What I'll show you is a T-SQL answer, but you could use the T-SQL ( modified for your column names, etc ) as a source query in an OLEDB source for SSIS.
First, let's set up some test data that will represent your current table.
----------------------------------------------------------------------------- -- I'm going to build this into a temp table -- but this uses master..sysmessages as a counting table. ----------------------------------------------------------------------------- IF EXISTS( SELECT TOP 1 1 Result FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#EncounterDiag' ) ) BEGIN DROP TABLE #EncounterDiag END go CREATE TABLE #EncounterDiag( EncounterNumber INT NOT NULL DEFAULT 1 , Diag VARCHAR( 9 ) NULL DEFAULT '999.9' , DiagSeq INT NULL DEFAULT 1 , POA VARCHAR( 1 ) NOT NULL DEFAULT 'Y' ) go INSERT #EncounterDiag( EncounterNumber , Diag , DiagSeq , POA ) SELECT 1 , RIGHT( '000' + ( RowNum * 1 ) , 3 ) + '.01' , CONVERT( VARCHAR , RowNum ) , 'Y' FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY msglangid ) AS RowNum FROM master..sysmessages ) sub WHERE sub.RowNum <= 55 UNION SELECT 2 , RIGHT( '000' + ( RowNum * 2 ) , 3 ) + '.01' , CONVERT( VARCHAR , ( RowNum ) ) , 'Y' FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY msglangid ) AS RowNum FROM master..sysmessages ) sub WHERE sub.RowNum <= 13 UNION SELECT 3 , RIGHT( '000' + ( RowNum * 3 ) , 3 ) + '.01' , CONVERT( VARCHAR , RowNum ) , 'Y' FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY msglangid ) AS RowNum FROM master..sysmessages ) sub WHERE sub.RowNum <= 26 UNION SELECT 4 , RIGHT( '000' + ( RowNum * 4 ) , 4 ) + '.01' , CONVERT( VARCHAR , RowNum * 7 ) , 'Y' FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY msglangid ) AS RowNum FROM master..sysmessages ) sub WHERE sub.RowNum <= 58 UNION SELECT 5 , RIGHT( '000' + ( RowNum * 5 ) , 3 ) + '.01' , CONVERT( VARCHAR , RowNum ) , 'Y' FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY msglangid ) AS RowNum FROM master..sysmessages ) sub WHERE sub.RowNum <= 12 go UPDATE #EncounterDiag SET POA = 'N' WHERE EncounterNumber = 4 AND Diag IN ( '188.01' , '172.01' , '232.01' -- this one is > 49, so you shouldn't see a 6th "N" in the sample rows.. , '168.01' , '72.01' , '96.01' ) go -- Here's the starting raw data that we'll pivot. SELECT EncounterNumber , Diag , DiagSeq , POA , ROW_NUMBER() OVER ( PARTITION BY EncounterNumber ORDER BY EncounterNumber , DiagSeq ) AS RowNumber -- assign them an ID in the order here.. FROM #EncounterDiag WHERE EncounterNumber IN ( 2 , 4 ) ORDER BY EncounterNumber , DiagSeq go
So that gets us to the table that you have where the data is NOT pivoted.
Take a look - it should give you 5 EncounterNumbers with varying numbers of diag codes. I am working in my example with EncounterNumbers 2 and 4. So what we'll do is PIVOT those 2 groups of data up into a row. I'm stealing the concepts from this URL: http://geekswithblogs.net/lorint/archive/2006/08/04/87166.aspx so definitely give that page a thorough reading - there is good info there.
I apologize for this being long, but I wanted to put all of the values in there so you could see it with the test data - all of the columns for both values.
SELECT p.EncounterNumber , p.[1] AS DX1 , p.[2] AS DX2 , p.[3] AS DX3 , p.[4] AS DX4 , p.[5] AS DX5 , p.[6] AS DX6 , p.[7] AS DX7 , p.[8] AS DX8 , p.[9] AS DX9 , p.[10] AS DX10 , p.[11] AS DX11 , p.[12] AS DX12 , p.[13] AS DX13 , p.[14] AS DX14 , p.[15] AS DX15 , p.[16] AS DX16 , p.[17] AS DX17 , p.[18] AS DX18 , p.[19] AS DX19 , p.[20] AS DX20 , p.[21] AS DX21 , p.[22] AS DX22 , p.[23] AS DX23 , p.[24] AS DX24 , p.[25] AS DX25 , p.[26] AS DX26 , p.[27] AS DX27 , p.[28] AS DX28 , p.[29] AS DX29 , p.[30] AS DX30 , p.[31] AS DX31 , p.[32] AS DX32 , p.[33] AS DX33 , p.[34] AS DX34 , p.[35] AS DX35 , p.[36] AS DX36 , p.[37] AS DX37 , p.[38] AS DX38 , p.[39] AS DX39 , p.[40] AS DX40 , p.[41] AS DX41 , p.[42] AS DX42 , p.[43] AS DX43 , p.[44] AS DX44 , p.[45] AS DX45 , p.[46] AS DX46 , p.[47] AS DX47 , p.[48] AS DX48 , p.[49] AS DX49 , a.[1] AS POA1 , a.[2] AS POA2 , a.[3] AS POA3 , a.[4] AS POA4 , a.[5] AS POA5 , a.[6] AS POA6 , a.[7] AS POA7 , a.[8] AS POA8 , a.[9] AS POA9 , a.[10] AS POA10 , a.[11] AS POA11 , a.[12] AS POA12 , a.[13] AS POA13 , a.[14] AS POA14 , a.[15] AS POA15 , a.[16] AS POA16 , a.[17] AS POA17 , a.[18] AS POA18 , a.[19] AS POA19 , a.[20] AS POA20 , a.[21] AS POA21 , a.[22] AS POA22 , a.[23] AS POA23 , a.[24] AS POA24 , a.[25] AS POA25 , a.[26] AS POA26 , a.[27] AS POA27 , a.[28] AS POA28 , a.[29] AS POA29 , a.[30] AS POA30 , a.[31] AS POA31 , a.[32] AS POA32 , a.[33] AS POA33 , a.[34] AS POA34 , a.[35] AS POA35 , a.[36] AS POA36 , a.[37] AS POA37 , a.[38] AS POA38 , a.[39] AS POA39 , a.[40] AS POA40 , a.[41] AS POA41 , a.[42] AS POA42 , a.[43] AS POA43 , a.[44] AS POA44 , a.[45] AS POA45 , a.[46] AS POA46 , a.[47] AS POA47 , a.[48] AS POA48 , a.[49] AS POA49 FROM ( SELECT * -- Yeah, I cheated here - it's ugly. I wouldn't do that in your production code. FROM ( SELECT EncounterNumber , Diag , ROW_NUMBER() OVER ( PARTITION BY EncounterNumber ORDER BY EncounterNumber , DiagSeq ) AS RowNumber -- assign them an ID in the order here.. FROM #EncounterDiag WHERE EncounterNumber IN ( 2 , 4 ) ) o PIVOT ( MAX( Diag ) FOR RowNumber IN ( [1] , [2] , [3] , [4] , [5] , [6] , [7] , [8] , [9] , [10] , [11] , [12] , [13] , [14] , [15] , [16] , [17] , [18] , [19] , [20] , [21] , [22] , [23] , [24] , [25] , [26] , [27] , [28] , [29] , [30] , [31] , [32] , [33] , [34] , [35] , [36] , [37] , [38] , [39] , [40] , [41] , [42] , [43] , [44] , [45] , [46] , [47] , [48] , [49] ) ) r ) p JOIN ( SELECT * -- Yeah, I cheated here - it's ugly. I wouldn't do that in your production code. FROM ( SELECT EncounterNumber , POA , ROW_NUMBER() OVER ( PARTITION BY EncounterNumber ORDER BY EncounterNumber , DiagSeq ) AS RowNumber -- assign them an ID in the order here.. FROM #EncounterDiag WHERE EncounterNumber IN ( 2 , 4 ) ) o PIVOT ( MAX( POA ) FOR RowNumber IN ( [1] , [2] , [3] , [4] , [5] , [6] , [7] , [8] , [9] , [10] , [11] , [12] , [13] , [14] , [15] , [16] , [17] , [18] , [19] , [20] , [21] , [22] , [23] , [24] , [25] , [26] , [27] , [28] , [29] , [30] , [31] , [32] , [33] , [34] , [35] , [36] , [37] , [38] , [39] , [40] , [41] , [42] , [43] , [44] , [45] , [46] , [47] , [48] , [49] ) ) t ) a ON p.EncounterNumber = a.EncounterNumber go
There are 2 sub-queries here, "p" and "a" that are doing the pivots for the Diag codes and the POAs. There may be a way to do them in a cleaner syntax, but I just did each as a sub-query and joined them.
Please note that I cheated and did a "SELECT *" in each sub-query. Make sure you take that out if you're going to modify it for production use.
Remember, you can use a select like this in an OLE DB Source adapter in your SSIS data flow - then it would just be a matter of ( lots of ) mapping.
Here's what the result looks like in my example ( just showing first 8 of each ):
Let me know how it goes and best of luck!
Keith Hyer
If that does the trick for you, please mark this post as "Answered".
Posted: 8/1/2011
Keith,
Thanks so much for your thorough response. I had been exchanging emails with Devin Knight regarding the use of the Pivot transform in SSIS. However, my colleagues and I see that your approach is a better one. I've adapted the code you provided to our database schema. Since I want to limit the selection criteria and need to join to another table, named Encounter, where do I include this join? Should it be appended to the end of the query? I'll be using parameters for discharge date start date and end date. As such, would this be best called from within a stored procedure? Or, encapsulated in an OLEDB data source in an SSIS package?
Here's your code updated with our schema:
SELECT p.[Encounter Number] , p.[1] AS DX1 , p.[2] AS DX2 , p.[3] AS DX3 , p.[4] AS DX4 , p.[5] AS DX5 , p.[6] AS DX6 , p.[7] AS DX7 , p.[8] AS DX8 , p.[9] AS DX9 , p.[10] AS DX10 , p.[11] AS DX11 , p.[12] AS DX12 , p.[13] AS DX13 , p.[14] AS DX14 , p.[15] AS DX15 , p.[16] AS DX16 , p.[17] AS DX17 , p.[18] AS DX18 , p.[19] AS DX19 , p.[20] AS DX20 , p.[21] AS DX21 , p.[22] AS DX22 , p.[23] AS DX23 , p.[24] AS DX24 , p.[25] AS DX25 , p.[26] AS DX26 , p.[27] AS DX27 , p.[28] AS DX28 , p.[29] AS DX29 , p.[30] AS DX30 , p.[31] AS DX31 , p.[32] AS DX32 , p.[33] AS DX33 , p.[34] AS DX34 , p.[35] AS DX35 , p.[36] AS DX36 , p.[37] AS DX37 , p.[38] AS DX38 , p.[39] AS DX39 , p.[40] AS DX40 , p.[41] AS DX41 , p.[42] AS DX42 , p.[43] AS DX43 , p.[44] AS DX44 , p.[45] AS DX45 , p.[46] AS DX46 , p.[47] AS DX47 , p.[48] AS DX48 , p.[49] AS DX49 , a.[1] AS POA1 , a.[2] AS POA2 , a.[3] AS POA3 , a.[4] AS POA4 , a.[5] AS POA5 , a.[6] AS POA6 , a.[7] AS POA7 , a.[8] AS POA8 , a.[9] AS POA9 , a.[10] AS POA10 , a.[11] AS POA11 , a.[12] AS POA12 , a.[13] AS POA13 , a.[14] AS POA14 , a.[15] AS POA15 , a.[16] AS POA16 , a.[17] AS POA17 , a.[18] AS POA18 , a.[19] AS POA19 , a.[20] AS POA20 , a.[21] AS POA21 , a.[22] AS POA22 , a.[23] AS POA23 , a.[24] AS POA24 , a.[25] AS POA25 , a.[26] AS POA26 , a.[27] AS POA27 , a.[28] AS POA28 , a.[29] AS POA29 , a.[30] AS POA30 , a.[31] AS POA31 , a.[32] AS POA32 , a.[33] AS POA33 , a.[34] AS POA34 , a.[35] AS POA35 , a.[36] AS POA36 , a.[37] AS POA37 , a.[38] AS POA38 , a.[39] AS POA39 , a.[40] AS POA40 , a.[41] AS POA41 , a.[42] AS POA42 , a.[43] AS POA43 , a.[44] AS POA44 , a.[45] AS POA45 , a.[46] AS POA46 , a.[47] AS POA47 , a.[48] AS POA48 , a.[49] AS POA49 FROM ( SELECT * -- Yeah, I cheated here - it's ugly. I wouldn't do that in your production code. FROM ( SELECT [ENCOUNTER NUMBER] , [SECONDARY DIAGNOSIS] , ROW_NUMBER() OVER ( PARTITION BY [ENCOUNTER NUMBER] ORDER BY [Encounter Number] , [SECONDARY DIAG SEQUENCE] ) AS RowNumber -- assign them an ID in the order here.. FROM EncounterDiag) o PIVOT ( MAX( [SECONDARY DIAGNOSIS] ) FOR RowNumber IN ( [1] , [2] , [3] , [4] , [5] , [6] , [7] , [8] , [9] , [10] , [11] , [12] , [13] , [14] , [15] , [16] , [17] , [18] , [19] , [20] , [21] , [22] , [23] , [24] , [25] , [26] , [27] , [28] , [29] , [30] , [31] , [32] , [33] , [34] , [35] , [36] , [37] , [38] , [39] , [40] , [41] , [42] , [43] , [44] , [45] , [46] , [47] , [48] , [49] ) ) r ) p JOIN ( SELECT * -- Yeah, I cheated here - it's ugly. I wouldn't do that in your production code. FROM ( SELECT [Encounter Number] , [PRESENT ON ADMISSION FLAG] , ROW_NUMBER() OVER ( PARTITION BY [Encounter Number] ORDER BY [Encounter Number] , [SECONDARY DIAGNOSIS] ) AS RowNumber -- assign them an ID in the order here.. FROM EncounterDiag) o PIVOT ( MAX( [PRESENT ON ADMISSION FLAG] ) FOR RowNumber IN ( [1] , [2] , [3] , [4] , [5] , [6] , [7] , [8] , [9] , [10] , [11] , [12] , [13] , [14] , [15] , [16] , [17] , [18] , [19] , [20] , [21] , [22] , [23] , [24] , [25] , [26] , [27] , [28] , [29] , [30] , [31] , [32] , [33] , [34] , [35] , [36] , [37] , [38] , [39] , [40] , [41] , [42] , [43] , [44] , [45] , [46] , [47] , [48] , [49] ) ) t ) a ON p.[Encounter Number] = a.[Encounter Number] go
schilders said: I had been exchanging emails with Devin Knight regarding the use of the Pivot transform in SSIS. However, my colleagues and I see that your approach is a better one.
I had been exchanging emails with Devin Knight regarding the use of the Pivot transform in SSIS. However, my colleagues and I see that your approach is a better one.
I'd like to accept that compliment, but keep in mind - I'd wager that when it comes to SSIS, Devin is probably more knowledgable than I - after all, I stole "my" method from that weblink that I sited.
If he gives you another method - and you have time - definitely benchmark them both. It may seem more complex up front - but the performance benefits may be significent and worth the extra time.
schilders said: Since I want to limit the selection criteria and need to join to another table, named Encounter, 1. where do I include this join? 2. Should it be appended to the end of the query? I'll be using parameters for discharge date start date and end date. As such, 3. would this be best called from within a stored procedure? 4. Or, encapsulated in an OLEDB data source in an SSIS package?
Since I want to limit the selection criteria and need to join to another table, named Encounter, 1. where do I include this join? 2. Should it be appended to the end of the query? I'll be using parameters for discharge date start date and end date. As such, 3. would this be best called from within a stored procedure? 4. Or, encapsulated in an OLEDB data source in an SSIS package?
I'll try to answer those in order.
1. As far as I know, you will want to join the Encounter table at the bottom on the current "FROM" / "JOIN" clause. The PIVOT is a special clause that ( as far as I know ) SQL treats at a part of the preceeding table. So I would try after the last current table.
2. Yes - that is a guess and based off of information from the link to the originating article.
3. Personally, I'd say put the query in a stored procedure. It makes it easier to update it if you find you need to do so. The SQL engine can also determine an optimal query plan for you this way.
4. You could, but I think "3" is the better option personally. I do have some of my queries in data sources - but I don't often admin it.
If you do try both methods - let us know how it goes! I didn't try the pivot component in SSIS, so I would welcome hearing which one performs better for you.
Hope it helps and good luck!
I've gone with the stored procedure approach and have updated your syntax to reflect the various joins, fields and where clause. Note that I need to pivot up to 49 procedure codes as well. I've attempted to adapt your syntax but receive an error near the where clause. Can you reivew and help me identify where my syntax is incorrect?
USE [DS_TSI] GO /****** Object: StoredProcedure [dbo].[getClaroPtDetail] Script Date: 08/01/2011 14:31:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[getClaroPtDetail] -- Add the parameters for the stored procedure here @StartDate varchar(15), @EndDate varchar(15) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT p.[Encounter Number] , MedicalRecordNumber ,[PERSON FIRST NAME] ,[PERSON LAST NAME] ,[AdmitDate - CCYYMMDD] ,[DischargeDate - CCYYMMDD] ,LengthOfStay ,[PERSON BIRTHDATE] ,Age ,Sex ,DischargeDisposition ,[MS-DRG - AFTER 10 01 2007] ,ActualTotalCharge , p.[1] AS DX1 , p.[2] AS DX2 , p.[3] AS DX3 , p.[4] AS DX4 , p.[5] AS DX5 , p.[6] AS DX6 , p.[7] AS DX7 , p.[8] AS DX8 , p.[9] AS DX9 , p.[10] AS DX10 , p.[11] AS DX11 , p.[12] AS DX12 , p.[13] AS DX13 , p.[14] AS DX14 , p.[15] AS DX15 , p.[16] AS DX16 , p.[17] AS DX17 , p.[18] AS DX18 , p.[19] AS DX19 , p.[20] AS DX20 , p.[21] AS DX21 , p.[22] AS DX22 , p.[23] AS DX23 , p.[24] AS DX24 , p.[25] AS DX25 , p.[26] AS DX26 , p.[27] AS DX27 , p.[28] AS DX28 , p.[29] AS DX29 , p.[30] AS DX30 , p.[31] AS DX31 , p.[32] AS DX32 , p.[33] AS DX33 , p.[34] AS DX34 , p.[35] AS DX35 , p.[36] AS DX36 , p.[37] AS DX37 , p.[38] AS DX38 , p.[39] AS DX39 , p.[40] AS DX40 , p.[41] AS DX41 , p.[42] AS DX42 , p.[43] AS DX43 , p.[44] AS DX44 , p.[45] AS DX45 , p.[46] AS DX46 , p.[47] AS DX47 , p.[48] AS DX48 , p.[49] AS DX49 , a.[1] AS POA1 , a.[2] AS POA2 , a.[3] AS POA3 , a.[4] AS POA4 , a.[5] AS POA5 , a.[6] AS POA6 , a.[7] AS POA7 , a.[8] AS POA8 , a.[9] AS POA9 , a.[10] AS POA10 , a.[11] AS POA11 , a.[12] AS POA12 , a.[13] AS POA13 , a.[14] AS POA14 , a.[15] AS POA15 , a.[16] AS POA16 , a.[17] AS POA17 , a.[18] AS POA18 , a.[19] AS POA19 , a.[20] AS POA20 , a.[21] AS POA21 , a.[22] AS POA22 , a.[23] AS POA23 , a.[24] AS POA24 , a.[25] AS POA25 , a.[26] AS POA26 , a.[27] AS POA27 , a.[28] AS POA28 , a.[29] AS POA29 , a.[30] AS POA30 , a.[31] AS POA31 , a.[32] AS POA32 , a.[33] AS POA33 , a.[34] AS POA34 , a.[35] AS POA35 , a.[36] AS POA36 , a.[37] AS POA37 , a.[38] AS POA38 , a.[39] AS POA39 , a.[40] AS POA40 , a.[41] AS POA41 , a.[42] AS POA42 , a.[43] AS POA43 , a.[44] AS POA44 , a.[45] AS POA45 , a.[46] AS POA46 , a.[47] AS POA47 , a.[48] AS POA48 , a.[49] AS POA49 , pr.[1] as PROC1 , pr.[2] as PROC2 , pr.[3] as PROC3 , pr.[4] as PROC4 , pr.[5] as PROC5 , pr.[6] as PROC6 , pr.[7] as PROC7 , pr.[8] as PROC8 , pr.[9] as PROC9 , pr.[10] as PROC10 , pr.[11] as PROC11 , pr.[12] as PROC12 , pr.[13] as PROC13 , pr.[14] as PROC14 , pr.[15] as PROC15 , pr.[16] as PROC16 , pr.[17] as PROC17 , pr.[18] as PROC18 , pr.[19] as PROC19 , pr.[20] as PROC20 , pr.[21] as PROC21 , pr.[22] as PROC22 , pr.[23] as PROC23 , pr.[24] as PROC24 , pr.[25] as PROC25 , pr.[26] as PROC26 , pr.[27] as PROC27 , pr.[28] as PROC28 , pr.[29] as PROC29 , pr.[30] as PROC30 , pr.[31] as PROC31 , pr.[32] as PROC32 , pr.[33] as PROC33 , pr.[34] as PROC34 , pr.[35] as PROC35 , pr.[36] as PROC36 , pr.[37] as PROC37 , pr.[38] as PROC38 , pr.[39] as PROC39 , pr.[40] as PROC40 , pr.[41] as PROC41 , pr.[42] as PROC42 , pr.[43] as PROC43 , pr.[44] as PROC44 , pr.[45] as PROC45 , pr.[46] as PROC46 , pr.[47] as PROC47 , pr.[48] as PROC48 , pr.[49] as PROC49 FROM ( SELECT * -- Yeah, I cheated here - it's ugly. I wouldn't do that in your production code. FROM ( SELECT [ENCOUNTER NUMBER] , [SECONDARY DIAGNOSIS] , ROW_NUMBER() OVER ( PARTITION BY [ENCOUNTER NUMBER] ORDER BY [Encounter Number] , [SECONDARY DIAG SEQUENCE] ) AS RowNumber -- assign them an ID in the order here.. FROM EncounterDiag) o PIVOT ( MAX( [SECONDARY DIAGNOSIS] ) FOR RowNumber IN ( [1] , [2] , [3] , [4] , [5] , [6] , [7] , [8] , [9] , [10] , [11] , [12] , [13] , [14] , [15] , [16] , [17] , [18] , [19] , [20] , [21] , [22] , [23] , [24] , [25] , [26] , [27] , [28] , [29] , [30] , [31] , [32] , [33] , [34] , [35] , [36] , [37] , [38] , [39] , [40] , [41] , [42] , [43] , [44] , [45] , [46] , [47] , [48] , [49] ) ) r ) p JOIN ( SELECT * -- Yeah, I cheated here - it's ugly. I wouldn't do that in your production code. FROM ( SELECT [Encounter Number] , [PRESENT ON ADMISSION FLAG] , ROW_NUMBER() OVER ( PARTITION BY [Encounter Number] ORDER BY [Encounter Number] , [SECONDARY DIAGNOSIS] ) AS RowNumber -- assign them an ID in the order here.. FROM EncounterDiag) o PIVOT ( MAX( [PRESENT ON ADMISSION FLAG] ) FOR RowNumber IN ( [1] , [2] , [3] , [4] , [5] , [6] , [7] , [8] , [9] , [10] , [11] , [12] , [13] , [14] , [15] , [16] , [17] , [18] , [19] , [20] , [21] , [22] , [23] , [24] , [25] , [26] , [27] , [28] , [29] , [30] , [31] , [32] , [33] , [34] , [35] , [36] , [37] , [38] , [39] , [40] , [41] , [42] , [43] , [44] , [45] , [46] , [47] , [48] , [49] ) ) t ) a JOIN (SELECT * FROM (SELECT [ENCOUNTER NUMBER] ,[PROCEDURE CODE (ENCTR)] ,ROW_NUMBER() OVER ( PARTITION BY [ENCOUNTER NUMBER] ORDER BY [ENCOUNTER NUMBER], [ENCOUNTER PROC SEQUENCE]) AS RowNumber from EncounterProc) o PIVOT (MAX([PROCEDURE CODE (ENCTR)]) FOR RowNumber IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10] ,[11], [12], [13], [14], [15], [16], [17], [18], [19] ,[20], [21], [22], [23], [24], [25], [26], [27], [28], [29] ,[30], [31], [32], [33], [34], [35], [36], [37], [38], [39] ,[40], [41], [42], [43], [44], [45], [46], [47], [48], [49])) r ) pr ON p.[Encounter Number] = a.[Encounter Number] Inner Join Encounter on p.[Encounter Number] = Encounter.EncounterNumber Inner Join Patient_MEDRCD on Encounter.PatientSK = Patient_MEDRCD.PatientSK where HSP#='1' and InOutCode='I' and InsurancePlanCode='M01' and AdmitSubService <>'SIG' and [DischargeDate - CCYYMMDD] between @StartDate and @EndDate END
Posted: 8/2/2011
schilders said: Can you reivew and help me identify where my syntax is incorrect?
Can you reivew and help me identify where my syntax is incorrect?
Well, I don't have a table that matches your structure - so I did not test this, but at first glance, it might be the location of the first "ON" clause within the JOIN portion of the query.
I put a red box where it was - this shows where I moved it up to.
That is where I'd look first.
Hope it helps,
That was the ticket:) After moving the ON clause, I was able to execute the stored procedure and now have pivoted diagnoses, POAs and procedures. Thanks again for all your help!!!