Need parameter value from outer query? 1

[rob1070]

I have the following Query in which there is a Subquery However I don't know how to supply the values to my parameter values from my outer Query nor am I sure I can do this The subquery pulls in 5 values A1-A5 to the dataset but I am not sure how to feed the values for @Dictid and @Pid to the subquery I can set the values manually and it runs fine but the results for A1-A5 need to be based on the Current Paientid and dictatorid of the current row. Thanks for any help.


SELECT RTRIM(u.Firstname) + ' ' + RTRIM(u.LastName) AS Clinician,x.PQRI_Dictatorid,
p.PatientFirst + ' ' + p.PatientLast + '-' + x.PQRI_PatientID AS Patient, COUNT(x.PQRI_CollectionID) AS PQRI_COUNT,x.PQRI_PatientID ,x.PQRI_Dictatorid,(SELECT PQRI_Questions.PQRI_Question as A1
FROM PQRI_Data X INNER JOIN
PQRI_Questions ON x.PQRI_Answer = PQRI_Questions.PQRI_QuestionID
WHERE (x.PQRI_Dictatorid = @Dictid ) AND (x.PQRI_PatientID = @PID ) AND (x.PQRI_QuestionID = 1)) A1,(SELECT PQRI_Questions.PQRI_Question as A1
FROM PQRI_Data x INNER JOIN
PQRI_Questions ON x.PQRI_Answer = PQRI_Questions.PQRI_QuestionID
WHERE (x.PQRI_Dictatorid = @Dictid ) AND (x.PQRI_PatientID =@PID) AND (x.PQRI_QuestionID = 2))A2,(SELECT PQRI_Questions.PQRI_Question as A1
FROM PQRI_Data x INNER JOIN
PQRI_Questions ON x.PQRI_Answer = PQRI_Questions.PQRI_QuestionID
WHERE (x.PQRI_Dictatorid = @Dictid ) AND (x.PQRI_PatientID = @PID) AND (x.PQRI_QuestionID = 3))A3,(SELECT PQRI_Questions.PQRI_Question as A1
FROM PQRI_Data x INNER JOIN
PQRI_Questions ON x.PQRI_Answer = PQRI_Questions.PQRI_QuestionID
WHERE (x.PQRI_Dictatorid = @Dictid ) AND (x.PQRI_PatientID = @PID) AND (x.PQRI_QuestionID = 4))A4,(SELECT PQRI_Questions.PQRI_Question as A1
FROM PQRI_Data x INNER JOIN
PQRI_Questions ON x.PQRI_Answer = PQRI_Questions.PQRI_QuestionID
WHERE (x.PQRI_Dictatorid = @Dictid ) AND (x.PQRI_PatientID = @PID) AND (x.PQRI_QuestionID = 5))A5

FROM aspnet_Users u INNER JOIN
PQRI_Data x ON u.userid1 = x.PQRI_Dictatorid INNER JOIN
Patientinfo p ON x.PQRI_PatientID = p.PatientID
GROUP BY u.LastName, u.Firstname,x.PQRI_Dictatorid, x.PQRI_PatientID, p.PatientLast, p.PatientFirst,p.patientid
having COUNT(x.PQRI_Collectionid)=5

 

[gmmastros]

Would you prefer the answer to your direct question, or would you prefer to be shown a better way to write the query?

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

 

[gmmastros]

Subqueries do have access to the outer query. Unfortunately, you have a table in the outer query aliased as x, and a table in the inner query also aliased as x. In order to get this to work the way you want it to, you'll need to change one of them. I suspect it's easier to change the outer queries alias, but the choice is yours.

Like this:

SELECT        RTRIM(u.Firstname) + ' ' + RTRIM(u.LastName) AS Clinician,x.PQRI_Dictatorid,
                         p.PatientFirst + ' ' + p.PatientLast + '-' + [!]xOuter[/!].PQRI_PatientID AS Patient, COUNT([!]xOuter[/!].PQRI_CollectionID) AS PQRI_COUNT,[!]xOuter[/!].PQRI_PatientID ,[!]xOuter[/!].PQRI_Dictatorid,(SELECT     PQRI_Questions.PQRI_Question as A1
FROM         PQRI_Data X INNER JOIN
                      PQRI_Questions ON x.PQRI_Answer = PQRI_Questions.PQRI_QuestionID
WHERE     [!]x.PQRI_DictatorID = xOuter.PQRI_DictatorId
          And x.PQRI_PatientID = xOuter.PQRI_PatientId
          And [/!](x.PQRI_Dictatorid = @Dictid  ) AND (x.PQRI_PatientID = @PID ) AND (x.PQRI_QuestionID = 1)) A1,(SELECT     PQRI_Questions.PQRI_Question as A1
FROM         PQRI_Data x INNER JOIN
                      PQRI_Questions ON x.PQRI_Answer = PQRI_Questions.PQRI_QuestionID
WHERE     [!]x.PQRI_DictatorID = xOuter.PQRI_DictatorId
          And x.PQRI_PatientID = xOuter.PQRI_PatientId
          And [/!](x.PQRI_Dictatorid = @Dictid ) AND (x.PQRI_PatientID =@PID) AND (x.PQRI_QuestionID = 2))A2,(SELECT     PQRI_Questions.PQRI_Question as A1
FROM         PQRI_Data x INNER JOIN
                      PQRI_Questions ON x.PQRI_Answer = PQRI_Questions.PQRI_QuestionID
WHERE     [!]x.PQRI_DictatorID = xOuter.PQRI_DictatorId
          And x.PQRI_PatientID = xOuter.PQRI_PatientId
          And [/!](x.PQRI_Dictatorid = @Dictid ) AND (x.PQRI_PatientID = @PID) AND (x.PQRI_QuestionID = 3))A3,(SELECT     PQRI_Questions.PQRI_Question as A1
FROM         PQRI_Data  x INNER JOIN
                      PQRI_Questions ON x.PQRI_Answer = PQRI_Questions.PQRI_QuestionID
WHERE     [!]x.PQRI_DictatorID = xOuter.PQRI_DictatorId
          And x.PQRI_PatientID = xOuter.PQRI_PatientId
          And [/!](x.PQRI_Dictatorid = @Dictid ) AND (x.PQRI_PatientID = @PID) AND (x.PQRI_QuestionID = 4))A4,(SELECT     PQRI_Questions.PQRI_Question as A1
FROM         PQRI_Data x INNER JOIN
                      PQRI_Questions ON x.PQRI_Answer = PQRI_Questions.PQRI_QuestionID
WHERE     [!]x.PQRI_DictatorID = xOuter.PQRI_DictatorId
          And x.PQRI_PatientID = xOuter.PQRI_PatientId
          And [/!](x.PQRI_Dictatorid = @Dictid ) AND (x.PQRI_PatientID = @PID) AND (x.PQRI_QuestionID = 5))A5 
                         
FROM            aspnet_Users u INNER JOIN
                         PQRI_Data [!]xOuter[/!] ON u.userid1 = [!]xOuter[/!].PQRI_Dictatorid INNER JOIN
                         Patientinfo p ON [!]xOuter[/!].PQRI_PatientID = p.PatientID 
GROUP BY u.LastName, u.Firstname,[!]xOuter[/!].PQRI_Dictatorid, [!]xOuter[/!].PQRI_PatientID, p.PatientLast, p.PatientFirst,p.patientid
having COUNT(x.PQRI_Collectionid)=5

Now... looking at your code, there seems to be a lot going on, but it can certainly be optimized. I say this because each of the sub-queries are exactly the same with the exception of a single where clause criteria.

If you are interested, I can show you how to re-write this query so that it is (probably) easier to understand while performing faster, and also returning the same information. If you're interested, let me know.

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

 

[markros]

If you're using SQL Server 2005 or up, just use PIVOT command.

You don't need to use subqueries here.

PluralSight Learning Library

 

[rob1070]

Thanks to everyone, as you can tell I am pretty novice in the advanced query area. I would indeed be interested in a better way to construct this query. I think it would help me better understand the best way to handle this type of situation in the future.

Thanks
Rob

 

[gmmastros]

Looking at the subqueries, they are all similar to this:

[tt][blue]
SELECT PQRI_Questions.PQRI_Question as A1
FROM PQRI_Data X INNER JOIN
PQRI_Questions ON x.PQRI_Answer = PQRI_Questions.PQRI_QuestionID
WHERE x.PQRI_Dictatorid = @Dictid
AND x.PQRI_PatientID = @PID
AND [!]x.PQRI_QuestionID = 1[/!]
[/blue][/tt]

The only difference is in the QuestionID part. Like Markros said, you can use a pivot query here. This is just one method.

One way to pivot the data is like this:

SELECT PQRI_Data.PQRI_Dictatorid,
       PQRI_Data.PQRI_PatientID,
       Max(Case When PQRI_QuestionID = 1 Then PQRI_Questions.PQRI_Question End) as A1,
       Max(Case When PQRI_QuestionID = 2 Then PQRI_Questions.PQRI_Question End) as A2,
       Max(Case When PQRI_QuestionID = 3 Then PQRI_Questions.PQRI_Question End) as A3,
       Max(Case When PQRI_QuestionID = 4 Then PQRI_Questions.PQRI_Question End) as A4,
       Max(Case When PQRI_QuestionID = 5 Then PQRI_Questions.PQRI_Question End) as A5
FROM   PQRI_Data 
       INNER JOIN PQRI_Questions 
          ON PQRI_Data.PQRI_Answer = PQRI_Questions.PQRI_QuestionID
GROUP BY PQRI_Data.PQRI_Dictatorid,
       PQRI_Data.PQRI_PatientID

This should return the DictatorId, PatientID, and the 5 questions.

Next, you could take that query and make it a derived table (in the from clause), like this:

SELECT  RTRIM(u.Firstname) + ' ' + RTRIM(u.LastName) AS Clinician,
		x.PQRI_Dictatorid,
        p.PatientFirst + ' ' + p.PatientLast + '-' + x.PQRI_PatientID AS Patient, 
        COUNT(x.PQRI_CollectionID) AS PQRI_COUNT,
        x.PQRI_PatientID ,
        x.PQRI_Dictatorid,
        PivotData.A1,
        PivotData.A2,
        PivotData.A3,
        PivotData.A4,
        PivotData.A5
FROM    aspnet_Users u INNER JOIN
        PQRI_Data x ON u.userid1 = x.PQRI_Dictatorid INNER JOIN
        Patientinfo p ON x.PQRI_PatientID = p.PatientID INNER JOIN 
        (
          SELECT PQRI_Data.PQRI_Dictatorid,
                 PQRI_Data.PQRI_PatientID,
                 Max(Case When PQRI_QuestionID = 1 Then PQRI_Questions.PQRI_Question End) as A1,
                 Max(Case When PQRI_QuestionID = 2 Then PQRI_Questions.PQRI_Question End) as A2,
                 Max(Case When PQRI_QuestionID = 3 Then PQRI_Questions.PQRI_Question End) as A3,
                 Max(Case When PQRI_QuestionID = 4 Then PQRI_Questions.PQRI_Question End) as A4,
                 Max(Case When PQRI_QuestionID = 5 Then PQRI_Questions.PQRI_Question End) as A5
          FROM   PQRI_Data 
                 INNER JOIN PQRI_Questions 
                    ON PQRI_Data.PQRI_Answer = PQRI_Questions.PQRI_QuestionID
          GROUP BY PQRI_Data.PQRI_Dictatorid,
                 PQRI_Data.PQRI_PatientID
        ) As PivotData On x.PQRI_DictatorId = PivotData.PQRI_DictatorID
            And x.PQRI_PatientId = PivotData.PQRI_PatientId

Note that I did not test the query. How could I? However, the idea, the concept, is sound. As you can see, the 5 subqueries are replaced with a single (slightly more complex) query. This should cause the query to run faster, and is, in my opinion, easier to understand.

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

 

[markros]

Or, using new SQL Server 2005+ PIVOT syntax, the first query can be written as

SELECT *
from (select 
PQRI_Data.PQRI_Dictatorid,
       PQRI_Data.PQRI_PatientID,
       PQRI_QuestionID, PQRI_Question
FROM   PQRI_Data 
       INNER JOIN PQRI_Questions 
          ON PQRI_Data.PQRI_Answer = PQRI_Questions.PQRI_QuestionID) src
PIVOT (max(PQRI_Question) for PQRI_QuestionID IN ([1],[2],[3],[4],[5])) pvt

and then you can add extra info as shown in the previous reply.

Although for this particular problem CASE statements may be better as we do GROUP BY only once (as we need to have an extra count)

PluralSight Learning Library

 

[rob1070]

Thanks to EVERYONE you guys are great. I have a working query now and It runs pretty Quickly, However I had to add another field from another table "Dictations" after adding this inner join so I can get "DOS" the query runs VERY slowly. Can you tell me if there is a better way to do this

SELECT PQRI_Data.PQRI_Dictatorid, PQRI_Data.PQRI_PatientID, Patientinfo.PatientLast + ', ' + Patientinfo.PatientFirst + ' ' + Patientinfo.PatientMiddle + '- ' + Patientinfo.TigerID AS Patient, RTRIM(aspnet_Users.Firstname)
+ ' ' + RTRIM(aspnet_Users.LastName) AS Clinician, MAX(CASE WHEN PQRI_data.PQRI_QuestionID = 1 THEN PQRI_Questions.PQRI_Question END) AS A1,
MAX(CASE WHEN PQRI_data.PQRI_QuestionID = 2 THEN PQRI_Questions.PQRI_Question END) AS A2,
MAX(CASE WHEN PQRI_data.PQRI_QuestionID = 3 THEN PQRI_Questions.PQRI_Question END) AS A3,
MAX(CASE WHEN PQRI_data.PQRI_QuestionID = 4 THEN PQRI_Questions.PQRI_Question END) AS A4,
MAX(CASE WHEN PQRI_data.PQRI_QuestionID = 5 THEN PQRI_Questions.PQRI_Question END) AS A5,
MAX(Case WHEN PQRI_DATA.isexternal = 'True' then PQRI_DATA.ExternalDOS Else Dictations.DOS END) AS DOS

FROM PQRI_Data INNER JOIN
PQRI_Questions ON PQRI_Data.PQRI_Answer = PQRI_Questions.PQRI_QuestionID INNER JOIN
Patientinfo ON PQRI_Data.PQRI_PatientID = Patientinfo.PatientID INNER JOIN
aspnet_Users ON PQRI_Data.PQRI_Dictatorid = aspnet_Users.userid1 Inner Join Dictations on PQRI_Data.PQRI_DictationID=Dictations.DictationID
GROUP BY PQRI_Data.PQRI_Dictatorid, PQRI_Data.PQRI_PatientID, Patientinfo.PatientFirst, Patientinfo.PatientMiddle, Patientinfo.PatientLast, aspnet_Users.Firstname,
aspnet_Users.LastName, Patientinfo.TigerID
HAVING (COUNT(PQRI_Data.PQRI_CollectionID) = 5)

 

[markros]

Since you only want to use Dictations table when IsExternal = 'False', try adding this condition into JOIN condition (but change it to LEFT JOIN). Alternatively, you can change this query to two queries (almost the same) with UNION ALL. The first will be for IsExternal = 'True' and no Join to Dictations and the second will be where IsExternal = 'False' and a join to Dictations.

Tell if there will be any improvement after these changes.



PluralSight Learning Library

 

[rob1070]

Thanks for the Suggestion Markros I tried The Union ALL and it did not make any difference in speed. How would I implement the join Condition?

 

[markros]

LEFT JOIN Dictations on PRQRI_Data.IsExternal='False' and
PRQRI_Data.PRQRI_DictationID = Dictations.DictationID

Do you have an index on this field in both tables?

PluralSight Learning Library

 

[rob1070]

Markros Thank you very much the index was the issue the Query went from over 2 minutes to 1 second !