People sometimes indicated a desire to output all PGM and/or AUT statuses that are relevant on one line. While Analytics allows a comfortable way to drill in and contrast program involvements or authorization status, and our Custom Reports allow  for the same in its own way as well, having this ability in SQL can also be nice. The building of such a SQL expression may also prove useful in other ways or be more conveniently run from the Aeries interface itself in the future. Exciting things happen with time and all the time with Aeries.

These three examples can be run relatively easy as is or by specifying a single school where indicated.

Here's a video showing it being run...

https://training.aeries.net/jared/PGM_AUT_status

PGM:

--PGM involvement for one school for the current moment or a specific date

declare @DynaQuery nvarchar(max) = ''

select @DynaQuery += N'

declare @day datetime = convert(char(10),current_timestamp,101)
--declare @day datetime = ''1/1/2015'' --or specify a specific date instead of the current moment and do not use the line above. 

--Specify a school number in the next line.   NOTE: You can remark out line 60 to see entire district
declare @sc int = 994

select SC, SN, ID, LN, FN, MN, GR, 
isnull([GD_STU_LF_STU_LF].[DE],'''') AS [Description_LF], 
isnull([GD_STU_SP_STU_SP].[DE],'''') AS [Description_SP], 
isnull([GD_STU_TG_STU_TG].[DE],'''') AS [Description_TG],
isnull(
(select top 1 cd 
from fre 
where id = stu.id and esd <= @day and (eed is null or eed >= @day)
order by esd desc, sq desc)
,'''' ) as NSLP,
isnull((select ''Y'' from cse where id = stu.id and di <> '''' and xd is null and del = 0),'''') SpEd,
' 
select @DynaQuery += N'
case 
when id in (select pid from pgm where cd = ''' + CD + ''' and (psd <= @day and (ped >= @day or ped is null)) and pid = stu.id and del = 0) then ''Yes''
when id in (select pid from pgm where cd = ''' + CD + ''' and (esd <= @day and (eed >= @day or eed is null)) and pid = stu.id and del = 0) then ''Eligible''
when id in (select pid from pgm where cd = ''' + CD + ''' and esd is null and psd is null and pid = stu.id and del = 0) then ''Null Start''
when id in (select pid from pgm where cd = ''' + CD + ''' and psd > @day and pid = stu.id and del = 0) then ''Future Yes''
when id in (select pid from pgm where cd = ''' + CD + ''' and esd > @day and pid = stu.id and del = 0) then ''Future Eligible''
when id in (select pid from pgm where cd = ''' + CD + ''' and esd <= @day and eed <= @day and pid = stu.id and del = 0) then ''Previous Yes''
when id in (select pid from pgm where cd = ''' + CD + ''' and esd <= @day and eed <= @day and pid = stu.id and del = 0) then ''Previous Eligible''
else ''No''
--end as ''PGM_'  + cd + N''' --PGM_CODE as Header
end as '''  + left(de,128) + N''' --Description as Header
,' 
from (select * from (select cd, replace(de,'''','''''') as de from USYSGCOD where tc = 'pgm' and fc = 'cd' and del = 0
union
select cd, replace(de,'''','''''') as de from COD where tc = 'pgm' and fc = 'cd' and del = 0 and cd not in (select cd from USYSGCOD where tc = 'pgm' and fc = 'cd' and del = 0)) codes where de not like '%no longer%' and de not like '%old code%' and cd <> ''

--and cd in ('122','131','135','190') --Even though this query builds an evaluation of all PGM codes with values, use this line to keep it simple and only select the codes you want
and cd in (select distinct pgm.cd from stu inner join pgm on stu.id = pgm.pid where stu.del = 0 
and stu.tg = '' --remark this line out when evaluating historic programs no active students participate in
and pgm.cd <> '')


) PGMcodes

set @DynaQuery = left(@DynaQuery, len(@DynaQuery) - 1) -- remove the final comma  since we can't have a trailing comma before the FROM as there are no new dynamic columns

select @DynaQuery += N'
from stu 
CROSS APPLY dbo.Get_Description_New(@sc, ''STU'', ''LF'', [STU].[LF]) AS [GD_STU_LF_STU_LF] 
CROSS APPLY dbo.Get_Description_New(@sc, ''STU'', ''SP'', [STU].[SP]) AS [GD_STU_SP_STU_SP] 
CROSS APPLY dbo.Get_Description_New(@sc, ''STU'', ''TG'', [STU].[TG]) AS [GD_STU_TG_STU_TG]
where del = 0 
and tg = '''' --remark out this line to include inactives

and sc = @sc --remark this line out to do the whole district (note this is potentially a large SQL burden!
'

--print @DynaQuery --NOTE: Print truncates at NVARCHAR(4000) characters
exec (@DynaQuery)

Example Output (PGM):

AUT:

declare @DynaQuery nvarchar(max) = ''

--Check to see if AUT codes are defined before attempting to run
if exists (select top 1 1 from (select cd, de from COD where tc = 'aut' and fc = 'cd' and del = 0) codes where cd <> ''
and cd in (select distinct cd from aut where del = 0 and pid in (select distinct id from stu where del = 0 and tg = '')) --Just AUT codes on active students
and cd in (select distinct cd from aut where del = 0) --just select AUT codes being used
)

begin --Transaction to evaluate AUT codes since they exist

select @DynaQuery += '
declare @day datetime = convert(char(10),current_timestamp,101)
select sc, sn, id, ln, fn, mn, gr, tg, lf, sp, 
'

select @DynaQuery += '
case 
when id in (select pid from aut where cd = ''' + CD + ''' and st = ''1'' and (dt <= @day and (sdt <= @day or sdt is null) and (eed >= @day or eed is null)) and del = 0 and pid = stu.id) and id not in (select pid from aut where cd = ''' + CD + ''' and st = ''2'' and (dt <= @day and (sdt <= @day or sdt is null) and (eed >= @day or eed is null)) and del = 0 and pid = stu.id) then ''Yes''
when id in (select pid from aut where cd = ''' + CD + ''' and st = ''1'' and (dt <= @day and (sdt <= @day or sdt is null) and (eed >= @day or eed is null)) and del = 0 and pid = stu.id) and id in (select pid from aut where cd = ''' + CD + ''' and st = ''2'' and (dt <= @day and (sdt <= @day or sdt is null) and (eed >= @day or eed is null)) and del = 0 and pid = stu.id) then ''Conflict''
when id not in (select pid from aut where cd = ''' + CD + ''' and st = ''1'' and (dt <= @day and (sdt <= @day or sdt is null) and (eed >= @day or eed is null)) and del = 0 and pid = stu.id) and id not in (select pid from aut where cd = ''' + CD + ''' and st = ''2'' and (dt <= @day and (sdt <= @day or sdt is null) and (eed >= @day or eed is null)) and del = 0 and pid = stu.id) then ''''
else ''No''
end as [' + left(de,128) + '] --AUT description as header
--end as ''AUT_' + cd + ''' --AUT_CD  as header
,'
from (select * from (select cd, de from COD where tc = 'aut' and fc = 'cd' and del = 0) codes where cd <> ''
and cd in (select distinct cd from aut where del = 0 and pid in (select distinct id from stu where del = 0 and tg = '')) --Just AUT codes on active students

--and cd in ('A','B') --Even though this query builds an evaluation of all AUT codes, use this line to keep it simple and only select the codes you want

and cd in (select distinct cd from aut where del = 0) --just select AUT codes being used
) AUTcodes

set @DynaQuery = left(@DynaQuery, len(@DynaQuery) - 1) -- remove the final comma  since we can't have a trailing comma before the FROM as there are no new dynamic columns

select @DynaQuery += '
from stu 
where del = 0 
and tg = '''' 
--and sc = 994
order by sc, ln, fn'

--print @DynaQuery
exec (@DynaQuery)

end --Transaction to evaluate AUT codes since they exist

if @DynaQuery = ''
select 'No AUT codes defined to evaluate' CannotRun

Example Output (AUT):

Both PGM and AUT status combined:

Example Output (PGM and AUT):

--PGM involvement and Authorizations for one school for the current moment or a specific date

declare @DynaQuery nvarchar(max) = ''

select @DynaQuery += N'

declare @day datetime = convert(char(10),current_timestamp,101)
--declare @day datetime = ''1/1/2015'' --or specify a specific date instead of the current moment and do not use the line above. 

--Specify a school number in the next line.   NOTE: You can remark out line 60 to see entire district
declare @sc int = 994

select SC, SN, ID, LN, FN, MN, GR, 
isnull([GD_STU_LF_STU_LF].[DE],'''') AS [Description_LF], 
isnull([GD_STU_SP_STU_SP].[DE],'''') AS [Description_SP], 
isnull([GD_STU_TG_STU_TG].[DE],'''') AS [Description_TG],
isnull(
(select top 1 cd 
from fre 
where id = stu.id and esd <= @day and (eed is null or eed >= @day)
order by esd desc, sq desc)
,'''' ) as NSLP,
isnull((select ''Y'' from cse where id = stu.id and di <> '''' and xd is null and del = 0),'''') SpEd,
' 
select @DynaQuery += N'
case 
when id in (select pid from pgm where cd = ''' + CD + ''' and (psd <= @day and (ped >= @day or ped is null)) and pid = stu.id and del = 0) then ''Yes''
when id in (select pid from pgm where cd = ''' + CD + ''' and (esd <= @day and (eed >= @day or eed is null)) and pid = stu.id and del = 0) then ''Eligible''
when id in (select pid from pgm where cd = ''' + CD + ''' and esd is null and psd is null and pid = stu.id and del = 0) then ''Null Start''
when id in (select pid from pgm where cd = ''' + CD + ''' and psd > @day and pid = stu.id and del = 0) then ''Future Yes''
when id in (select pid from pgm where cd = ''' + CD + ''' and esd > @day and pid = stu.id and del = 0) then ''Future Eligible''
when id in (select pid from pgm where cd = ''' + CD + ''' and esd <= @day and eed <= @day and pid = stu.id and del = 0) then ''Previous Yes''
when id in (select pid from pgm where cd = ''' + CD + ''' and esd <= @day and eed <= @day and pid = stu.id and del = 0) then ''Previous Eligible''
else ''No''
--end as ''PGM_'  + cd + N''' --PGM_CODE as Header
end as '''  + left(de,128) + N''' --Description as Header
,' 
from (select * from (select cd, replace(de,'''','''''') as de from USYSGCOD where tc = 'pgm' and fc = 'cd' and del = 0
union
select cd, replace(de,'''','''''') as de from COD where tc = 'pgm' and fc = 'cd' and del = 0 and cd not in (select cd from USYSGCOD where tc = 'pgm' and fc = 'cd' and del = 0)) codes where de not like '%no longer%' and de not like '%old code%' and cd <> ''

--and cd in ('122','131','135','190') --Even though this query builds an evaluation of all PGM codes with values, use this line to keep it simple and only select the codes you want
and cd in (select distinct pgm.cd from stu inner join pgm on stu.id = pgm.pid where stu.del = 0 
and stu.tg = '' --remark this line out when evaluating historic programs no active students participate in
and pgm.cd <> '')


) PGMcodes

--Check to see if AUT codes are defined before attempting to run
if exists (select top 1 1 from (select cd, de from COD where tc = 'aut' and fc = 'cd' and del = 0) codes where cd <> ''
and cd in (select distinct cd from aut where del = 0 and pid in (select distinct id from stu where del = 0 and tg = '')) --Just AUT codes on active students
and cd in (select distinct cd from aut where del = 0) --just select AUT codes being used
)

begin --Transaction to evaluate AUT codes since they exist

select @DynaQuery += '
case 
when id in (select pid from aut where cd = ''' + CD + ''' and st = ''1'' and (dt <= @day and (sdt <= @day or sdt is null) and (eed >= @day or eed is null)) and del = 0 and pid = stu.id) and id not in (select pid from aut where cd = ''' + CD + ''' and st = ''2'' and (dt <= @day and (sdt <= @day or sdt is null) and (eed >= @day or eed is null)) and del = 0 and pid = stu.id) then ''Yes''
when id in (select pid from aut where cd = ''' + CD + ''' and st = ''1'' and (dt <= @day and (sdt <= @day or sdt is null) and (eed >= @day or eed is null)) and del = 0 and pid = stu.id) and id in (select pid from aut where cd = ''' + CD + ''' and st = ''2'' and (dt <= @day and (sdt <= @day or sdt is null) and (eed >= @day or eed is null)) and del = 0 and pid = stu.id) then ''Conflict''
when id not in (select pid from aut where cd = ''' + CD + ''' and st = ''1'' and (dt <= @day and (sdt <= @day or sdt is null) and (eed >= @day or eed is null)) and del = 0 and pid = stu.id) and id not in (select pid from aut where cd = ''' + CD + ''' and st = ''2'' and (dt <= @day and (sdt <= @day or sdt is null) and (eed >= @day or eed is null)) and del = 0 and pid = stu.id) then ''''
else ''No''
end as [' + left(de,128) + '] --AUT description as header
--end as ''AUT_' + cd + ''' --AUT_CD  as header
,'
from (select * from (select cd, de from COD where tc = 'aut' and fc = 'cd' and del = 0) codes where cd <> ''
and cd in (select distinct cd from aut where del = 0 and pid in (select distinct id from stu where del = 0 and tg = '')) --Just AUT codes on active students

--and cd in ('A','B') --Even though this query builds an evaluation of all AUT codes, use this line to keep it simple and only select the codes you want

and cd in (select distinct cd from aut where del = 0) --just select AUT codes being used
) AUTcodes

set @DynaQuery = left(@DynaQuery, len(@DynaQuery) - 1) -- remove the final comma  since we can't have a trailing comma before the FROM as there are no new dynamic columns

end --Transaction to evaluate AUT codes since they exist

set @DynaQuery = left(@DynaQuery, len(@DynaQuery) - 1) -- remove the final comma  since we can't have a trailing comma before the FROM as there are no new dynamic columns

select @DynaQuery += N'
from stu 
CROSS APPLY dbo.Get_Description_New(@sc, ''STU'', ''LF'', [STU].[LF]) AS [GD_STU_LF_STU_LF] 
CROSS APPLY dbo.Get_Description_New(@sc, ''STU'', ''SP'', [STU].[SP]) AS [GD_STU_SP_STU_SP] 
CROSS APPLY dbo.Get_Description_New(@sc, ''STU'', ''TG'', [STU].[TG]) AS [GD_STU_TG_STU_TG]
where del = 0 
and tg = '''' --remark out this line to include inactives

and sc = @sc --remark this line out to do the whole district (note this is potentially a large SQL burden!
'

--print @DynaQuery --NOTE: Print truncates at NVARCHAR(4000) characters
exec (@DynaQuery)