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 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):

SCSNIDLNFNMNGRDescription_LFDescription_SPDescription_TGNSLPSpEd504 Accommodation PlanNCLB Title I Part A Basic TargetedGifted and Talented Education (GATE)Title I Part C MigrantTutoringEducational Options: Advancement Via Individual Determination (AVID)Foster ProgramHomeless Program
994199400001AbbottAllanJames12English LearnerRegular ProgramActiveFYNoYesYesNoNoNoYesNo
994299400002AbdelnourAlice
9English LearnerRegular ProgramActive

NoNoYesNoNoNoNoNo
994399400003AbdoAliceA9English OnlyRegular ProgramActive

NoNoNoNoNoNoYesNo
994499400004AbdoArnoldA10English OnlyRegular ProgramActive

YesNoNoNoNoNoNoNo
994599400005AbeaAyriannaJ12English LearnerRegular ProgramActive
YNoNoNoNoNoNoNoNo
994699400006AbejonTanyaA10English OnlyRegular ProgramActive

NoNoNoNoNoNoNoNo
994799400007AbesamisSiobhanA12English OnlyRegular ProgramActive

NoNoNoNoNoNoNoNo
994899400008AbesamisTatianaJ9English OnlyRegular ProgramActive

NoNoNoNoNoNoYesNo
994999400009AbneyJessicaMarie12
Regular ProgramActive

NoNoNoNoNoNoNoNo


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 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 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 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 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 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 pid = stu.id) then ''Undeclared''
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):

scsnidlnfnmngrtglfspInclude in Student Address Directory ListingVoluntary Student Accident InsuranceUse the InternetRelease Records to MilitaryPhoto Use in District Brochures and Press ReleasesSurveys Permitted
994199400001AbbottAllanJames12
L
YesYesYesNoNoNo
994299400002AbdelnourAlice
9
L
UndeclaredUndeclaredUndeclaredUndeclaredUndeclaredUndeclared
994399400003AbdoAliceA9
E
UndeclaredUndeclaredUndeclaredUndeclaredUndeclaredUndeclared
994499400004AbdoArnoldA10
E
UndeclaredUndeclaredUndeclaredUndeclaredUndeclaredUndeclared
994599400005AbeaAyriannaJ12
L
UndeclaredUndeclaredUndeclaredUndeclaredUndeclaredUndeclared
994699400006AbejonTanyaA10
E
UndeclaredUndeclaredUndeclaredUndeclaredUndeclaredUndeclared
994799400007AbesamisSiobhanA12
E
UndeclaredUndeclaredUndeclaredUndeclaredUndeclaredUndeclared
994899400008AbesamisTatianaJ9
E
UndeclaredUndeclaredUndeclaredUndeclaredUndeclaredUndeclared
994999400009AbneyJessicaMarie12
F
UndeclaredUndeclaredUndeclaredUndeclaredUndeclaredUndeclared


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 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 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 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 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 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 pid = stu.id) then ''Undeclared''
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)



SCSNIDLNFNMNGRDescription_LFDescription_SPDescription_TGNSLPSpEd504 Accommodation PlanNCLB Title I Part A Basic TargetedGifted and Talented Education (GATE)Title I Part C MigrantTutoringEducational Options: Advancement Via Individual Determination (AVID)Foster ProgramHomeless ProgramInclude in Student Address Directory ListingVoluntary Student Accident InsuranceUse the InternetRelease Records to MilitaryPhoto Use in District Brochures and Press ReleasesSurveys Permitted
994199400001AbbottAllanJames12English LearnerRegular ProgramActiveFYNoYesYesNoNoNoYesNoYesYesYesNoNoNo
994299400002AbdelnourAlice
9English LearnerRegular ProgramActive

NoNoYesNoNoNoNoNoUndeclaredUndeclaredUndeclaredUndeclaredUndeclaredUndeclared
994399400003AbdoAliceA9English OnlyRegular ProgramActive

NoNoNoNoNoNoYesNoUndeclaredUndeclaredUndeclaredUndeclaredUndeclaredUndeclared
994499400004AbdoArnoldA10English OnlyRegular ProgramActive

YesNoNoNoNoNoNoNoUndeclaredUndeclaredUndeclaredUndeclaredUndeclaredUndeclared
994599400005AbeaAyriannaJ12English LearnerRegular ProgramActive
YNoNoNoNoNoNoNoNoUndeclaredUndeclaredUndeclaredUndeclaredUndeclaredUndeclared
994699400006AbejonTanyaA10English OnlyRegular ProgramActive

NoNoNoNoNoNoNoNoUndeclaredUndeclaredUndeclaredUndeclaredUndeclaredUndeclared
994799400007AbesamisSiobhanA12English OnlyRegular ProgramActive

NoNoNoNoNoNoNoNoUndeclaredUndeclaredUndeclaredUndeclaredUndeclaredUndeclared
994899400008AbesamisTatianaJ9English OnlyRegular ProgramActive

NoNoNoNoNoNoYesNoUndeclaredUndeclaredUndeclaredUndeclaredUndeclaredUndeclared
994999400009AbneyJessicaMarie12
Regular ProgramActive

NoNoNoNoNoNoNoNoUndeclaredUndeclaredUndeclaredUndeclaredUndeclaredUndeclared
9941299400012AbregoAliceA9
Regular ProgramActive

NoNoNoNoNoNoNoNoUndeclaredUndeclaredUndeclaredUndeclaredUndeclaredUndeclared
9941599400015AcevesSteven
12English LearnerRegular ProgramActive

NoNoNoNoNoNoNoNoUndeclaredUndeclaredUndeclaredUndeclaredUndeclaredUndeclared
9941699400016AcharyaJosiah
11English LearnerRegular ProgramActive

NoNoNoNoNoNoNoNoUndeclaredUndeclaredUndeclaredUndeclaredUndeclaredUndeclared
9941799400017AckermannKathrinAnn10English OnlyRegular ProgramActive

NoNoNoNoNoNoNoNoUndeclaredUndeclaredUndeclaredUndeclaredUndeclaredUndeclared