Description

Discuss and demonstrate good techniques for writing Stored Procedures, Functions, and ensuring queries are SARGable whenever possible. Show how Dynamic SQL and Query Hints can help with performance. 

 

 

Expected Outcomes

Upon completing this Aeries conference session, attendees should be familiar with:

 

  • Synonyms
  • How to craft Dynamic SQL statements
  • Cursors vs Set-Based methods
  • Pivot without using PIVOT
  • Derived Tables
  • CTE - Common Table Expressions  - CTE example
  • How to analyze a query plan
  •  SARGable/non-SARGable queries
  •  How SQL indexes work 


Content:


Synonyms:

These can be used within a [AeriesCurrent] database as an example to point to all tables in the current year DST18000YourDistrict for instance. Scripts made against that database need never be changed to point to the current year if you simply drop the synonyms and recreate them. The following SQL can help with this task in a simplistic way...


 

use master;
create database AeriesCurrent;
go
use AeriesCurrent;
go

SELECT 'CREATE SYNONYM ['+TABLE_NAME+'] FOR DST20000AeriesDemo.dbo.['+TABLE_NAME+']' FROM DST20000AeriesDemo.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

SELECT 'DROP SYNONYM IF EXISTS ['+TABLE_NAME+']' FROM DST20000AeriesDemo.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'


Dynamic SQL:

We can also enhance this using dynamic SQL so we do not have to copy and paste the output and execute it. This can be suitable to drop in to a SQL job so the synonyms always include all tables. I also went a little further and tied to more than the current year. This can aid you in doing longitudinal queries. 


USE AeriesCurrent

IF EXISTS (SELECT 1 WHERE DB_NAME() = 'AERIESCURRENT') -- I wrapped this to protect against dropping synonyms on anything but a DB called AeriesCurrent
BEGIN
DECLARE @S NVARCHAR(MAX) = N''

SELECT @S += N'
DROP SYNONYM IF EXISTS ['+[NAME]+N']' 
FROM SYS.SYNONYMS ORDER BY [NAME]

SELECT @S += N'
CREATE SYNONYM ['+TABLE_NAME+N'] FOR DST20000AeriesDemo.dbo.['+TABLE_NAME+N']' 
FROM DST20000AeriesDemo.INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE'

SELECT @S += N'
CREATE SYNONYM ['+TABLE_NAME+N'_LastYear1] FOR DST19000AeriesDemo.dbo.['+TABLE_NAME+N']' 
FROM DST20000AeriesDemo.INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE'
AND LEN(TABLE_NAME) < 118 --Beacause adding the ten characters '_LastYear1' cannot exceed max object name length of 128 characters. 

--print @S
--select @S
exec (@S)
end


Here's a simple example using the synonyms above now connecting to the current and prior year...


--What was their Total Weighted GPA in this year's DB compared to last year's DB
--This will only become interesting after they accomplish something this current year and their GPA may actually be different.
SELECT STU.SC, STU.ID, STU.FN, STU.LN, STU.TP, S1.SC, S1.TP
FROM STU LEFT JOIN STU_LastYear1 S1 ON STU.ID = S1.ID


 



Another Dynamic SQL Example...

--Select Table Counts even if there are other schemas owning them
create table dbo.#TableCounts (
SchemaName varchar(255) null, 
TableName varchar(255) null, 
Counts bigint null)

declare @SQL nvarchar(max) = N''

select @SQL += N'insert #TableCounts (SchemaName, TableName, Counts)
select ''' + TABLE_SCHEMA + N'''  SchemaName, ''' + TABLE_NAME + N''' TableName,count(*) Counts from [' + TABLE_SCHEMA + N'].[' + TABLE_NAME + N']
' from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE Table'
order by TABLE_NAME

--print @SQL
--select @SQL
exec (@SQL)

select * from #TableCounts

drop table #TableCounts


Cursors VS Set Based:

NOTE: Cursors behave much like programmatic loops and although the cursor is slower in the following example, it is good to keep in mind that a long running set based SQL method could risk lock escalation if it runs very long having escalated to a table lock on STU (that would be very bad). This example is very quick and does not have lock escalation issues.


A Cursor method to remove duplicate records in FRE that may exist for a few reasons...

/*   FRE De-duper
by: James Lancaster - Aeries Software c2018
*/

--First create a backup of FRE in case something goes wrong

declare @BackupFRE nvarchar(max) = '
SELECT *
INTO FRE_BAK' + (select cast(replace(replace(replace(convert(varchar(20),current_timestamp,20),'-',''),':',''),' ','') as varchar(14))) + ' 
FROM FRE;'
exec (@BackupFRE)

--Grab all fields except SQ, DEL, and DTS to look for true duplicates

DECLARE @id INT,  @esd datetime, @eed datetime, @cd varchar(5), @src varchar(5);

DECLARE qrFRE CURSOR DYNAMIC
FOR
    SELECT DISTINCT --Use "DISTINCT" to prevent us from updating the same rows multiple times
           id,
           ESD,
           EED,
           CD,
           src
    FROM FRE
    WHERE del = 0

OPEN qrFRE;

FETCH NEXT FROM qrFRE INTO @id, @esd, @eed, @cd, @src;

WHILE @@fetch_status = 0
    BEGIN
     --This is where we del-tag...
        UPDATE FRE
          SET
              del = 1
        FROM FRE
        WHERE id = @id
              AND isnull(esd, GETDATE()) = isnull(@esd, GETDATE())
              AND cd = @cd
              AND isnull(eed, GETDATE()) = isnull(@eed, GETDATE())
              AND src = @src
              AND del = 0
              AND sq < --Del-tag all but latest SQ record
        (
            SELECT MAX(sq)
            FROM FRE
            WHERE id = @id
              AND isnull(esd, GETDATE()) = isnull(@esd, GETDATE())
              AND cd = @cd
              AND isnull(eed, GETDATE()) = isnull(@eed, GETDATE())
              AND src = @src
              AND del = 0
        );
        FETCH NEXT FROM qrFRE INTO @id, @esd, @eed, @cd, @src;
    END;

CLOSE qrFRE;

DEALLOCATE qrFRE;



Here's a set based method accomplishing about the same results. It delete tags all sequence numbers lower than the max(sq) unique data. It is broken up in to a select that first displays what will need deletion. The part that does the work is remarked out for safety.

NOTE: The remarked out region also contains dynamic SQL to backup the FRE table with a timestamp as FRE_BAKyyyymmddhhmmss

--Show FRE Duplicates (MAX SQ Omitted)
--Jared Kuszewski - Aeries Software
select * from fre 
where sq <> (select max(sq) from fre f where del = 0
and id = fre.id and FS = fre.fs and AF = fre.af and FC = fre.fc and src = fre.src 
and isnull(dt, '1/1/1900') = isnull(fre.dt, '1/1/1900')
and CD = fre.cd and EC = fre.ec 
and isnull(esd, '1/1/1900') = isnull(fre.esd, '1/1/1900')
and isnull(eed, '1/1/1900') = isnull(fre.eed, '1/1/1900')
and isnull(psd, '1/1/1900') = isnull(fre.psd, '1/1/1900')
and isnull(ped, '1/1/1900') = isnull(fre.ped, '1/1/1900')
)
and del = 0

/*
--First create a backup of FRE in case something goes wrong

declare @BackupFRE nvarchar(max) = '
SELECT *
INTO FRE_BAK' + (select cast(replace(replace(replace(convert(varchar(20),current_timestamp,20),'-',''),':',''),' ','') as varchar(14))) + ' 
FROM FRE;'
exec (@BackupFRE)

--Del Tag older duplicate FRE records since the distinct MAX SQ records are likely correct
update fre set del = 1 
from fre inner join (
--Show FRE Duplicates (MAX SQ Omitted)
select * from fre 
where sq <> (select max(sq) from fre f where del = 0
and id = fre.id and FS = fre.fs and AF = fre.af and FC = fre.fc and src = fre.src 
and isnull(dt, '1/1/1900') = isnull(fre.dt, '1/1/1900')
and CD = fre.cd and EC = fre.ec 
and isnull(esd, '1/1/1900') = isnull(fre.esd, '1/1/1900')
and isnull(eed, '1/1/1900') = isnull(fre.eed, '1/1/1900')
and isnull(psd, '1/1/1900') = isnull(fre.psd, '1/1/1900')
and isnull(ped, '1/1/1900') = isnull(fre.ped, '1/1/1900')
)
and del = 0
) a
on fre.id = a.id and fre.sq = a.sq
*/


Pivot without using PIVOT:

Subqueries vs aggregate pivot technique

--subqueries
SELECT CD, NM, 
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = -2) AS [PS],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = -1) AS [TK],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 0) AS [K],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 1) AS [1],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 2) AS [2],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 3) AS [3],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 4) AS [4],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 5) AS [5],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 6) AS [6],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 7) AS [7],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 8) AS [8],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 9) AS [9],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 10) AS [10],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 11) AS [11],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 12) AS [12],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 13) AS [13],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 14) AS [14],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 15) AS [15],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 16) AS [16],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 17) AS [17],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 18) AS [18]
FROM LOC WHERE DEL = 0
ORDER BY CD

--aggregate style pivot is more efficient
select  max(cd) cd, max(nm) nm, 
isnull(max(case when gr = -2 then countgr else null end),0) [PS],
isnull(max(case when gr = -1 then countgr else null end),0) [TK],
isnull(max(case when gr = 0 then countgr else null end),0) [K],
isnull(max(case when gr = 1 then countgr else null end),0) [1],
isnull(max(case when gr = 2 then countgr else null end),0) [2],
isnull(max(case when gr = 3 then countgr else null end),0) [3],
isnull(max(case when gr = 4 then countgr else null end),0) [4],
isnull(max(case when gr = 5 then countgr else null end),0) [5],
isnull(max(case when gr = 6 then countgr else null end),0) [6],
isnull(max(case when gr = 7 then countgr else null end),0) [7],
isnull(max(case when gr = 8 then countgr else null end),0) [8],
isnull(max(case when gr = 9 then countgr else null end),0) [9],
isnull(max(case when gr = 10 then countgr else null end),0) [10],
isnull(max(case when gr = 11 then countgr else null end),0) [11],
isnull(max(case when gr = 12 then countgr else null end),0) [12],
isnull(max(case when gr = 13 then countgr else null end),0) [13],
isnull(max(case when gr = 14 then countgr else null end),0) [14],
isnull(max(case when gr = 15 then countgr else null end),0) [15],
isnull(max(case when gr = 16 then countgr else null end),0) [16],
isnull(max(case when gr = 17 then countgr else null end),0) [17],
isnull(max(case when gr = 18 then countgr else null end),0) [18]

from loc left join
(select sc, gr, count(gr) CountGR
from stu
where stu.del = 0 --and tg = ''
group by sc, gr
) Tallies on loc.cd = tallies.sc
where loc.del = 0
group by loc.cd
order by loc.cd


Running both these queries arriving at the same relative results and choosing to display the execution plan reveals the more efficient method in this challenge.


Then Kevin Quinn of Placentia-Yorba Linda Unified School District tops my quickly prepared (Jared) way of doing it after our Spring 2020 Aeries Con. It always makes me smile when others contribute...

--subqueries
SELECT CD, NM, 
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = -2) AS [PS],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = -1) AS [TK],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 0) AS [K],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 1) AS [1],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 2) AS [2],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 3) AS [3],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 4) AS [4],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 5) AS [5],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 6) AS [6],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 7) AS [7],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 8) AS [8],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 9) AS [9],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 10) AS [10],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 11) AS [11],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 12) AS [12],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 13) AS [13],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 14) AS [14],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 15) AS [15],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 16) AS [16],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 17) AS [17],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 18) AS [18]
FROM LOC WHERE DEL = 0
ORDER BY CD

--aggregate style pivot is more efficient
select  max(cd) cd, max(nm) nm, 
  isnull(max(case when gr = -2 then countgr else null end),0) [PS],
  isnull(max(case when gr = -1 then countgr else null end),0) [TK],
  isnull(max(case when gr = 0 then countgr else null end),0) [K],
  isnull(max(case when gr = 1 then countgr else null end),0) [1],
  isnull(max(case when gr = 2 then countgr else null end),0) [2],
  isnull(max(case when gr = 3 then countgr else null end),0) [3],
  isnull(max(case when gr = 4 then countgr else null end),0) [4],
  isnull(max(case when gr = 5 then countgr else null end),0) [5],
  isnull(max(case when gr = 6 then countgr else null end),0) [6],
  isnull(max(case when gr = 7 then countgr else null end),0) [7],
  isnull(max(case when gr = 8 then countgr else null end),0) [8],
  isnull(max(case when gr = 9 then countgr else null end),0) [9],
  isnull(max(case when gr = 10 then countgr else null end),0) [10],
  isnull(max(case when gr = 11 then countgr else null end),0) [11],
  isnull(max(case when gr = 12 then countgr else null end),0) [12],
  isnull(max(case when gr = 13 then countgr else null end),0) [13],
  isnull(max(case when gr = 14 then countgr else null end),0) [14],
  isnull(max(case when gr = 15 then countgr else null end),0) [15],
  isnull(max(case when gr = 16 then countgr else null end),0) [16],
  isnull(max(case when gr = 17 then countgr else null end),0) [17],
  isnull(max(case when gr = 18 then countgr else null end),0) [18]
from loc left join
  (select sc, gr, count(gr) CountGR
  from stu
  where stu.del = 0 --and tg = ''
  group by sc, gr
  ) Tallies on loc.cd = tallies.sc
where loc.del = 0
group by loc.cd
order by loc.cd



--Summing a case statement in a derived by Kevin Quinn for the win
SELECT  loc.cd, loc.nm, 
  isnull(tallies.[PS],0) [PS],
  isnull(tallies.[TK],0) [TK],
  isnull(tallies.[K],0) [K],
  isnull(tallies.[1],0) [1],
  isnull(tallies.[2],0) [2],
  isnull(tallies.[3],0) [3],
  isnull(tallies.[4],0) [4],
  isnull(tallies.[5],0) [5],
  isnull(tallies.[6],0) [6],
  isnull(tallies.[7],0) [7],
  isnull(tallies.[8],0) [8],
  isnull(tallies.[9],0) [9],
  isnull(tallies.[10],0) [10],
  isnull(tallies.[11],0) [11],
  isnull(tallies.[12],0) [12],
  isnull(tallies.[13],0) [13],
  isnull(tallies.[14],0) [14],
  isnull(tallies.[15],0) [15],
  isnull(tallies.[16],0) [16],
  isnull(tallies.[17],0) [17],
  isnull(tallies.[18],0) [18]
FROM loc
left join (select  sc,
    sum(case when gr = -2 then 1 else 0 end) [PS],
    sum(case when gr = -1 then 1 else 0 end) [TK],
    sum(case when gr = 0 then 1 else 0 end) [K],
    sum(case when gr = 1 then 1 else 0 end) [1],
    sum(case when gr = 2 then 1 else 0 end) [2],
    sum(case when gr = 3 then 1 else 0 end) [3],
    sum(case when gr = 4 then 1 else 0 end) [4],
    sum(case when gr = 5 then 1 else 0 end) [5],
    sum(case when gr = 6 then 1 else 0 end) [6],
    sum(case when gr = 7 then 1 else 0 end) [7],
    sum(case when gr = 8 then 1 else 0 end) [8],
    sum(case when gr = 9 then 1 else 0 end) [9],
    sum(case when gr = 10 then 1 else 0 end) [10],
    sum(case when gr = 11 then 1 else 0 end) [11],
    sum(case when gr = 12 then 1 else 0 end) [12],
    sum(case when gr = 13 then 1 else 0 end) [13],
    sum(case when gr = 14 then 1 else 0 end) [14],
    sum(case when gr = 15 then 1 else 0 end) [15],
    sum(case when gr = 16 then 1 else 0 end) [16],
    sum(case when gr = 17 then 1 else 0 end) [17],
    sum(case when gr = 18 then 1 else 0 end) [18]
    from stu 
    where del = 0 --and tg = ''
  group by sc) Tallies ON tallies.sc = loc.cd
where loc.del = 0




Derived Tables:

Derived Tables are subqueries within the from portion of an outer query. These can allow you to think of one query as a modular part of something greater. It can help you avoid relying on temp tables to hold outputs you would use in a later expression for your final result.


--THIS USE OF A DERIVED TABLE EXAMPLE FINDS ALL UNDELETED CONTACTS BY THE ORDER ENTERED AND INCLUDES ONLY THE FIRST IN THE OUTER QUERY 
select STU.SC, STU.SN, STU.ID, STU.LN, STU.FN, STU.GR, STU.SP, STU.TG,
C.FN, C.LN, C.NM, C.TL, C.EM
FROM
STU LEFT JOIN 

--DERIVED TABLE WRAPPED WITH PARENTHESIS IN JOIN
(
SELECT PID, FN, LN, NM, TL, EM,

ROW_NUMBER() OVER (PARTITION BY PID ORDER BY SQ) ROWNUM --WINDOWED FUNCTION TO NUMBER CONTACTS BY SQ

FROM CON 
WHERE DEL = 0
) C -- DERIVED TABLE ALIASED AS C IN THIS EXAMPLE

ON STU.ID = C.PID 
AND C.ROWNUM = 1

WHERE STU.DEL = 0 AND TG = ''



This Pivot Example will be used in the following as two different testing administrations on the same one line.

SELECT        STU.ID, STU.FN, STU.LN, TST.ID AS TestID, TST.TA, TST.TD,
--ISNULL(MAX(CASE WHEN TST.PT = 0 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT0RS,
ISNULL(MAX(CASE WHEN TST.PT = 0 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT0SS,
--ISNULL(MAX(CASE WHEN TST.PT = 0 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT0PL,

--ISNULL(MAX(CASE WHEN TST.PT = 1 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT1RS,
ISNULL(MAX(CASE WHEN TST.PT = 1 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT1SS,
--ISNULL(MAX(CASE WHEN TST.PT = 1 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT1PL,

--ISNULL(MAX(CASE WHEN TST.PT = 2 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT2RS,
ISNULL(MAX(CASE WHEN TST.PT = 2 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT2SS
--ISNULL(MAX(CASE WHEN TST.PT = 2 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT2PL,

--ISNULL(MAX(CASE WHEN TST.PT = 3 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT3RS,
--ISNULL(MAX(CASE WHEN TST.PT = 3 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT3SS,
--ISNULL(MAX(CASE WHEN TST.PT = 3 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT3PL,

--ISNULL(MAX(CASE WHEN TST.PT = 4 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT4RS,
--ISNULL(MAX(CASE WHEN TST.PT = 4 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT4SS,
--ISNULL(MAX(CASE WHEN TST.PT = 4 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT4PL,

--ISNULL(MAX(CASE WHEN TST.PT = 5 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT5RS,
--ISNULL(MAX(CASE WHEN TST.PT = 5 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT5SS,
--ISNULL(MAX(CASE WHEN TST.PT = 5 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT5PL,

--ISNULL(MAX(CASE WHEN TST.PT = 6 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT6RS,
--ISNULL(MAX(CASE WHEN TST.PT = 6 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT6SS,
--ISNULL(MAX(CASE WHEN TST.PT = 6 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT6PL


FROM            STU INNER JOIN
                         TST ON STU.ID = TST.PID
WHERE        (TST.ID = 'elpac')
group by STU.ID, STU.FN, STU.LN, TST.ID, TST.TA, TST.TD


So we take the example above and throw it in to two derived tables targeting different tests to try and contrast on one line. (NOTE: demo data is not as cool as your real data!)

select STU.SC, STU.ID, STU.FN, STU.LN, STU.SP, STU.TG, ELPAC.TestID, ELPAC.TA, ELPAC.TD, ELPAC.PT0SS, ELPAC.PT1SS, ELPAC.PT2SS,
CELDT.TestID, CELDT.TA, CELDT.TD, CELDT.PT0SS, CELDT.PT1SS, CELDT.PT2SS

FROM STU LEFT JOIN
(
SELECT        STU.ID, STU.FN, STU.LN, TST.ID AS TestID, TST.TA, TST.TD,
--ISNULL(MAX(CASE WHEN TST.PT = 0 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT0RS,
ISNULL(MAX(CASE WHEN TST.PT = 0 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT0SS,
--ISNULL(MAX(CASE WHEN TST.PT = 0 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT0PL,

--ISNULL(MAX(CASE WHEN TST.PT = 1 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT1RS,
ISNULL(MAX(CASE WHEN TST.PT = 1 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT1SS,
--ISNULL(MAX(CASE WHEN TST.PT = 1 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT1PL,

--ISNULL(MAX(CASE WHEN TST.PT = 2 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT2RS,
ISNULL(MAX(CASE WHEN TST.PT = 2 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT2SS
--ISNULL(MAX(CASE WHEN TST.PT = 2 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT2PL,

--ISNULL(MAX(CASE WHEN TST.PT = 3 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT3RS,
--ISNULL(MAX(CASE WHEN TST.PT = 3 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT3SS,
--ISNULL(MAX(CASE WHEN TST.PT = 3 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT3PL,

--ISNULL(MAX(CASE WHEN TST.PT = 4 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT4RS,
--ISNULL(MAX(CASE WHEN TST.PT = 4 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT4SS,
--ISNULL(MAX(CASE WHEN TST.PT = 4 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT4PL,

--ISNULL(MAX(CASE WHEN TST.PT = 5 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT5RS,
--ISNULL(MAX(CASE WHEN TST.PT = 5 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT5SS,
--ISNULL(MAX(CASE WHEN TST.PT = 5 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT5PL,

--ISNULL(MAX(CASE WHEN TST.PT = 6 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT6RS,
--ISNULL(MAX(CASE WHEN TST.PT = 6 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT6SS,
--ISNULL(MAX(CASE WHEN TST.PT = 6 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT6PL


FROM            STU INNER JOIN
                         TST ON STU.ID = TST.PID
WHERE        (TST.ID = 'ELPAC')
AND TST.TD BETWEEN '8/1/2018' AND '8/1/2019'
group by STU.ID, STU.FN, STU.LN, TST.ID, TST.TA, TST.TD
) ELPAC
ON STU.ID = ELPAC.ID
LEFT JOIN
(
SELECT        STU.ID, STU.FN, STU.LN, TST.ID AS TestID, TST.TA, TST.TD,
--ISNULL(MAX(CASE WHEN TST.PT = 0 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT0RS,
ISNULL(MAX(CASE WHEN TST.PT = 0 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT0SS,
--ISNULL(MAX(CASE WHEN TST.PT = 0 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT0PL,

--ISNULL(MAX(CASE WHEN TST.PT = 1 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT1RS,
ISNULL(MAX(CASE WHEN TST.PT = 1 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT1SS,
--ISNULL(MAX(CASE WHEN TST.PT = 1 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT1PL,

--ISNULL(MAX(CASE WHEN TST.PT = 2 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT2RS,
ISNULL(MAX(CASE WHEN TST.PT = 2 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT2SS
--ISNULL(MAX(CASE WHEN TST.PT = 2 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT2PL,

--ISNULL(MAX(CASE WHEN TST.PT = 3 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT3RS,
--ISNULL(MAX(CASE WHEN TST.PT = 3 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT3SS,
--ISNULL(MAX(CASE WHEN TST.PT = 3 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT3PL,

--ISNULL(MAX(CASE WHEN TST.PT = 4 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT4RS,
--ISNULL(MAX(CASE WHEN TST.PT = 4 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT4SS,
--ISNULL(MAX(CASE WHEN TST.PT = 4 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT4PL,

--ISNULL(MAX(CASE WHEN TST.PT = 5 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT5RS,
--ISNULL(MAX(CASE WHEN TST.PT = 5 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT5SS,
--ISNULL(MAX(CASE WHEN TST.PT = 5 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT5PL,

--ISNULL(MAX(CASE WHEN TST.PT = 6 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT6RS,
--ISNULL(MAX(CASE WHEN TST.PT = 6 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT6SS,
--ISNULL(MAX(CASE WHEN TST.PT = 6 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT6PL


FROM            STU INNER JOIN
                         TST ON STU.ID = TST.PID
WHERE        (TST.ID = 'CELDT')
AND TST.TD BETWEEN '8/1/2018' AND '8/1/2019'
group by STU.ID, STU.FN, STU.LN, TST.ID, TST.TA, TST.TD
) CELDT
ON STU.ID = CELDT.ID
WHERE CELDT.ID IS NOT NULL OR ELPAC.ID IS NOT NULL


The prior two examples made more sense when I wrote them during the transition between CELDT to ELPAC however you may find the following more inspiring in the now. Aggregate analysis of ELPAC. This was crafted well for a purpose, but look for a dynamic creation similar to PGMS except for aggregate test views in the .


select sc, sn, id, ln, fn, gr,
(select count(pt) from tst where id like '%ELPAC' and pt = 0 and pid = stu.id and del = 0) as P0tries,
isnull(convert(char(10),(select min(TD) from tst where id like '%ELPAC' and pt = 0 and pid = stu.id and del = 0),101),'') as FirstP0Date,
isnull(convert(char(10),(select max(TD) from tst where id like '%ELPAC' and pt = 0 and pid = stu.id and del = 0),101),'') as RecentP0Date,
isnull(cast((select min(PL) from tst where id like '%ELPAC' and pt = 0 and pid = stu.id and del = 0) as varchar(20)),'') as MinP0PL,
isnull(cast((select max(PL) from tst where id like '%ELPAC' and pt = 0 and pid = stu.id and del = 0) as varchar(20)),'') as MaxP0PL,
isnull(cast((select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 0 and pid = stu.id and del = 0) as varchar(20)),'') as AvgP0PL,
isnull(cast((select max(PL) from tst where id like '%ELPAC' and pt = 0 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 0 and pid = stu.id and del = 0)) as varchar(20)),'') as RecentP0PL,
case 
when (select max(PL) from tst where id like '%ELPAC' and pt = 0 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 0 and pid = stu.id and del = 0)) > (select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 0 and pid = stu.id and del = 0) then 'Yes'
when (select max(PL) from tst where id like '%ELPAC' and pt = 0 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 0 and pid = stu.id and del = 0)) < (select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 0 and pid = stu.id and del = 0) then 'No'
when (select max(PL) from tst where id like '%ELPAC' and pt = 0 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 0 and pid = stu.id and del = 0)) = (select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 0 and pid = stu.id and del = 0) then 'Same'
else '' end as P0Improved,
(select count(pt) from tst where id like '%ELPAC' and PT = 1 and pid = stu.id and del = 0) as P1tries,
isnull(convert(char(10),(select min(TD) from tst where id like '%ELPAC' and PT = 1 and pid = stu.id and del = 0),101),'') as FirstP1Date,
isnull(convert(char(10),(select max(TD) from tst where id like '%ELPAC' and PT = 1 and pid = stu.id and del = 0),101),'') as RecentP1Date,
isnull(cast((select min(PL) from tst where id like '%ELPAC' and PT = 1 and pid = stu.id and del = 0) as varchar(20)),'') as MinP1PL,
isnull(cast((select max(PL) from tst where id like '%ELPAC' and PT = 1 and pid = stu.id and del = 0) as varchar(20)),'') as MaxP1PL,
isnull(cast((select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and PT = 1 and pid = stu.id and del = 0) as varchar(20)),'') as AvgP1PL,
isnull(cast((select max(PL) from tst where id like '%ELPAC' and PT = 1 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and PT = 1 and pid = stu.id and del = 0)) as varchar(20)),'') as RecentP1PL,
case 
when (select max(PL) from tst where id like '%ELPAC' and PT = 1 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and PT = 1 and pid = stu.id and del = 0)) > (select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and PT = 1 and pid = stu.id and del = 0) then 'Yes'
when (select max(PL) from tst where id like '%ELPAC' and PT = 1 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and PT = 1 and pid = stu.id and del = 0)) < (select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and PT = 1 and pid = stu.id and del = 0) then 'No'
when (select max(PL) from tst where id like '%ELPAC' and PT = 1 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and PT = 1 and pid = stu.id and del = 0)) = (select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and PT = 1 and pid = stu.id and del = 0) then 'Same'
else '' end as P1Improved,
(select count(pt) from tst where id like '%ELPAC' and pt = 2 and pid = stu.id and del = 0) as P2tries,
isnull(convert(char(10),(select min(TD) from tst where id like '%ELPAC' and pt = 2 and pid = stu.id and del = 0),101),'') as FirstP2Date,
isnull(convert(char(10),(select max(TD) from tst where id like '%ELPAC' and pt = 2 and pid = stu.id and del = 0),101),'') as RecentP2Date,
isnull(cast((select min(PL) from tst where id like '%ELPAC' and pt = 2 and pid = stu.id and del = 0) as varchar(20)),'') as MinP2PL,
isnull(cast((select max(PL) from tst where id like '%ELPAC' and pt = 2 and pid = stu.id and del = 0) as varchar(20)),'') as MaxP2PL,
isnull(cast((select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 2 and pid = stu.id and del = 0) as varchar(20)),'') as AvgP2PL,
isnull(cast((select max(PL) from tst where id like '%ELPAC' and pt = 2 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 2 and pid = stu.id and del = 0)) as varchar(20)),'') as RecentP2PL,
case 
when (select max(PL) from tst where id like '%ELPAC' and pt = 2 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 2 and pid = stu.id and del = 0)) > (select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 2 and pid = stu.id and del = 0) then 'Yes'
when (select max(PL) from tst where id like '%ELPAC' and pt = 2 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 2 and pid = stu.id and del = 0)) < (select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 2 and pid = stu.id and del = 0) then 'No'
when (select max(PL) from tst where id like '%ELPAC' and pt = 2 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 2 and pid = stu.id and del = 0)) = (select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 2 and pid = stu.id and del = 0) then 'Same'
else '' end as P2Improved,
(select count(pt) from tst where id like '%ELPAC' and pt = 3 and pid = stu.id and del = 0) as P3tries,
isnull(convert(char(10),(select min(TD) from tst where id like '%ELPAC' and pt = 3 and pid = stu.id and del = 0),101),'') as FirstP3Date,
isnull(convert(char(10),(select max(TD) from tst where id like '%ELPAC' and pt = 3 and pid = stu.id and del = 0),101),'') as RecentP3Date,
isnull(cast((select min(PL) from tst where id like '%ELPAC' and pt = 3 and pid = stu.id and del = 0) as varchar(20)),'') as MinP3PL,
isnull(cast((select max(PL) from tst where id like '%ELPAC' and pt = 3 and pid = stu.id and del = 0) as varchar(20)),'') as MaxP3PL,
isnull(cast((select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 3 and pid = stu.id and del = 0) as varchar(20)),'') as AvgP3PL,
isnull(cast((select max(PL) from tst where id like '%ELPAC' and pt = 3 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 3 and pid = stu.id and del = 0)) as varchar(20)),'') as RecentP3PL,
case 
when (select max(PL) from tst where id like '%ELPAC' and pt = 3 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 3 and pid = stu.id and del = 0)) > (select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 3 and pid = stu.id and del = 0) then 'Yes'
when (select max(PL) from tst where id like '%ELPAC' and pt = 3 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 3 and pid = stu.id and del = 0)) < (select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 3 and pid = stu.id and del = 0) then 'No'
when (select max(PL) from tst where id like '%ELPAC' and pt = 3 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 3 and pid = stu.id and del = 0)) = (select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 3 and pid = stu.id and del = 0) then 'Same'
else '' end as P3Improved,
(select count(pt) from tst where id like '%ELPAC' and pt = 4 and pid = stu.id and del = 0) as P4tries,
isnull(convert(char(10),(select min(TD) from tst where id like '%ELPAC' and pt = 4 and pid = stu.id and del = 0),101),'') as FirstP4Date,
isnull(convert(char(10),(select max(TD) from tst where id like '%ELPAC' and pt = 4 and pid = stu.id and del = 0),101),'') as RecentP4Date,
isnull(cast((select min(PL) from tst where id like '%ELPAC' and pt = 4 and pid = stu.id and del = 0) as varchar(20)),'') as MinP4PL,
isnull(cast((select max(PL) from tst where id like '%ELPAC' and pt = 4 and pid = stu.id and del = 0) as varchar(20)),'') as MaxP4PL,
isnull(cast((select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 4 and pid = stu.id and del = 0) as varchar(20)),'') as AvgP4PL,
isnull(cast((select max(PL) from tst where id like '%ELPAC' and pt = 4 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 4 and pid = stu.id and del = 0)) as varchar(20)),'') as RecentP4PL,
case 
when (select max(PL) from tst where id like '%ELPAC' and pt = 4 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 4 and pid = stu.id and del = 0)) > (select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 4 and pid = stu.id and del = 0) then 'Yes'
when (select max(PL) from tst where id like '%ELPAC' and pt = 4 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 4 and pid = stu.id and del = 0)) < (select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 4 and pid = stu.id and del = 0) then 'No'
when (select max(PL) from tst where id like '%ELPAC' and pt = 4 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 4 and pid = stu.id and del = 0)) = (select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 4 and pid = stu.id and del = 0) then 'Same'
else '' end as P4Improved,
(select count(pt) from tst where id like '%ELPAC' and pt = 5 and pid = stu.id and del = 0) as P5tries,
isnull(convert(char(10),(select min(TD) from tst where id like '%ELPAC' and pt = 5 and pid = stu.id and del = 0),101),'') as FirstP5Date,
isnull(convert(char(10),(select max(TD) from tst where id like '%ELPAC' and pt = 5 and pid = stu.id and del = 0),101),'') as RecentP5Date,
isnull(cast((select min(PL) from tst where id like '%ELPAC' and pt = 5 and pid = stu.id and del = 0) as varchar(20)),'') as MinP5PL,
isnull(cast((select max(PL) from tst where id like '%ELPAC' and pt = 5 and pid = stu.id and del = 0) as varchar(20)),'') as MaxP5PL,
isnull(cast((select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 5 and pid = stu.id and del = 0) as varchar(20)),'') as AvgP5PL,
isnull(cast((select max(PL) from tst where id like '%ELPAC' and pt = 5 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 5 and pid = stu.id and del = 0)) as varchar(20)),'') as RecentP5PL,
case 
when (select max(PL) from tst where id like '%ELPAC' and pt = 5 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 5 and pid = stu.id and del = 0)) > (select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 5 and pid = stu.id and del = 0) then 'Yes'
when (select max(PL) from tst where id like '%ELPAC' and pt = 5 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 5 and pid = stu.id and del = 0)) < (select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 5 and pid = stu.id and del = 0) then 'No'
when (select max(PL) from tst where id like '%ELPAC' and pt = 5 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 5 and pid = stu.id and del = 0)) = (select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 5 and pid = stu.id and del = 0) then 'Same'
else '' end as P5Improved,
(select count(pt) from tst where id like '%ELPAC' and pt = 6 and pid = stu.id and del = 0) as P6tries,
isnull(convert(char(10),(select min(TD) from tst where id like '%ELPAC' and pt = 6 and pid = stu.id and del = 0),101),'') as FirstP6Date,
isnull(convert(char(10),(select max(TD) from tst where id like '%ELPAC' and pt = 6 and pid = stu.id and del = 0),101),'') as RecentP6Date,
isnull(cast((select min(PL) from tst where id like '%ELPAC' and pt = 6 and pid = stu.id and del = 0) as varchar(20)),'') as MinP6PL,
isnull(cast((select max(PL) from tst where id like '%ELPAC' and pt = 6 and pid = stu.id and del = 0) as varchar(20)),'') as MaxP6PL,
isnull(cast((select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 6 and pid = stu.id and del = 0) as varchar(20)),'') as AvgP6PL,
isnull(cast((select max(PL) from tst where id like '%ELPAC' and pt = 6 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 6 and pid = stu.id and del = 0)) as varchar(20)),'') as RecentP6PL,
case 
when (select max(PL) from tst where id like '%ELPAC' and pt = 6 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 6 and pid = stu.id and del = 0)) > (select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 6 and pid = stu.id and del = 0) then 'Yes'
when (select max(PL) from tst where id like '%ELPAC' and pt = 6 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 6 and pid = stu.id and del = 0)) < (select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 6 and pid = stu.id and del = 0) then 'No'
when (select max(PL) from tst where id like '%ELPAC' and pt = 6 and pid = stu.id and del = 0 and td = (select max(TD) from tst where id like '%ELPAC' and pt = 6 and pid = stu.id and del = 0)) = (select avg(cast(PL as dec(12,6))) from tst where id like '%ELPAC' and pt = 6 and pid = stu.id and del = 0) then 'Same'
else '' end as P6Improved

from stu
where del = 0
and tg = ''
--remark out or don't include the following line if you wish to include students not having taken ELPAC
and id in (select distinct pid from tst where id like '%ELPAC' and del = 0)


CTE (Common Table Expression):

A CTE is a self deallocating temp table. It has the one requirement that it must be used in the very next query. This could be deeply nested in a derived table if needed and also fulfill the immediate use requirement. A CTE also has the benefit of making recursive query possible.


--A CTE (COMMON TABLE EXPRESSION) IS A SELF DEALLOCATING TEMP TABLE THAT MUST BE USED IN THE NEXT QUERY, NO MATTER THE DEPTH.
--BEGIN A CTE WITH THE PHRASE "WITH". PRECEEDING THE WORD "WITH" WITH A SEMICOLON TO ASSURE IT IS FIRST IN A BATCH IS ALSO COMMON 
--IN THIS EXAMPLE WE SELECT ITEMS FROM STU FOR LATER USE. THIS CAN AVOID LOCK ESCALATION REACHING THE STU TABLE!

CREATE VIEW STUview
AS
WITH CTE 

(SC, SN, ID, LN, FN, GR, SP, TG)
AS
(SELECT SC, SN, ID, LN, FN, GR, SP, TG
FROM STU 
WHERE DEL = 0)

SELECT * FROM CTE
GO
--STUview was created!


SELECT STUview.*, CON.NM, CON.TL, CON.EM 
FROM STUview LEFT JOIN CON
ON STUview.ID = CON.PID AND CON.DEL = 0

--DROP VIEW STUview


Another fun Recursive CTE to help evaluate characters within a string

--What Characters are in a string? (Up to 32767 as set in MAXRECURSION below which is MSSQLSERVER's max it appears)
--Apostrophes within the string have to be depicted as a double apostrophe
--Unremark and use the where clause on line 22 to find the character position of specific characters alone
DECLARE @string NVARCHAR(MAX) = N'Whatever ╬™Ω☺☻☼♥♪''♫ 
'

DECLARE @SQL NVARCHAR(MAX) = N'
WITH CHARS (Position, TheCharacter, asASCII, UnicodeNum, ASCIInum) AS (SELECT 1 AS Position
,substring(N''' + replace(@string,'''','''''') + N''',1,1) TheCharacter
,cast(substring(N''' + replace(@string,'''','''''') + N''',1,1) as varchar(1)) asASCII
,unicode(substring(N''' + replace(@string,'''','''''') + N''',1,1)) UnicodeNum
,ASCII(substring(N''' + replace(@string,'''','''''') + N''',1,1)) ASCIInum
 UNION ALL 
 SELECT Position + 1 Position
,substring(N''' + replace(@string,'''','''''') + N''',Position + 1,1) TheCharacter
,cast(substring(N''' + replace(@string,'''','''''') + N''',Position + 1,1) as varchar(1)) asASCII
,unicode(substring(N''' + replace(@string,'''','''''') + N''',Position + 1,1)) UnicodeNum
,ASCII(substring(N''' + replace(@string,'''','''''') + N''',Position + 1,1)) ASCIInum
  FROM CHARS WHERE Position + 1 <= ' + cast(datalength(cast(@string as varchar(max))) as varchar(20)) + ') 
SELECT * FROM CHARS
--where TheCharacter = N''e''
--COLLATE Latin1_General_CS_AS --Case Sensitive comparison
OPTION (MAXRECURSION 32767)
'
--print @SQL
exec (@SQL)



To see a practical example of Recursive CTE in action, see the Permissions Analysis Queries...

https://support.aeries.com/support/solutions/articles/14000059245-permissions-analysis-queries


To see an example of using a CTE in a view to avoid redundant evaluations of day of the week logic, see the Attendance Percentage by Period (APBP) view in this article...

https://support.aeries.com/support/solutions/articles/14000092446-aeries-query-expansion-through-sql-views