ASRs: IDMS_ASR_LOG with DeferredStatus == Ordered and CreateDate in between now and start of the year before today (01/01/2021).
JCVHCs: IDMS_JC_VehicleLine with CreateDateTime in between now and start of the year before (01/01/2021), grouped by the JCCode.
- ASRsCurrent: from the ASRs above we keep the entries with CreateDate in the selected time frame (YTD, QTD, MTD).
- ASRsPrevious: from the ASRs above we keep the entries with CreateDate in the time frame (PYTD, PQTD, PMTD).
- JCVHCsCurrent: from the JCVHCs above we keep the entries with CreateDate in the selected time frame (YTD, QTD, MTD).
- JCVHCsPrevious: from the JCVHCs above we keep the entries with CreateDate in the time frame (PYTD, PQTD, PMTD).
Calculations
- CurrentRate: If ASRsCurrent count > 0 and JCVHCsCurrent count > 0 then ASRsCurrent count / JCVHCsCurrent count.
- PreviousRate: If ASRsPrevious count > 0 and JCVHCsPrevious count > 0 then ASRsPrevious count / JCVHCsPrevious
- PercentageChange: If the PreviousRate is > 0 then (CurrentRate – PreviousRate) / PreviousRate.
Add Comment