Hello,
I'm reaching out for assistance with a query I use to determine which students to pick up for packaging. The query is not functioning as expected for the 2025–2026 year.
Specifically:
- I'm trying to exclude students with certain comment codes.
- Some students who should appear in the results are missing.
- Students with comment code 257, which should be excluded, are still being picked up.
This query worked correctly for the 2024–2025 year, so I'm wondering if there have been any changes for 2025–2026 that might be affecting the results.
Any help in identifying what might be going wrong would be greatly appreciated.
here is the query
SELECT DISTINCT A.EMPLID, I.NAME, A.INSTITUTION, A.AID_YEAR, (CONVERT(CHAR(10),A.EFFDT,121)), A.PELL_ELIGIBILITY, A.SAR_C_FLAG, A.VERF_SELECTION_IND, A.EFC_STATUS, B.PRIMARY_EFC, E.ACAD_CAREER, G.EXP_GRAD_TERM, H.RESIDENCY, E.PROCESSING_STATUS, E.DL_HEAL_LN_SW, (CONVERT(CHAR(10),SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10),121))
FROM PS_ISIR_CONTROL A, PS_ZZ_INST_SCRTY A1, PS_ISIR_COMPUTED B, PS_ZZ_INST_SCRTY B1, PS_ISIR_COMMENTS C, PS_ZZ_INST_SCRTY C1, PS_STDNT_AID_ATRBT E, PS_SCCPU_STDNT_BND D, PS_SFA_BPKG_BIND F, PS_ZZ_INST_SCRTY F1, PS_STDNT_FA_TERM G, PS_ZZ_INST_SCRTY G1, PS_STDNT_TERM_BDGT H, PS_ZZ_INST_SCRTY H1, PS_SCC_PRI_NAME_VW I
WHERE ( A.INSTITUTION = A1.INSTITUTION
AND A1.OPRID = 'ADAI10'
AND B.INSTITUTION = B1.INSTITUTION
AND B1.OPRID = 'ADAI10'
AND C.INSTITUTION = C1.INSTITUTION
AND C1.OPRID = 'ADAI10'
AND F.INSTITUTION = F1.INSTITUTION
AND F1.OPRID = 'ADAI10'
AND G.INSTITUTION = G1.INSTITUTION
AND G1.OPRID = 'ADAI10'
AND H.INSTITUTION = H1.INSTITUTION
AND H1.OPRID = 'ADAI10'
AND ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_ISIR_CONTROL A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.INSTITUTION = A_ED.INSTITUTION
AND A.AID_YEAR = A_ED.AID_YEAR
AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ) FROM PS_ISIR_CONTROL A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.INSTITUTION = A_ES.INSTITUTION
AND A.AID_YEAR = A_ES.AID_YEAR
AND A.EFFDT = A_ES.EFFDT)
AND A.EMPLID = B.EMPLID
AND A.INSTITUTION = B.INSTITUTION
AND A.AID_YEAR = B.AID_YEAR
AND A.EFFSEQ = B.EFFSEQ
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_ISIR_COMPUTED B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.INSTITUTION = B_ED.INSTITUTION
AND B.AID_YEAR = B_ED.AID_YEAR
AND B_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND B.EFFSEQ =
(SELECT MAX(B_ES.EFFSEQ) FROM PS_ISIR_COMPUTED B_ES
WHERE B.EMPLID = B_ES.EMPLID
AND B.INSTITUTION = B_ES.INSTITUTION
AND B.AID_YEAR = B_ES.AID_YEAR
AND B.EFFDT = B_ES.EFFDT)
AND A.EMPLID = C.EMPLID
AND A.INSTITUTION = C.INSTITUTION
AND A.AID_YEAR = C.AID_YEAR
AND A.EFFSEQ = C.EFFSEQ
AND C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PS_ISIR_COMMENTS C_ED
WHERE C.EMPLID = C_ED.EMPLID
AND C.INSTITUTION = C_ED.INSTITUTION
AND C.AID_YEAR = C_ED.AID_YEAR
AND C_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND C.EFFSEQ =
(SELECT MAX(C_ES.EFFSEQ) FROM PS_ISIR_COMMENTS C_ES
WHERE C.EMPLID = C_ES.EMPLID
AND C.INSTITUTION = C_ES.INSTITUTION
AND C.AID_YEAR = C_ES.AID_YEAR
AND C.EFFDT = C_ES.EFFDT)
AND A.INSTITUTION = 'LSUHS'
AND A.AID_YEAR = :1
AND A.SAR_C_FLAG <> 'Y'
AND A.VERF_SELECTION_IND = 'N'
AND C.COMMENT_CODE NOT IN ('018','276','277','159','160','017','019','020','022','155','289','293','294','326','163','165','216','217','253','255','256','258','260','164','261','233','234','235','236','237','238','251','252','006','007','008','009','013','115','116','117','118','141','054','218','219','023','024','026','027','028','029','030','031','032','033','034','035','036','037','038','039','041','042','043','044','045','046','100','109','110','111','112','113','119','120','121','122','123','124','125','126','127','128','129','130','131','132','133','134','264','265','055','056','057','084','085','086','087','088','089','090','091','092','093','094','095','096','097','098','099','101','102','310','311','062','063','064','065','066','067','070','071','072','073','074','077','078','079','080','081','082','083','103','104','105','106','107','108','114','295','296','301','049','003','025','325','5','10','14','40','47','50','52','59','60','61','69','75','76','142','158','162','221','222','257','263','278','303','327')
AND A.EMPLID = E.EMPLID
AND A.INSTITUTION = E.INSTITUTION
AND A.AID_YEAR = E.AID_YEAR
AND E.ACAD_CAREER = :5
AND A.EFC_STATUS = 'O'
AND E.EMPLID = D.EMPLID
AND E.EMPLID = F.EMPLID
AND E.INSTITUTION = F.INSTITUTION
AND E.AID_YEAR = F.AID_YEAR
AND F.ACAD_CAREER = E.ACAD_CAREER
AND E.EMPLID = G.EMPLID
AND E.INSTITUTION = G.INSTITUTION
AND E.AID_YEAR = G.AID_YEAR
AND G.EFFDT =
(SELECT MAX(G_ED.EFFDT) FROM PS_STDNT_FA_TERM G_ED
WHERE G.EMPLID = G_ED.EMPLID
AND G.INSTITUTION = G_ED.INSTITUTION
AND G.STRM = G_ED.STRM
AND G_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND G.EFFSEQ =
(SELECT MAX(G_ES.EFFSEQ) FROM PS_STDNT_FA_TERM G_ES
WHERE G.EMPLID = G_ES.EMPLID
AND G.INSTITUTION = G_ES.INSTITUTION
AND G.STRM = G_ES.STRM
AND G.EFFDT = G_ES.EFFDT)
AND G.STRM IN ('1257','1261','1264','1259','1256')
AND G.EXP_GRAD_TERM <> :6
AND G.EMPLID = H.EMPLID
AND G.INSTITUTION = H.INSTITUTION
AND G.STRM = H.STRM
AND G.EFFSEQ = H.EFFSEQ
AND H.AID_YEAR = G.AID_YEAR
AND H.ACAD_CAREER = G.ACAD_CAREER
AND H.EFFDT =
(SELECT MAX(H_ED.EFFDT) FROM PS_STDNT_TERM_BDGT H_ED
WHERE H.EMPLID = H_ED.EMPLID
AND H.INSTITUTION = H_ED.INSTITUTION
AND H.AID_YEAR = H_ED.AID_YEAR
AND H.ACAD_CAREER = H_ED.ACAD_CAREER
AND H.STRM = H_ED.STRM
AND H_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND H.EFFSEQ =
(SELECT MAX(H_ES.EFFSEQ) FROM PS_STDNT_TERM_BDGT H_ES
WHERE H.EMPLID = H_ES.EMPLID
AND H.INSTITUTION = H_ES.INSTITUTION
AND H.AID_YEAR = H_ES.AID_YEAR
AND H.ACAD_CAREER = H_ES.ACAD_CAREER
AND H.STRM = H_ES.STRM
AND H.EFFDT = H_ES.EFFDT)
AND E.PROCESSING_STATUS IN ('1','3')
AND E.EMPLID = I.EMPLID
AND I.EFFDT =
(SELECT MAX(I_ED.EFFDT) FROM PS_SCC_PRI_NAME_VW I_ED
WHERE I.EMPLID = I_ED.EMPLID
AND I_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10)) ))
ORDER BY 1
------------------------------
Anna Daigle
Director of Student Financial Aid
LSU Health Sciences Center
------------------------------
Message from the HEUG Marketplace:------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the
HEUG Marketplace.
------------------------------