Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I believe the error is at the end of your PIVOT:</p> <pre><code>) pr on Encounter.EncounterNumber = EncounterProc.[Encounter Number] ^-- this should be pr </code></pre> <p>So the code should be:</p> <pre><code>) pr on Encounter.EncounterNumber = pr.[Encounter Number] </code></pre> <p>Edit, based on your additional errors, it appears that you are not including the <code>[DATE OF SERVICE]</code> in your subquery. Also it doesn't make sense that you have a filter on the outside WHERE clause using the <code>[Procedure Code (ENCTR)]</code> because you are using that column in your <code>max()</code> on the PIVOT. Based on those guesses your code seems like it should be:</p> <pre><code>SELECT [Encounter Number], [AdmitDate - CCYYMMDD], [DischargeDate - CCYYMMDD], Encounter.LengthOfStay ,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, CASE WHEN [Procedure Code (ENCTR)] in ('01.25','01.14','01.59') then 'Brain' WHEN [Procedure Code (ENCTR)] = '03.09' then 'Spinal Canal' WHEN [Procedure Code (ENCTR)] in ('06.4','06.81','26.32') then 'Head/Neck' WHEN [Procedure Code (ENCTR)] in ('32.29','32.39','32.49','32.59') then 'Lungs/Thorax' WHEN [Procedure Code (ENCTR)] in ('35.12','35.21','35.22','35.23','35.24') then 'Cardiac Valve' WHEN [Procedure Code (ENCTR)] = '36.10' then 'CABG' WHEN [Procedure Code (ENCTR)] = '00.66' then 'PCI' WHEN [Procedure Code (ENCTR)] in ('37.33','37.34','37.35','37.36','37.37') then 'Excision Heart Lesion' WHEN [Procedure Code (ENCTR)] = '37.94' then 'Implant Auto Cardioversion/Defib System' WHEN [Procedure Code (ENCTR)] in ('39.52','39.71','39.78') then 'Abdominal Aortic Aneurysm' WHEN [Procedure Code (ENCTR)] in ('39.50','39.79','39.72','39.74','39.75','39.76','00.62') then 'Other Vascular' WHEN [Procedure Code (ENCTR)] = '38.12' then 'Carotid Endarterectomy' WHEN [Procedure Code (ENCTR)] in ('38.18','39.29') then 'Lower Limb Vascular' WHEN [Procedure Code (ENCTR)] in ('38.34','38.44','38.45','39.25','39.71') then 'Other Aortic Vascular' WHEN [Procedure Code (ENCTR)] in ('44.38','44.95','44.67') then 'Other Gastrointestinal' WHEN [Procedure Code (ENCTR)] in ('45.71','45.72','45.73','45.74','45.76','45.77','45.78','45.79','45.81','45.82','45.83','45.41', '45.42','45.43','45.44','45.45','45.46','45.47','45.48','45.49','48.35','48.63') then 'Colorectal' END as ProcGrouper From Encounter left outer join DimFactEncounter on Encounter.EncounterNumber = DimFactEncounter.EncounterNumber Left Outer JOIN ( SELECT [Encounter Number], [DATE OF SERVICE], [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] FROM ( SELECT [Encounter Number], [DATE OF SERVICE] ,[Procedure Code (Enctr)] ,Row_Number() OVER ( Partition By [Encounter Number] Order By [Encounter Number], [Procedure Code (Enctr)] ) AS RowNumber FROM EncounterProc where [Procedure Code (ENCTR)] in ('01.25','01.14','01.59','03.09','06.4','06.81','26.32','32.29','32.39','32.49', '32.59','35.12','35.21','35.22','35.23','35.24','36.10','00.66','37.33','37.34','37.35','37.36', '37.37','37.94','39.52','39.71','39.78','39.50','39.79','39.72','39.74','39.75','39.76','00.62', '38.12','38.18','39.29','38.34', '38.44','38.45','39.25','39.71','44.38','44.95','44.67','45.71','45.72','45.73','45.74','45.75', '45.76','45.77', '45.78','45.79','45.81','45.82','45.83','45.41','45.42','45.43','45.44','45.45','45.46','45.47' '45.48','45.49', '48.35','48.63') ) 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]) ) t ) pr on Encounter.EncounterNumber = pr.[Encounter Number] where pr.[Date of Service] between @StartDate and @EndDate and Encounter.InOutCode = 'I' and AdmitSubService &lt;&gt; 'SIG' and Encounter.HSP# = 1 and Encounter.ActualTotalCharge &gt; 0 and Encounter.Age &gt;= 65 and Encounter.PayorGroup = 'Medicare' and [ED Flag] is null and [DischargeDisposition] not in ('MA', 'TA', '7Z') </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POTSQL PIVOT is throwing multi-part identifier could not be bound
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USTaryn
    1. USTaryn
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POTSQL PIVOT is throwing multi-part identifier could not be bound
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks for this! Please see updates including both updated code and new errors.
      singulars
      1. PO
      1. USSidC
    2. CO@SidC Without seeing your table structures this would be guess work. Are you sure you are returning those columns in your query? Can you edit your question with the table structures?
      singulars
      1. PO
      1. USTaryn
    3. CO@SidC See my edit, you have to be sure that the columns in your where clause are included in your subqueries.
      singulars
      1. PO
      1. USTaryn
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload