Awards in multiple SP files should not be reported in most recent file.
Something like this Research department SQL will tell you cross grad_date:
| Code Block | ||
|---|---|---|
| ||
SELECT "studentgraduates"."pidm",
"studentgraduates"."program",
"studentgraduates"."degree_code",
"studentgraduates"."status",
"studentgraduates"."grad_date"
FROM "StudentNet"."dbo"."studentgraduates" "StudentGraduates"
WHERE ( "studentgraduates"."grad_date" >= {ts '2020-07-01 00:00:00'}
AND "studentgraduates"."grad_date" < {ts '2021-06-30 00:00:01'} )
AND ( "studentgraduates"."status" = 'AW'
OR "studentgraduates"."status" = 'CM'
OR "studentgraduates"."status" = 'CT'
OR "studentgraduates"."status" = 'SC'
OR "studentgraduates"."status" = 'SK' ) |
This SQL can be ran against the MIS_Data/Student_Program_Award, database/table to find records that exist in both selected GI03_MIS_TERM codes:
| Code Block | ||
|---|---|---|
| ||
select *
from [Student_Program_Award] a
where a.GI03_MIS_TERM =210 and
exists( select pidm from [Student_Program_Award] b where b.GI03_MIS_TERM =200 and a.sp01_program_id=b.sp01_program_id and a.SP02_PROGRAM_AWARD=b.SP02_PROGRAM_AWARD
and a.SP03_PROGR_AWARD_EARN=b.SP03_PROGR_AWARD_EARN and a.SP04_PROGRAM_CO_UNIQUE_CODE = b.SP04_PROGRAM_CO_UNIQUE_CODE) |
Asked the CO to implement this error checking. Case: CS0003748 submitted .
...
| title | Historical Decisions No Longer In Effect |
|---|
...
Backdating Awards
| Expand | ||
|---|---|---|
| ||
From: CCC MISEDIT <cccmisedit@CCCCO.edu>
TJ, See below From: TJ Baugus <tjb@cos.edu>
[External Email: Do not click any links or open attachments if you do not trust the sender and know the content is safe]
Hello,
We have students that forget to apply for graduation within the term they complete the award’s work. Some may apply several years later.
Should we include in the latest SP file (18-19) records with SP03 STUDENT-PROGRAM-AWARD-EARNED dates that are older than the 18-19 fiscal year? Yes, go ahead and report when the awards were earned.
This is the MIS DED SP domain. It uses the word “awarded”. Because of the application date or delay, the ‘awarding’ did happen in 18-19 even if the completion of the degree work was in a prior year. “The following record layout indicates the format in which the districts report information on student program awards awarded during the prior fiscal year."
TJ IMPORTANT NOTICE: This e-mail message is intended to be received only by persons entitled to receive the confidential information it may contain. E-mail messages to clients of the College of the Sequoias may contain information that is confidential and legally privileged. Please do not read, copy, forward, or store this message unless you are an intended recipient of it. If you have received this message in error, please forward it to the sender and delete it completely from your computer system. |
Historical Decision No Longer In Effect
Code snippet for the customization we used to have in place to capture situation explained to CO. Introduced duplicate records over annual submission cycles.
| Code Block |
|---|
SHRDGMR.SHRDGMR_GRAD_DATE BETWEEN :START_DATE AND :END_DATE
or SHRDGMR_appl_DATE BETWEEN :START_DATE AND :END_DATE and shrdgmr_grad_date < :START_DATE |
Example:
SHRDGMR_GRAD_DAT
E = 06/25/2021
SHRDGMR_appl_DATE = 7/23/2021
| Code Block |
|---|
SHRDGMR.SHRDGMR_GRAD_DATE BETWEEN :START_DATE AND :END_DATE
or SHRDGMR_appl_DATE BETWEEN :START_DATE AND :END_DATE and shrdgmr_grad_date < :START_DATE |
210:
Grad Start Date = 7/1/2020
Grad End Date = 6/30/2021
220
Start Date = 7/1/2021
End Date = 6/30/2022
| Expand | ||
|---|---|---|
| ||
Sorry to weigh you down with emails. Attached is a sheet I just assembled that points towards 47 student who received an award in 2019/20 and had an apply date in 2020/21. I’m pretty sure these are the students that make up the difference in our counts. As you can see, the majority are a specific program that was processed in a certain way, probably in a batch format?
Lets not grossly over-report our FIRE award 😊
Ryan From: TJ Baugus
Thanks for summarizing this.
Now that we’ve realized the issue, our first priority is still finishing the 202020 term MIS. (It’s not moving fast enough.) Then we’ll look at this one a bit more deeply.
From: Ryan Barry-Souza <ryanbs@cos.edu>
Thank you again for your investigation into this matter. I’d like to recap the meeting for sake of documentation.
We should reconvene this conversation in mid-August to ensure we’re all on the same page regarding the MIS submission due this Fall. It should be noted that AR staff in conjunction with IR staff have made several passes at the 19/20 data in search of data entry errors. So far, there have been no errors identified!
Ryan
From: TJ Baugus <tjb@cos.edu>
Jordan, Ludmila, & I are still busy with the spring MIS submissions. We’ll talk about this in the Thursday meeting.
From: Ryan Barry-Souza <ryanbs@cos.edu>
With MIS submissions upon us, I wanted to reinforce my opinion that we need to investigate the way in which the MIS award data is generated. Lee had it perfect in the Fall but things went a bit weird during the resubmissions. I urge you to not dismiss this as an entry error issue – that office is doing a fantastic job of working through their processes and communicating with TS and IR regarding their data entries.
Can someone please investigate these students and explain why they’d be in more than one MIS submission for their given award? I believe these students were sent up in both the 2017-18 and 2018-19 MIS files.
Students ["@00000669", "@00107424", "@00180566", "@00186254", "@00344725", "@00347941", "@00355853"] all have graduation dates associated with the 2017-18 while their apply date is post 6.30.2018. When I looked at the MIS list in Argos, it appears these students were already reported in the 2017-18 year! In my opinion, using the apply date will result in the District over-reporting awards if they are also included in the 2018-19 submission. There is one student I came across that was an oddity "@00021291", as this student graduated 20 years ago?!?!
From: Ryan Barry-Souza
You’re likely to find this error to be reoccurring. I’ve identified it before and confirmed with your staff. The same awards are being submitted in duplicate years because of the apply date.
TJ, we all(including yourself) agreed to this methodology in a joint meeting with student services, computer services and research office. A/R staff explicitly stated not to use the apply date for allocating awards to a timeframe. I’m really at a loss that we brought all those people together for an agreement yet nobody consulted these staff or made any conversation when you decided to change it.
From: TJ Baugus <tjb@cos.edu>
We never took time to see if the policy was accurately applied to 1718 SP or if 1718 needed to be resubmitted. Probably it does. It definitely is suspect for the same manual-editing & other problems that caused all the chaos last year. I recall a July 2019 meeting on the award MIS topic, & a person that was in attendance made statements that were concerning to me. However, I never could get a straight answer from that person.
The use of application date was a long-standing policy. There may have been a blip of confusion related to one person in the past year-plus.
Lee will look at these examples by week’s end.
From: Ryan Barry-Souza <ryanbs@cos.edu>
Before we settle up for a meeting, I’d really like to continue this conversation while we have access to data.
I pulled Lee’s awards and worked some magic to identify several students that are included in the 3186 count but not included in the 3177 count. Here’s what I’ve discovered:
Students ["@00000669", "@00107424", "@00180566", "@00186254", "@00344725", "@00347941", "@00355853"] all have graduation dates associated with the 2017-18 while their apply date is post 6.30.2018. When I looked at the MIS list in Argos, it appears these students were already reported in the 2017-18 year! In my opinion, using the apply date will result in the District over-reporting awards if they are also included in the 2018-19 submission. There is one student I came across that was an oddity "@00021291", as this student graduated 20 years ago?!?!
Additionally, I made a phone call to Fresno State and spoke with their registrar’s office. They confirmed to me that all CSU and UC institutions will only identify the term in which an award was applied for and conferred. Simply stated, they do not back date. Ever. She also said Fresno Pacific is the same.
Its been a long day everyone. Thanks for putting up with me.
From: TJ Baugus <tjb@cos.edu>
Jordan, can you meet with TS so we can understand what exactly your suggesting it asking?
Get Outlook for iOS From: TJ Baugus <tjb@cos.edu>
I’ve asked Jordan about meetings with us (SIG) & if we should wait until the 3/24 SIG or not. Emailing people not on this list & not including all of us is adding to the confusion, especially since not all of the end-user population has been turned into MIS experts.
Related to Justine’s comments: Because the student didn’t apply when they should have & because we are updating it with “the day we enter it”, it will do exactly what we want it to do à report the record in the next cycle, within the next year’s SP. Justine probably doesn’t know MIS requirements & therefore, probably doesn’t know this is allowed. Students that apply & graduate within the same year are fine. Using the Application date in the query is only to pick up late students that forgot to apply, & it works because of our policy to use today’s date. No red flag. Again, what we are doing was verified just days ago with the MIS department of the CCCCO – the masters of everything MIS.
Related to Joanne (not within this chain, record entered March 2020 with a back-dated application date): If she would have used “the day we enter it”, it would have worked – the record would be in the next SP. By back dating the appl date, it causes a record that will never report & that is a mis-match to what was submitted for SP 1819. This record can be corrected by fixing it’s application date.
From: Ryan Barry-Souza <ryanbs@cos.edu>
FYI – this is the feedback I received from Justine.
From: Justine Kincade <justinek@cos.edu>
Huge red flag with apply date. That is for internal purposes of the day we enter it. It’s not going to be accurate for reporting purposes, in my opinion. Example, we get a late grad app after may for a spring application. We would put in the day we get it (let’s say June) but they would be graduated in May. Same for if we are entering information early. A student graduates this spring but we have their application date as December of last year because that’s when they applied.
I think it is best to use either graduation term or graduation date. The date might be better for your report since it is based off specific dates and not terms since summer can throw things off.
Justine Kincade, M.S. Resource Specialist: Degree Audit & Evaluation College of the Sequoias Admissions & Records Office (559)737-6129
From: Ryan Barry-Souza <ryanbs@cos.edu>
Any red flags here? Now tech services is saying we should also consider the apply date?
From: TJ Baugus <tjb@cos.edu>
The CCCCO MIS department confirmed this month that the selection criteria is correct in using the “application date” to pick up people that forgot to apply for graduation in a prior year. Additionally, we have discussed this via email with Ellucian & will be submitting an IDEA (enhancement request) to Ellucian to have Banner’s baseline report modified.
From: Ryan Barry-Souza <ryanbs@cos.edu>
Thanks for the clarification. I have some follow-up questions.
Why do you generate 3187 records but the Argos report counts 3174, which is the original number that was uploaded back in the Fall? Also, I don’t understand why the query is using anything other than graduation date? When I look at MIS data dictionary, there’s no mention of an application date, just graduation dates.
From: Lee McDonald <leem@cos.edu>
Here is how Banner is selecting the records to include:
(SHRDGMR_GRAD_DATE BETWEEN '1-JUL-2018' AND '30-JUN-2019' or (shrdgmr_appl_date BETWEEN '1-JUL-2018' AND '30-JUN-2019' and shrdgmr_term_code_grad < '201910')) AND SHRDGMR_DEGS_CODE IN ('AW','CM','CT','SC','SK')
This currently selects 3,187 records
Since the criteria includes the application date, I suspect you will not be able to exactly match the MIS submission using the Student Graduates view.
From: Ryan Barry-Souza <ryanbs@cos.edu>
Can we please talk this one out? I believe that A/R staff is doing exactly what we asked of them – which is to be aware and organized of the June 30th deadline and how it pertains to MIS reporting. That said, we were all under the impression that the graduation date and award status were the only things driving the MIS query.
With regard to the term issue below, it is quite possible there are some inconsistencies but that’s largely due to the nature of the summer term splitting the reporting timeline. Some students who were conferred an award during 201930 term did so in the reporting period while others were conferred their award after 6.30.19.
Can you please confirm the exact nature of the awards query used for MIS submission? I was able to match in the fall term…. We can’t be that far apart. Ryan
From: Lee McDonald <leem@cos.edu>
Jose and I ran a couple of queries: Grad date NOT in 2018-19 but Term in 2018-19 – 193 records
Grad date in 2018-19 but Term not in 2018-19 – 2 records
Looks like there is still a data entry issue.
Looking at 2019-20 year to date Grad date NOT in 2019-20 but Term in 2019-20 – 43 records All have a grad date of June 28 (end of Summer Session 1 instead of a grad date at the end of the Summer Term)
Grad date in 2019-20 but Term not in 2019-20 – 2 records
From: Ryan Barry-Souza <ryanbs@cos.edu>
Thanks for the reply Lee. I’m unable to match the counts for the resubmission. My query is as follows:
SELECT "vm_stugraduates"."grad_date",
From: Lee McDonald <leem@cos.edu>
The original SP submission on August was 3174 awards The Nov 26 resubmission was 3186 records, an increase of twelve awards.
When we did the original 2018-19 Program Awards submission, we had to remove records because students had been missed the in the original term submissions. Once we completed the resubmission of the term data, we resubmitted the annual Awards file to include these additional awards.
Let me know if you have any questions.
From: Ryan Barry-Souza <ryanbs@cos.edu>
Yes.
From: TJ Baugus <tjb@cos.edu>
We are at a conference & return to the office Thursday. Can it wait until Thursday?
Get Outlook for iOS From: Ryan Barry-Souza <ryanbs@cos.edu>
I missed something that happened with the MIS resubmits.
How did the awards for the 2018-19 year increase compared to what Lee submitted in fall term? I thought we all agreed that we’d adhere to a methodology of using the graduation date for the time period July 1 – June 30th? The number should be 3176 per the data that currently exists in our system?
Please advise ASAP. |
| Expand | ||
|---|---|---|
| ||
From: CCC MISEDIT <cccmisedit@CCCCO.edu>
TJ, See below From: TJ Baugus <tjb@cos.edu>
[External Email: Do not click any links or open attachments if you do not trust the sender and know the content is safe]
Hello,
We have students that forget to apply for graduation within the term they complete the award’s work. Some may apply several years later.
Should we include in the latest SP file (18-19) records with SP03 STUDENT-PROGRAM-AWARD-EARNED dates that are older than the 18-19 fiscal year? Yes, go ahead and report when the awards were earned.
This is the MIS DED SP domain. It uses the word “awarded”. Because of the application date or delay, the ‘awarding’ did happen in 18-19 even if the completion of the degree work was in a prior year. “The following record layout indicates the format in which the districts report information on student program awards awarded during the prior fiscal year."
TJ IMPORTANT NOTICE: This e-mail message is intended to be received only by persons entitled to receive the confidential information it may contain. E-mail messages to clients of the College of the Sequoias may contain information that is confidential and legally privileged. Please do not read, copy, forward, or store this message unless you are an intended recipient of it. If you have received this message in error, please forward it to the sender and delete it completely from your computer system. |
Example:
SHRDGMR_GRAD_DAT
E = 06/25/2021
SHRDGMR_appl_DATE = 7/23/2021
| Code Block |
|---|
SHRDGMR.SHRDGMR_GRAD_DATE BETWEEN :START_DATE AND :END_DATE
or SHRDGMR_appl_DATE BETWEEN :START_DATE AND :END_DATE and shrdgmr_grad_date < :START_DATE |
210:
Grad Start Date = 7/1/2020
Grad End Date = 6/30/2021
220
Start Date = 7/1/2021
End Date = 6/30/2022
...
Awards in multiple SP files should not be reported in most recent file.
Something like this Research department SQL will tell you cross grad_date:
| Code Block | ||
|---|---|---|
| ||
SELECT "studentgraduates"."pidm",
"studentgraduates"."program",
"studentgraduates"."degree_code",
"studentgraduates"."status",
"studentgraduates"."grad_date"
FROM "StudentNet"."dbo"."studentgraduates" "StudentGraduates"
WHERE ( "studentgraduates"."grad_date" >= {ts '2020-07-01 00:00:00'}
AND "studentgraduates"."grad_date" < {ts '2021-06-30 00:00:01'} )
AND ( "studentgraduates"."status" = 'AW'
OR "studentgraduates"."status" = 'CM'
OR "studentgraduates"."status" = 'CT'
OR "studentgraduates"."status" = 'SC'
OR "studentgraduates"."status" = 'SK' ) |
This SQL can be ran against the MIS_Data/Student_Program_Award, database/table to find records that exist in both selected GI03_MIS_TERM codes:
| Code Block | ||
|---|---|---|
| ||
select *
from [Student_Program_Award] a
where a.GI03_MIS_TERM =210 and
exists( select pidm from [Student_Program_Award] b where b.GI03_MIS_TERM =200 and a.sp01_program_id=b.sp01_program_id and a.SP02_PROGRAM_AWARD=b.SP02_PROGRAM_AWARD
and a.SP03_PROGR_AWARD_EARN=b.SP03_PROGR_AWARD_EARN and a.SP04_PROGRAM_CO_UNIQUE_CODE = b.SP04_PROGRAM_CO_UNIQUE_CODE) |
Asked the CO to implement this error checking. Case: CS0003748 submitted .