Dynamic SQL queries to provide a rich one line result of whatever unique Programs and/or Authorizations are current or historical : Aeries Software

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

Description_LF

Description_SP

Description_TG

NSLP

SpEd

504 Accommodation Plan

NCLB Title I Part A Basic Targeted

Gifted and Talented Education (GATE)

Title I Part C Migrant

Tutoring

Educational Options: Advancement Via Individual Determination (AVID)

Foster Program

Homeless Program

994

99400001

Abbott

Allan

James

English Learner

Regular Program

Active

Yes

994

99400002

Abdelnour

Alice

English Learner

Regular Program

Active

Yes

994

99400003

Abdo

Alice

English Only

Regular Program

Active

Yes

994

99400004

Abdo

Arnold

English Only

Regular Program

Active

Yes

994

99400005

Abea

Ayrianna

English Learner

Regular Program

Active

994

99400006

Abejon

Tanya

English Only

Regular Program

Active

994

99400007

Abesamis

Siobhan

English Only

Regular Program

Active

994

99400008

Abesamis

Tatiana

English Only

Regular Program

Active

Yes

994

99400009

Abney

Jessica

Marie

Regular Program

Active

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

Include in Student Address Directory Listing

Voluntary Student Accident Insurance

Use the Internet

Release Records to Military

Photo Use in District Brochures and Press Releases

Surveys Permitted

994

99400001

Abbott

Allan

James

Yes

994

99400002

Abdelnour

Alice

994

99400003

Abdo

Alice

994

99400004

Abdo

Arnold

994

99400005

Abea

Ayrianna

994

99400006

Abejon

Tanya

994

99400007

Abesamis

Siobhan

994

99400008

Abesamis

Tatiana

994

99400009

Abney

Jessica

Marie

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)

Description_LF

Description_SP

Description_TG

NSLP

SpEd

504 Accommodation Plan

NCLB Title I Part A Basic Targeted

Gifted and Talented Education (GATE)

Title I Part C Migrant

Tutoring

Educational Options: Advancement Via Individual Determination (AVID)

Foster Program

Homeless Program

Include in Student Address Directory Listing

Voluntary Student Accident Insurance

Use the Internet

Release Records to Military

Photo Use in District Brochures and Press Releases

Surveys Permitted

994

99400001

Abbott

Allan

James

English Learner

Regular Program

Active

Yes

994

99400002

Abdelnour

Alice

English Learner

Regular Program

Active

Yes

994

99400003

Abdo

Alice

English Only

Regular Program

Active

Yes

994

99400004

Abdo

Arnold

English Only

Regular Program

Active

Yes

994

99400005

Abea

Ayrianna

English Learner

Regular Program

Active

994

99400006

Abejon

Tanya

English Only

Regular Program

Active

994

99400007

Abesamis

Siobhan

English Only

Regular Program

Active

994

99400008

Abesamis

Tatiana

English Only

Regular Program

Active

Yes

994

99400009

Abney

Jessica

Marie

Regular Program

Active

994

99400012

Abrego

Alice

Regular Program

Active

994

99400015

Aceves

Steven

English Learner

Regular Program

Active

994

99400016

Acharya

Josiah

English Learner

Regular Program

Active

994

99400017

Ackermann

Kathrin

Ann

English Only

Regular Program

Active

Support

Dynamic SQL queries to provide a rich one line result of whatever unique Programs and/or Authorizations are current or historical Print

Dynamic SQL queries to provide a rich one line result of whatever unique Programs and/or Authorizations are current or historical Print

Related Articles