This tSQL will not only create a view called PGMS (Program and Authorization Status), but it also adds itself to the CTD (Custom Table Definitions) and CTF (Custom Table Fields) automatically.
Purpose: To have an easy way to see if a student was in a program and what their current authorizations are. Also to have a large amount of pertinent data available in one line for use in conjunction with Aeries Query having come from data that likely would have many rows.
Not everyone uses the same programs, custom programs, or authorizations. No problem! This only shows things you actually use. If you commision a new program, just drop the view and definitions and run it again.
(NSLP Code and SpecialEducation "Y" were also included)
--Create PGMS Custom View if not present - Run lines 4, 5, 6 if you wish to drop a prior PGMS rendition
--Jared Kuszewski - Aeries Software 9/10/2018
/*
drop view pgms
delete from ctf where tc = 'PGMS'
delete from ctd where tc = 'PGMS'
*/
if not exists ((select top 1 1 from ctd where tc = 'PGMS') union (select top 1 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'PGMS'))
BEGIN
--Create PGMS view of PGM involvement and Authorizations
declare @DynaQuery nvarchar(max) = ''
select @DynaQuery += N'
create view PGMS_descrip as
select ID,
isnull(
(select top 1 cd
from fre
where id = idn.id and esd <= convert(varchar(10),current_timestamp,101) and (eed is null or eed >= convert(varchar(10),current_timestamp,101))
order by esd desc, sq desc)
,'''' ) as NSL,
isnull((select ''Y'' from cse where id = idn.id and di <> '''' and xd is null and del = 0),'''') SPD,
'
select @DynaQuery += N'
case
when id in (select pid from pgm where cd = ''' + CD + ''' and (psd <= convert(varchar(10),current_timestamp,101) and (ped >= convert(varchar(10),current_timestamp,101) or ped is null)) and pid = idn.id and del = 0) then ''Yes''
when id in (select pid from pgm where cd = ''' + CD + ''' and (esd <= convert(varchar(10),current_timestamp,101) and (eed >= convert(varchar(10),current_timestamp,101) or eed is null)) and pid = idn.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 = idn.id and del = 0) then ''Null Start''
else ''''
end as ''PGM_' + cd + N''' --PGM_CODE as Header
--end as ''' + left(de,128) + N''' --Description as Header
,'
from (select cd, de from (select cd, replace(replace(de,'''',''''''),' ','_') as de from USYSGCOD where tc = 'pgm' and fc = 'cd' and del = 0
union
select cd, replace(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 (pgm.psd > CURRENT_TIMESTAMP - 400 or pgm.esd > CURRENT_TIMESTAMP - 400) --Only incude programs you still populate in a sensible timeframe.
and stu.tg = '' --remark this line out when evaluating historic programs no active students participate in
and pgm.cd <> '')
) PGMcodes order by cd
--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 <= convert(varchar(10),current_timestamp,101) and (sdt <= convert(varchar(10),current_timestamp,101) or sdt is null) and (eed >= convert(varchar(10),current_timestamp,101) or eed is null)) and pid = idn.id) and id not in (select pid from aut where cd = ''' + CD + ''' and st = ''2'' and (dt <= convert(varchar(10),current_timestamp,101) and (sdt <= convert(varchar(10),current_timestamp,101) or sdt is null) and (eed >= convert(varchar(10),current_timestamp,101) or eed is null)) and pid = idn.id and del = 0) then ''Yes''
when id in (select pid from aut where cd = ''' + CD + ''' and st = ''1'' and (dt <= convert(varchar(10),current_timestamp,101) and (sdt <= convert(varchar(10),current_timestamp,101) or sdt is null) and (eed >= convert(varchar(10),current_timestamp,101) or eed is null)) and pid = idn.id) and id in (select pid from aut where cd = ''' + CD + ''' and st = ''2'' and (dt <= convert(varchar(10),current_timestamp,101) and (sdt <= convert(varchar(10),current_timestamp,101) or sdt is null) and (eed >= convert(varchar(10),current_timestamp,101) or eed is null)) and pid = idn.id and del = 0) then ''Conflict''
when id not in (select pid from aut where cd = ''' + CD + ''' and st = ''1'' and (dt <= convert(varchar(10),current_timestamp,101) and (sdt <= convert(varchar(10),current_timestamp,101) or sdt is null) and (eed >= convert(varchar(10),current_timestamp,101) or eed is null)) and pid = idn.id) and id in (select pid from aut where cd = ''' + CD + ''' and st = ''2'' and (dt <= convert(varchar(10),current_timestamp,101) and (sdt <= convert(varchar(10),current_timestamp,101) or sdt is null) and (eed >= convert(varchar(10),current_timestamp,101) or eed is null)) and pid = idn.id and del = 0) then ''No''
else ''''
end as ''AUT_' + cd + ''' --AUT_CD as header
,'
from (select cd, de from (select cd, replace(replace(de,'''',''''''),' ','_') 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 order by cd
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'
,idn.del, idn.dts
from idn
where del = 0
'
--print @DynaQuery --NOTE: Print truncates at NVARCHAR(4000) characters
exec (@DynaQuery)
insert CTD (TC, NM, NLC, PKF, PT, PF, CF, OM, OB, VW)
select 'PGMS','Program and Authorization Status','Student Data','ID','STU','ID','ID','One','ID','1'
insert ctf (TC, FC, SQ, NM, TY, DL)
select 'PGMS' TC, COLUMN_NAME FC, row_number() over (order by ordinal_position ) * 2 -1 SQ,
case when COLUMN_NAME = 'ID' then 'PermID'
when COLUMN_NAME = 'NSL' then 'NSLP_Code'
when COLUMN_NAME = 'SPD' then 'Special_Ed'
when left(COLUMN_NAME, 4) in ('PGM_','AUT_') then
(select top 1 de from
(select de from USYSGCOD where tc = left(COLUMN_NAME, 3) and fc = 'cd' and cd = substring(COLUMN_NAME,charindex('_',COLUMN_NAME) + 1,255) and del = 0
union
select de from COD where tc = left(COLUMN_NAME, 3) and fc = 'cd' and cd = substring(COLUMN_NAME,charindex('_',COLUMN_NAME) + 1,255) and del = 0) codes)
else '' end NM,
case
when right(DATA_TYPE, 3) like 'INT' then 'Number'
when right(DATA_TYPE, 4) like 'CHAR' then 'Text'
else '' end TY,
'600' DL
from INFORMATION_SCHEMA.columns
where TABLE_NAME = 'pgms_descrip'
and COLUMN_NAME not in ('id','del','dts')
insert CTF (TC, FC, SQ)
select top (select count(*) - 1 from ctf where tc = 'PGMS')
'PGMS' TC, FC = 'NEWLINE' + cast(row_number() over (order by sq) as varchar(4)),
sq + 1 SQ
from ctf where tc = 'PGMS'
drop view PGMS_Descrip
set @DynaQuery = N''
select @DynaQuery += N'
create view PGMS as
select ID,
isnull(
(select top 1 cd
from fre
where id = idn.id and esd <= convert(varchar(10),current_timestamp,101) and (eed is null or eed >= convert(varchar(10),current_timestamp,101))
order by esd desc, sq desc)
,'''' ) as NSL,
isnull((select ''Y'' from cse where id = idn.id and di <> '''' and xd is null and del = 0),'''') SPD,
'
select @DynaQuery += N'
case
when id in (select pid from pgm where cd = ''' + CD + ''' and (psd <= convert(varchar(10),current_timestamp,101) and (ped >= convert(varchar(10),current_timestamp,101) or ped is null)) and pid = idn.id and del = 0) then ''Yes''
when id in (select pid from pgm where cd = ''' + CD + ''' and (esd <= convert(varchar(10),current_timestamp,101) and (eed >= convert(varchar(10),current_timestamp,101) or eed is null)) and pid = idn.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 = idn.id and del = 0) then ''Null Start''
else ''''
end as ''P' + cast(ROW_NUMBER() over (order by cd) as varchar(3)) + N''' --PGM code Header
,'
from (select cd, de from (select cd, replace(replace(de,'''',''''''),' ','_') as de from USYSGCOD where tc = 'pgm' and fc = 'cd' and del = 0
union
select cd, replace(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 (pgm.psd > CURRENT_TIMESTAMP - 400 or pgm.esd > CURRENT_TIMESTAMP - 400) --Only incude programs you still populate in a sensible timeframe.
and stu.tg = '' --remark this line out when evaluating historic programs no active students participate in
and pgm.cd <> '')
) PGMcodes order by cd
--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 <= convert(varchar(10),current_timestamp,101) and (sdt <= convert(varchar(10),current_timestamp,101) or sdt is null) and (eed >= convert(varchar(10),current_timestamp,101) or eed is null)) and pid = idn.id) and id not in (select pid from aut where cd = ''' + CD + ''' and st = ''2'' and (dt <= convert(varchar(10),current_timestamp,101) and (sdt <= convert(varchar(10),current_timestamp,101) or sdt is null) and (eed >= convert(varchar(10),current_timestamp,101) or eed is null)) and pid = idn.id) then ''Yes''
when id in (select pid from aut where cd = ''' + CD + ''' and st = ''1'' and (dt <= convert(varchar(10),current_timestamp,101) and (sdt <= convert(varchar(10),current_timestamp,101) or sdt is null) and (eed >= convert(varchar(10),current_timestamp,101) or eed is null)) and pid = idn.id) and id in (select pid from aut where cd = ''' + CD + ''' and st = ''2'' and (dt <= convert(varchar(10),current_timestamp,101) and (sdt <= convert(varchar(10),current_timestamp,101) or sdt is null) and (eed >= convert(varchar(10),current_timestamp,101) or eed is null)) and pid = idn.id) then ''Conflict''
when id not in (select pid from aut where cd = ''' + CD + ''' and st = ''1'' and (dt <= convert(varchar(10),current_timestamp,101) and (sdt <= convert(varchar(10),current_timestamp,101) or sdt is null) and (eed >= convert(varchar(10),current_timestamp,101) or eed is null)) and pid = idn.id) and id in (select pid from aut where cd = ''' + CD + ''' and st = ''2'' and (dt <= convert(varchar(10),current_timestamp,101) and (sdt <= convert(varchar(10),current_timestamp,101) or sdt is null) and (eed >= convert(varchar(10),current_timestamp,101) or eed is null)) and pid = idn.id) then ''No''
else ''''
end as [A' + cast(ROW_NUMBER() over (order by cd) as varchar(3)) + '] --AUT code header
,'
from (select cd, de from (select cd, replace(replace(de,'''',''''''),' ','_') 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 order by cd
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'
,idn.del, idn.dts
from idn
where del = 0
'
--print @DynaQuery --NOTE: Print truncates at NVARCHAR(4000) characters
exec (@DynaQuery)
update CTF
set FC = NEWFC
from ctf inner join (select sq,
case when fc like 'PGM_%' then 'P' + cast(ROW_NUMBER() over (order by sq) as varchar(3)) end NEWFC
from ctf
where tc = 'pgms' and fc like 'PGM_%') c
on ctf.sq = c.sq and ctf.tc = 'PGMS'
update CTF
set FC = NEWFC
from ctf inner join (select sq,
case when fc like 'AUT_%' then 'A' + cast(ROW_NUMBER() over (order by sq) as varchar(3)) end NEWFC
from ctf
where tc = 'pgms' and fc like 'AUT_%') c
on ctf.sq = c.sq and ctf.tc = 'PGMS'
print 'PGMS Custom View Created!'
END
else print 'PGMS object or definition already exists - nothing done!'
