On the Aeries Immunizations page prior to July 2023, only the Exemption - Date field was provided for exemptions, and some districts used that Date field to record the exemption expiration date. A new Exemption - Expiration Date field was added July 2023 to provide the following new functionality:

  • Highlight expired exemptions based on the new Exemption - Expiration Date field in red on the Immunizations page. 
  • List students with expired exemptions from the Student Immunization Status report.

As of that update, any date that was previously entered in the Exemption -Date field is now in the Exemption - Effective Date for all schools. Any schools that used the Date field to record exemption expiration dates must move those dates into the new Exemption - Expiration Date fields using a CHANGE or SQL query to see this new functionality.

 

Change Queries - Be sure to test in a sandbox first. 


Immunization

Copy Effective Date to Expiration Date

Set Effective Date to Null

PolioCHANGE IMM PXD TO PED IF PED # NULL  AND PXD = NULLCHANGE IMM PED TO NULL IF PED = PXD
DPT

CHANGE IMM DXD TO DED IF DED # NULL AND DXD = NULL

CHANGE IMM DED TO NULL IF DED = DXD
MMR

CHANGE IMM AXD TO AED IF AED # NULL  AND AXD = NULL

CHANGE IMM AED TO NULL IF AED = AXD 

Haemophilus Influenzae type b

CHANGE IMM YXD TO YED IF YED # NULL  AND YXD = NULL

CHANGE IMM YED TO NULL IF YED = YXD

Hepatitis B

CHANGE IMM IXD TO IED IF IED # NULL  AND IXD = NULL


CHANGE IMM IED TO NULL IF IED = IXD

Varicella

CHANGE IMM VXD TO VED IF VED # NULL  AND VXD = NULL

CHANGE IMM VED TO NULL IF VED = VXD

Hepatitis A

CHANGE IMM HAXD TO HAD IF HAD # NULL  AND HAXD = NULL

CHANGE IMM HAD TO NULL IF HAD = HAXD

Physical

CHANGE IMM HEXD TO HED IF HED # NULL  AND HEXD = NULL

CHANGE IMM HED TO NULL IF HED = HEXD

PVC

CHANGE IMM PCXD TO PCD IF PCD # NULL  AND PCXD = NULL

CHANGE IMM PCD TO NULL IF PCD = PCXD

MCV4

CHANGE IMM MCXD TO MCD IF MCD # NULL  AND MCXD = NULL

CHANGE IMM MCD TO NULL IF MCD = MCD

Tdap-7th Grade

CHANGE IMM TXD TO TED IF TED # NULL  AND TXD = NULL

CHANGE IMM TED TO NULL IF TED = TXD

COVID-19

CHANGE IMM CVXD TO CVD IF CVD # NULL  AND CVXD = NULL

CHANGE IMM CVD TO NULL IF CVD = CVXD

TB Skin Test

CHANGE IMM TBXD TO TBD IF TBD # NULL  AND TBXD = NULL

CHANGE IMM TBD TO NULL IF TBD = TBXD


SQL Query Be sure to test in a sandbox first. 

The SQL Query sets the Expiration Date to the Effective Date IF there is an Effective Date AND not already an Expiration Date entered.


UPDATE IMM
SET PXD = PED,
DXD = DED,
AXD = AED,
YXD = YED,
IXD = IED,
VXD = VED,
HAXD = HAD,
HEXD = HED,
PCXD = PCD,
MCXD = MCD,
TXD = TED,
CVXD = CVD,
TBXD = TBD
WHERE ((PED IS NOT NULL AND PXD IS NULL) OR
(DED IS NOT NULL AND DXD IS NULL) OR
(AED IS NOT NULL AND AXD IS NULL) OR
(YED IS NOT NULL AND YXD IS NULL) OR
(IED IS NOT NULL AND IXD IS NULL) OR
(VED IS NOT NULL AND VXD IS NULL) OR
(HAD IS NOT NULL AND HAXD IS NULL) OR
(HED IS NOT NULL AND HEXD IS NULL) OR
(PCD IS NOT NULL AND PCXD IS NULL) OR
(MCD IS NOT NULL AND MCXD IS NULL) OR
(TED IS NOT NULL AND TXD IS NULL) OR
(CVD IS NOT NULL AND CVXD IS NULL) OR
(TBXD IS NOT NULL AND TBD IS NULL))
           AND  DEL = 0

 

If the district needs to set the Effective Date to NULL after moving it to the Expiration Date:
Only run if ALL dates from Effective Dates were moved to the new Expiration Dates:

UPDATE IMM
SET PED = NULL,
DED = NULL,
AED = NULL,
YED = NULL,
IED = NULL,
VED = NULL,
HAD = NULL,
HED = NULL,
PCD = NULL,
MCD = NULL,
TED = NULL,
CVD = NULL,
TBD = NULL
WHERE CAST(PED AS DATE) = PXD AND
CAST(DED AS DATE) = DXD AND
CAST(AED AS DATE) = AXD AND
CAST(YED AS DATE) = YXD AND
CAST(IED AS DATE) = IXD AND
CAST(VED AS DATE) = VXD AND
CAST(HAD AS DATE) = HAXD AND
CAST(HED AS DATE) = HEXD AND
CAST(PCD AS DATE)= PCXD AND
CAST(MCD AS DATE) = MCXD AND
CAST(TED AS DATE) = TXD AND
CAST(CVD AS DATE) = CVXD AND
CAST(TBXD AS DATE) = TBD AND
DEL = 0