Help with a script

Hello all

I have this script that i have that had to be altered. Once I altered it I am getting errors. This is the original script:

    ````SELECT FY 
     , PatientMRN AS MRN
	 , Race
INTO #Race
FROM
(SELECT DISTINCT
	Race_Num_flg.*
	,ROW_NUMBER() OVER (PARTITION BY FY, PatientMRN ORDER BY RACE_num_flg ASC) AS Race_row
	,CASE WHEN PatientRaceDSC = 'ASIAN' THEN 'Asian'
		  WHEN PatientRaceDSC = 'BLACK OR AFRICAN AMERICAN' THEN 'Black or African American'
		  WHEN PatientRaceDSC = 'Hispanic or Latino' THEN 'Hispanic or Latino'
		  WHEN PatientRaceDSC LIKE ('WHITE%') THEN 'White or Caucasian'
		  --WHEN PatientRaceDSC = 'American Indian or Alaska Native' THEN 'American Indian or Alaska Native'
		  --WHEN PatientRaceDSC = 'Native Hawaiian or Other Pacific Islander' THEN 'Native Hawaiian or Other Pacific Islander'
	 	  WHEN PatientRaceDSC IN ('Declined', 'Unavailable') THEN 'Unknown'
		  WHEN PatientRaceDSC IS NULL THEN 'Unknown'
		  ELSE 'Other' END AS 'RACE'
FROM
	(SELECT DISTINCT
		CASE WHEN Month(PAT.DeathDTS) > 9 THEN YEAR(PAT.DeathDTS) + 1 ELSE YEAR(PAT.DeathDTS) END as FY
		,PatientMRN
		,PatientRaceDSC
		,CASE WHEN PatientRaceDSC IN ('White', 'White or Caucasian', 'BLACK OR AFRICAN AMERICAN','ASIAN', 'Hispanic or Latino') THEN 1
			  WHEN PatientRaceDSC IN ('Declined', 'Unavailable') THEN 3
			  WHEN PatientRaceDSC IS NULL THEN 3
			  ELSE 2 END AS 'RACE_num_flg' /** RACE_num_flg = 2 captures "Other" **/

	FROM Epic.Finance.HospitalTransaction HT 		
	LEFT JOIN Epic.Finance.HospitalAccount HA on HT.HospitalAccountID = HA.HospitalAccountID		
	LEFT JOIN Epic.Patient.race R on HA.PatientID = R.patientid		
	LEFT JOIN Epic.Patient.[Identity] I on HA.PatientID = I.PatientID
	LEFT JOIN [Epic].[Patient].[Patient] PAT ON PAT.PatientID=HA.PatientID
		
	WHERE 1=1		
		  /* DFCI patients only */
		  AND I.IdentityTypeID = '109'
		  /* EPIC FY16 and later */
		  AND CASE WHEN Month(PAT.DeathDTS) > 9 THEN YEAR(PAT.DeathDTS) + 1 ELSE YEAR(PAT.DeathDTS) END >= @FY
		  /* Other condistions */
		  AND HT.DepartmentDSC NOT IN ('BMV','DF MAMMOGRAPHY VAN')
		  
	) Race_num_flg
) Race_row	
WHERE Race_row = 1;

This is the new code:

     , I.PatientIdentityID as MRN
	 , Race
INTO #Race
FROM
(SELECT DISTINCT
	Race_Num_flg.*
	,ROW_NUMBER() OVER (PARTITION BY FY, I.PatientIdentityID ORDER BY RACE_num_flg ASC) AS Race_row
	,CASE WHEN PatientRaceDSC = 'ASIAN' THEN 'Asian'
		  WHEN PatientRaceDSC = 'BLACK OR AFRICAN AMERICAN' THEN 'Black or African American'
		  WHEN PatientRaceDSC = 'Hispanic or Latino' THEN 'Hispanic or Latino'
		  WHEN PatientRaceDSC LIKE ('WHITE%') THEN 'White or Caucasian'
		  --WHEN PatientRaceDSC = 'American Indian or Alaska Native' THEN 'American Indian or Alaska Native'
		  --WHEN PatientRaceDSC = 'Native Hawaiian or Other Pacific Islander' THEN 'Native Hawaiian or Other Pacific Islander'
	 	  WHEN PatientRaceDSC IN ('Declined', 'Unavailable') THEN 'Unknown'
		  WHEN PatientRaceDSC IS NULL THEN 'Unknown'
		  ELSE 'Other' END AS 'RACE'
FROM
	(SELECT DISTINCT
		CASE WHEN Month(PAT.DeathDTS) > 9 THEN YEAR(PAT.DeathDTS) + 1 ELSE YEAR(PAT.DeathDTS) END as FY
		,I.PatientIdentityID as MRN
		,PatientRaceDSC
		,CASE WHEN PatientRaceDSC IN ('White', 'White or Caucasian', 'BLACK OR AFRICAN AMERICAN','ASIAN', 'Hispanic or Latino') THEN 1
			  WHEN PatientRaceDSC IN ('Declined', 'Unavailable') THEN 3
			  WHEN PatientRaceDSC IS NULL THEN 3
			  ELSE 2 END AS 'RACE_num_flg' /** RACE_num_flg = 2 captures "Other" **/

	FROM Epic.Finance.HospitalTransaction HT 		
	LEFT JOIN Epic.Finance.HospitalAccount HA on HT.HospitalAccountID = HA.HospitalAccountID		
	LEFT JOIN Epic.Patient.race R on HA.PatientID = R.patientid		
	LEFT JOIN Epic.Patient.[Identity] I on HA.PatientID = I.PatientID
	LEFT JOIN [Epic].[Patient].[Patient] PAT ON PAT.PatientID=HA.PatientID
		
	WHERE 1=1		
		  /* DFCI patients only */
		  AND I.IdentityTypeID = '109'
		  /* EPIC FY16 and later */
		  AND CASE WHEN Month(PAT.DeathDTS) > 9 THEN YEAR(PAT.DeathDTS) + 1 ELSE YEAR(PAT.DeathDTS) END >= @FY
		  /* Other condistions */
		  AND HT.DepartmentDSC NOT IN ('BMV','DF MAMMOGRAPHY VAN')


	) Race_num_flg
) Race_row	
WHERE Race_row = 1
;

The only thing changed is the "PatientMRN as MRN" to "I.PatientIdentityID as MRN" and I get this error:

Msg 4104, Level 16, State 1, Line 23
The multi-part identifier "I.PatientIdentityID" could not be bound.
Msg 4104, Level 16, State 1, Line 17
The multi-part identifier "I.PatientIdentityID" could not be bound.

Any assistance would be greatly appreciated.

Thanks

...
, PatientIdentityID as MRN --<<-- remove "I."
	 , Race
INTO #Race
FROM
(SELECT DISTINCT
	Race_Num_flg.*
	,ROW_NUMBER() OVER (PARTITION BY FY, PatientIdentityID ORDER BY RACE_num_flg ASC) AS Race_row  --<<-- remove "I."
...

Thanks Scott. I tried that and still get the same errors

In fact, I thought I would need to do that because of the join

What "errors" do you get?

You need to do that because the "I" alias doesn't exist any more. When you use a subquery and give it an alias, the aliases inside the subquery no longer exist.

That is, when you write:
SELECT col1, x.col2, col3 --<<--NOT a.col1 or b.col2
FROM ( SELECT a.col1, b.col2, c.col3 FROM tablea a INNER JOIN tableb b ON ... INNER JOIN tablec c ON ... ) AS x

The alias for those columns is now "x", SQL can "no longer see" the a, b, c aliases.

When I take the I alias off I get this error:

Msg 207, Level 16, State 1, Line 23
Invalid column name 'PatientIdentityID'.
Msg 207, Level 16, State 1, Line 17
Invalid column name 'PatientIdentityID'.

Oh yeah, that column's not in the inner-most SELECT.

Again, the subquery is "out of scope" once you've assigned a new alias, so only the columns SELECTed in the subquery are available to the outer query.

For example. suppose tablea has col1, col2 and col3, then:
SELECT x.col1, x.col2 FROM ( SELECT col1 FROM table a WHERE col2 LIKE 'a%') AS x
col2 is not valid since is not available (was not SELECTed) as part of x.

I still don't understand and i truly appreciate you trying to assist me, but based on your examples, I have become even more confused. I inherited this code and unfortunately I am not well versed in SQL.Is there another way to write this so that I don't have this problem?

You're summing up records by race, etc., so individual IDs are not really valid any longer with that output.

For example, consider, say, 500 students at a university, each with their own unique student id. If you summed up all by class, you might get freshman 240, sophomore 120, junior 80, senior 60. But, for those totals, notice that a student id doesn't make sense, because it's the total for all student ids.

I don't see any summarization in the query - not sure where you see that. The OP's problem is that the query is built using multiple derived tables. The query only needs a single derived table - and doesn't need DISTINCT either.

The query I working on has 3 sections. The first section, which is the one I submitted here, is the Race/Ethnicity by FY and MRN. The second part is Location by FY and MRN. I am able to run that by using the I.PatientIdentityID. The third section joins the first 2 sections. Here are sections 1 & 2. Again, when i changed the code using an alias in section 2 and ran it on its own it works.

DECLARE @FY AS INT;
SET @FY = 2016; /** Output shows data from the set @FY to the current date **/

DROP TABLE IF EXISTS
	#Race , #Location
;

/*-------------------------------------*/
/* 1. Get Race/Ethnicity by FY and MRN */
/*-------------------------------------*/

SELECT FY 
     , I.PatientIdentityID as MRN
	 , Race
INTO #Race
FROM
(SELECT DISTINCT
	Race_Num_flg.*
	,ROW_NUMBER() OVER (PARTITION BY FY, I.PatientIdentityID ORDER BY RACE_num_flg ASC) AS Race_row
	,CASE WHEN PatientRaceDSC = 'ASIAN' THEN 'Asian'
		  WHEN PatientRaceDSC = 'BLACK OR AFRICAN AMERICAN' THEN 'Black or African American'
		  WHEN PatientRaceDSC = 'Hispanic or Latino' THEN 'Hispanic or Latino'
		  WHEN PatientRaceDSC LIKE ('WHITE%') THEN 'White or Caucasian'
		  --WHEN PatientRaceDSC = 'American Indian or Alaska Native' THEN 'American Indian or Alaska Native'
		  --WHEN PatientRaceDSC = 'Native Hawaiian or Other Pacific Islander' THEN 'Native Hawaiian or Other Pacific Islander'
	 	  WHEN PatientRaceDSC IN ('Declined', 'Unavailable') THEN 'Unknown'
		  WHEN PatientRaceDSC IS NULL THEN 'Unknown'
		  ELSE 'Other' END AS 'RACE'
FROM
	(SELECT DISTINCT
		CASE WHEN Month(PAT.DeathDTS) > 9 THEN YEAR(PAT.DeathDTS) + 1 ELSE YEAR(PAT.DeathDTS) END as FY
		,I.PatientIdentityID as MRN
		,PatientRaceDSC
		,CASE WHEN PatientRaceDSC IN ('White', 'White or Caucasian', 'BLACK OR AFRICAN AMERICAN','ASIAN', 'Hispanic or Latino') THEN 1
			  WHEN PatientRaceDSC IN ('Declined', 'Unavailable') THEN 3
			  WHEN PatientRaceDSC IS NULL THEN 3
			  ELSE 2 END AS 'RACE_num_flg' /** RACE_num_flg = 2 captures "Other" **/

	FROM Epic.Finance.HospitalTransaction HT 		
	LEFT JOIN Epic.Finance.HospitalAccount HA on HT.HospitalAccountID = HA.HospitalAccountID		
	LEFT JOIN Epic.Patient.race R on HA.PatientID = R.patientid		
	LEFT JOIN Epic.Patient.[Identity] I on HA.PatientID = I.PatientID
	LEFT JOIN [Epic].[Patient].[Patient] PAT ON PAT.PatientID=HA.PatientID
		
	WHERE 1=1		
		  /* DFCI patients only */
		  AND I.IdentityTypeID = '109'
		  /* EPIC FY16 and later */
		  AND CASE WHEN Month(PAT.DeathDTS) > 9 THEN YEAR(PAT.DeathDTS) + 1 ELSE YEAR(PAT.DeathDTS) END >= @FY
		  /* Other condistions */
		  AND HT.DepartmentDSC NOT IN ('BMV','DF MAMMOGRAPHY VAN')
		  
		  /* Only Outpatient */ /* use as needed */
		  --AND HT.HospitalAccountClassDSC = 'Outpatient'
		  /* Charge Data */ /* use as needed */
		  --AND HT.TransactionTypeDSC = 'Charge'

	) Race_num_flg
) Race_row	
WHERE Race_row = 1
;

/*-------------------------------*/
/* 2. Get Location by FY and MRN */
/*-------------------------------*/

SELECT DISTINCT
	HA.PatientID
  , I.PatientIdentityID as MRN
  , DischargeEpicLocationID
  , Loc.RevenueLocationNM
  , Pay.PayorID
  , Pay.PayorNM
  , Pat.DeathDTS
,CASE WHEN Month(PAT.DeathDTS) > 9 THEN YEAR(PAT.DeathDTS) + 1 ELSE YEAR(PAT.DeathDTS) END AS FY
INTO #Location
FROM Epic.Finance.HospitalAccount HA
LEFT JOIN epic.Finance.HospitalTransaction HT ON HT.HospitalAccountID=HA.HospitalAccountID
LEFT JOIN Epic.Reference.[Location] LOC ON HA.[DischargeEpicLocationID] = LOC.LocationID
LEFT JOIN [Epic].[Reference].[Payor] PAY ON HT.PayorID = PAY.PayorID
LEFT JOIN [Epic].[Patient].[Patient] PAT ON PAT.PatientID=HA.PatientID
LEFT JOIN Epic.Patient.[Identity] I on HA.PatientID = I.PatientID
WHERE 1=1
	  --AND LEFT(Loc.RevenueLocationNM,4) = 'Dana'
	  AND CASE WHEN Month(PAT.DeathDTS) > 9 THEN YEAR(PAT.DeathDTS) + 1 ELSE YEAR(PAT.DeathDTS) END in (2016,2017,2018,2019,2020)
	  AND Pay.PayorID = '110001'	
;

The query could be rewritten as:

SELECT FY 
        , PatientMRN AS MRN
	, Race
INTO #Race
FROM
(SELECT CASE WHEN Month(PAT.DeathDTS) > 9 THEN YEAR(PAT.DeathDTS) + 1 ELSE YEAR(PAT.DeathDTS) END as FY
	,PatientMRN
	,CASE WHEN PatientRaceDSC = 'ASIAN' THEN 'Asian'
		  WHEN PatientRaceDSC = 'BLACK OR AFRICAN AMERICAN' THEN 'Black or African American'
		  WHEN PatientRaceDSC = 'Hispanic or Latino' THEN 'Hispanic or Latino'
		  WHEN PatientRaceDSC LIKE ('WHITE%') THEN 'White or Caucasian'
		  --WHEN PatientRaceDSC = 'American Indian or Alaska Native' THEN 'American Indian or Alaska Native'
		  --WHEN PatientRaceDSC = 'Native Hawaiian or Other Pacific Islander' THEN 'Native Hawaiian or Other Pacific Islander'
	 	  WHEN PatientRaceDSC IN ('Declined', 'Unavailable') THEN 'Unknown'
		  WHEN PatientRaceDSC IS NULL THEN 'Unknown'
		  ELSE 'Other' END AS 'RACE'
        ,ROW_NUMBER() OVER (PARTITION BY FY, PatientMRN ORDER BY CASE WHEN PatientRaceDSC IN ('White', 'White or Caucasian', 'BLACK OR AFRICAN AMERICAN','ASIAN', 'Hispanic or Latino') THEN 1
	         	                                                      WHEN PatientRaceDSC IN ('Declined', 'Unavailable') THEN 3          
		                                                              WHEN PatientRaceDSC IS NULL THEN 3    
		                                                              ELSE 2 
                                                                       END ASC) AS Race_row

FROM Epic.Finance.HospitalTransaction HT 		
LEFT JOIN Epic.Finance.HospitalAccount HA on HT.HospitalAccountID = HA.HospitalAccountID		
LEFT JOIN Epic.Patient.race R on HA.PatientID = R.patientid		
LEFT JOIN Epic.Patient.[Identity] I on HA.PatientID = I.PatientID
LEFT JOIN [Epic].[Patient].[Patient] PAT ON PAT.PatientID=HA.PatientID
		
WHERE 1=1		
	  /* DFCI patients only */
	  AND I.IdentityTypeID = '109'
	  /* EPIC FY16 and later */
	  AND CASE WHEN Month(PAT.DeathDTS) > 9 THEN YEAR(PAT.DeathDTS) + 1 ELSE YEAR(PAT.DeathDTS) END >= @FY
	  /* Other condistions */
	  AND HT.DepartmentDSC NOT IN ('BMV','DF MAMMOGRAPHY VAN')
) Race_row	
WHERE Race_row = 1;

With this - we see that the derived table 'race_row' is not returning a column named PatientIdentityID - so the change to this would be:

SELECT FY 
        , PatientIdentityID AS MRN
	, Race
INTO #Race
FROM
(SELECT CASE WHEN Month(PAT.DeathDTS) > 9 THEN YEAR(PAT.DeathDTS) + 1 ELSE YEAR(PAT.DeathDTS) END as FY
	,PatientIdentityID
	,CASE WHEN PatientRaceDSC = 'ASIAN' THEN 'Asian'
		  WHEN PatientRaceDSC = 'BLACK OR AFRICAN AMERICAN' THEN 'Black or African American'
		  WHEN PatientRaceDSC = 'Hispanic or Latino' THEN 'Hispanic or Latino'
		  WHEN PatientRaceDSC LIKE ('WHITE%') THEN 'White or Caucasian'
		  --WHEN PatientRaceDSC = 'American Indian or Alaska Native' THEN 'American Indian or Alaska Native'
		  --WHEN PatientRaceDSC = 'Native Hawaiian or Other Pacific Islander' THEN 'Native Hawaiian or Other Pacific Islander'
	 	  WHEN PatientRaceDSC IN ('Declined', 'Unavailable') THEN 'Unknown'
		  WHEN PatientRaceDSC IS NULL THEN 'Unknown'
		  ELSE 'Other' END AS 'RACE'
        ,ROW_NUMBER() OVER (PARTITION BY FY, PatientIdentityID ORDER BY CASE WHEN PatientRaceDSC IN ('White', 'White or Caucasian', 'BLACK OR AFRICAN AMERICAN','ASIAN', 'Hispanic or Latino') THEN 1
		                                                                     WHEN PatientRaceDSC IN ('Declined', 'Unavailable') THEN 3
		                                                                     WHEN PatientRaceDSC IS NULL THEN 3    
		                                                                     ELSE 2 
                                                                         END ASC) AS Race_row

FROM Epic.Finance.HospitalTransaction HT 		
LEFT JOIN Epic.Finance.HospitalAccount HA on HT.HospitalAccountID = HA.HospitalAccountID		
LEFT JOIN Epic.Patient.race R on HA.PatientID = R.patientid		
LEFT JOIN Epic.Patient.[Identity] I on HA.PatientID = I.PatientID
LEFT JOIN [Epic].[Patient].[Patient] PAT ON PAT.PatientID=HA.PatientID
		
WHERE 1=1		
	  /* DFCI patients only */
	  AND I.IdentityTypeID = '109'
	  /* EPIC FY16 and later */
	  AND CASE WHEN Month(PAT.DeathDTS) > 9 THEN YEAR(PAT.DeathDTS) + 1 ELSE YEAR(PAT.DeathDTS) END >= @FY
	  /* Other condistions */
	  AND HT.DepartmentDSC NOT IN ('BMV','DF MAMMOGRAPHY VAN')
) Race_row	
WHERE Race_row = 1;

Jeff

Unfortunately the column "PatientMRN" no longer exist in our database, that's why I had to use PatientIdentityID from the joined table Epic.Patient.[Identity]. For some reason IT and/or the dba's thought it was a good idea to eliminate that column. Not sure why.

Okay - so where are you still having a problem?

when I use I.PatientIdentityID as MRN I get two bounding errors

Msg 4104, Level 16, State 1, Line 23
The multi-part identifier "I.PatientIdentityID" could not be bound.
Msg 4104, Level 16, State 1, Line 17
The multi-part identifier "I.PatientIdentityID" could not be bound.

But only on setrion 1. Section 2 runs perfectly when I use "I.PatientIdentityID"

BTW - I would recommend creating 2 new variables:

DECLARE @fyStart date = datefromparts(@fy - 1, 10, 1)
      , @fyEnd date = datefromparts(@fy, 9, 30);

Then - use those dates to filter the rows:

AND PAT.DeathDTS >= @fyStart
AND PAT.DeathDTS <  dateadd(day, 1, @fsEnd)

This will allow for indexes to be used - if they exist - on the DeathDTS column. And since this is a FY report limited to only a single FY - then no need to 'calculate' the FY based on that column. You can just return the define @FY since you already know that all patients to be returned will have a date of death in the defined FY.

Actually its for FY's 16 thru current date

I would change the join order - and change the joins to INNER JOINS since you are filtering on PAT and I tables.

It makes more sense going from the Hospital Account - to the Patient - then to the Identity. And tie Race to patient:

FROM Epic.Finance.HospitalTransaction    HT 		
INNER JOIN Epic.Finance.HospitalAccount  HA on HA.HospitalAccountID = HT.HospitalAccountID		
INNER JOIN [Epic].[Patient].[Patient]   PAT ON PAT.PatientID=HA.PatientID
INNER JOIN Epic.Patient.[Identity]        I on I.PatientID = PAT.PatientID
INNER JOIN Epic.Patient.race              R on R.PatientID = PAT.patientid		

Not the code you provided - the code you provided is specific to a single FY based on the variable you defined as @FY and the code you use to filter. It cannot be used for multiple fiscal years...which makes me think this is probably a part of a larger set of code that uses a CURSOR to loop over the 'list' of fiscal years.

Most likely - that isn't needed and everything could be done for all years at once. But that isn't what you have asked here.