I was able to get the sql code behind the main system timeliness report, but I am unsure how I use it within crystal.
Peppers Report Line 3 SQL: Administrative Appeals
SELECT DISTINCT A.Handling_District, 3 AS PeppersLine,'
D.DispositionEntry_Date, D.Appeal_Number
FROM DISPOSITION D, APPEAL A, ASSISTANCEPROGRAM AP, APPEALDISPOCODE AD
WHERE ( (INSTR(AD.DESCRIPTION, ''WITHDRAW'') = 0) OR
((INSTR(AD.DESCRIPTION, ''WITHDRAW'') > 0)
AND (UPPER(D.COMPLIANCE) = ''Y'')) )
AND ( A.Appeal_Number = D.Appeal_Number )
AND ( AP.Assistance_Program = A.Assistance_Program )
AND ( UPPER(AP.Peppers_Exclude) <> ''Y'' )
AND ( AD.Disposition = D.Disposition )
AND ( AD.Decision_Required = ''Y'' )
AND ( D.Disposition_Date >= :p_BeginDate )
AND ( D.Disposition_Date <= :p_EndDate )
AND ( (D.Disposition_Date - A.Request_Date) > 30 )
AND ( RTRIM(D.Docket_Number) IS NOT NULL )
ParamByName('p _BeginDate').AsDate := FBeginDate
ParamByName('p_EndDate').AsDate := FEndDate
Peppers Report Line 4 SQL: Client Requested Postponements
SELECT DISTINCT A.Handling_District, 4 AS PeppersLine,
D.DispositionEntry_Date, D.Appeal_Number
FROM DISPOSITION D, APPEAL A, ASSISTANCEPROGRAM AP,
APPEALDISPOCODE AD, HEARINGAPPEAL HA
WHERE ( (INSTR(AD.DESCRIPTION, ''WITHDRAW'') = 0) OR'
((INSTR(AD.DESCRIPTION, ''WITHDRAW'') > 0)
AND (UPPER(D.COMPLIANCE) = ''Y'')) )
AND ( A.Appeal_Number = D.Appeal_Number )
AND ( (D.Disposition_Date - A.Request_Date) > 30 )
AND ( AP.Assistance_Program = A.Assistance_Program )
AND ( UPPER(AP.Peppers_Exclude) <> ''Y'' )
AND ( AD.Disposition = D.Disposition )
AND ( AD.Decision_Required = ''Y'' )
AND ( D.Disposition_Date >= :p_BeginDate )
AND ( D.Disposition_Date <= :p_EndDate )
AND ( RTRIM(D.Docket_Number) IS NULL )
AND (( HA.Appeal_Number = A.Appeal_Number)
AND ( UPPER(HA.Hearing_Type) = ''R'' ))
AND ( ( (AP.GroupID <> ''4'') AND
((D.Disposition_Date - A.Request_Date) < 91) ) OR
(AP.GroupID = ''4'' ) AND
((D.Disposition_Date - A.Request_Date) < (61 + GETCREDIT(D.Appeal_Number))) ) )
ParamByName('p_BeginDate').AsDate := FBeginDate
ParamByName('p_EndDate').AsDate := FEndDate
Peppers Report Line 5 SQL: Medical Determinations
SELECT DISTINCT A.Handling_District, 5 AS PeppersLine,
D.DispositionEntry_Date, D.Appeal_Number
FROM DISPOSITION D, APPEAL A, ASSISTANCEPROGRAM AP, APPEALDISPOCODE AD
WHERE ( (INSTR(AD.DESCRIPTION, ''WITHDRAW'') = 0) OR
((INSTR(AD.DESCRIPTION, ''WITHDRAW'') > 0)'
AND (UPPER(D.COMPLIANCE) = ''Y'')) )
AND ( A.Appeal_Number = D.Appeal_Number )
AND ( (D.Disposition_Date - A.Request_Date) > 30 )
AND ( AP.Assistance_Program = A.Assistance_Program )
AND ( UPPER(AP.Peppers_Exclude) <> ''Y'' )
AND ( AD.Disposition = D.Disposition )
AND ( AD.Decision_Required = ''Y'' )
AND ( D.Disposition_Date >= :p_BeginDate )
AND ( D.Disposition_Date <= :p_EndDate )
AND ( RTRIM(D.Docket_Number) IS NULL )
AND ( NOT EXISTS')
(SELECT HA.APPEAL_NUMBER')
FROM HEARINGAPPEAL HA')
WHERE ( HA.Appeal_Number = A.Appeal_Number)
AND ( UPPER(HA.Hearing_Type) = ''R'' )))
AND ( UPPER(A.Peppers_Delay) = ''MD'' )
AND ( (D.Disposition_Date - A.Request_Date) < 91 )
ParamByName('p_BeginDate').AsDate := FBeginDate
ParamByName('p_EndDate').AsDate := FEndDate
Peppers Report Line 6 SQL: Other Acceptable Exemptions
SELECT DISTINCT A.Handling_District, 6 AS PeppersLine,
D.DispositionEntry_Date, D.Appeal_Number
FROM DISPOSITION D, APPEAL A, ASSISTANCEPROGRAM AP, APPEALDISPOCODE AD
WHERE ( (INSTR(AD.DESCRIPTION, ''WITHDRAW'') = 0) OR
((INSTR(AD.DESCRIPTION, ''WITHDRAW'') > 0)
AND (UPPER(D.COMPLIANCE) = ''Y'')) )
AND ( A.Appeal_Number = D.Appeal_Number )
AND ( (D.Disposition_Date - A.Request_Date) > 30 )
AND ( AP.Assistance_Program = A.Assistance_Program )
AND ( UPPER(AP.Peppers_Exclude) <> ''Y'' )
AND ( AD.Disposition = D.Disposition )
AND ( AD.Decision_Required = ''Y'' )
AND ( D.Disposition_Date >= :p_BeginDate )
AND ( D.Disposition_Date <= :p_EndDate )
AND ( RTRIM(D.Docket_Number) IS NULL )
AND ( NOT EXISTS
(SELECT HA.APPEAL_NUMBER
FROM HEARINGAPPEAL HA
WHERE ( HA.Appeal_Number = A.Appeal_Number)
AND ( UPPER(HA.Hearing_Type) = ''R'' )))
AND ( UPPER(A.Peppers_Delay) <> ''MD'' )
AND ( RTRIM(A.Peppers_Delay) IS NOT NULL )
AND ('
( (AP.GroupID <> ''4'') AND
((D.Disposition_Date - A.Request_Date) < 91) ) OR
( (AP.GroupID = ''4'')
AND ((D.Disposition_Date - A.Request_Date) < 61) )
ParamByName('p_BeginDate').AsDate := FBeginDate
ParamByName('p_EndDate').AsDate := FEndDate
The Total Exemptions figure is these 4 SQL statements’ totals added together.