**APPENDIX D-7**

Aggregate Lead and Copper 90^th^ Percentile Data

This Page is intentionally left blank

**D-7. Aggregate Lead and Copper 90^th^ Percentile Data (Section
3.6.8)**

Check-Point Restart Issues: This process will be driven by changes to
the SSMPA table triggered by New or Changed Lead (1030) and Copper
(1022) SARs.

Calculate Aggregates for New or Changed Lead (1030) and Copper (1022)
Copper SARs.

> The driver for this subprocess is the SSMPA table.
>
> The record key for this subprocess is the composite of CDS trigger
> timestamp and the TMNSASCH_IS_NUMBER and TMNMPRD_IS_NUMBER columns.
>
> The sort order for this subprocess will be CDS trigger timestamp
> ascending and TMNMPRD_IS_NUMBER ascending.

Use these detailed specifications to determine whether and how to
calculate lead 90^th^ percentile values for those Sample Schedule
Monitoring Period Assignments that are related to Lead Tap Water Sample
Schedules.

1\. Create a log in the CDS Execution Log indicating the process start
time.

2\. Read each SSMPA where

a.  its CDS trigger timestamp is greater than the last run date & time
    for the *CDS Setup* process (note that the CDS trigger timestamp for
    an SSMPA record gets updated when a new result is added or an
    existing result is modified) AND

b.  SSMPA is associated to a Sample Schedule where:

    i.  the Analyte Code referenced by the referenced Monitoring
        Requirement is equal to "1030\" AND

    ii. the Sample Type Code for the referenced Monitoring
        Requirement\'s Violation Type is not equal to 03 or 56 or that
        the Monitoring Requirement does not reference a Violation Type
        and

```{=html}
<!-- -->
```
2.  For each unique SSMPA identified,

> Determine whether a lead 90^th^ percentile sample summary already
> exists for the same water system facility and monitoring period as
> referenced by the SSMPA.

If one is found,

> If USERID \<\> CDSSETUP (it is user-entered), do not calculate 90^th^
> percentile values B go on to next SSMPA.
>
> Else modify existing lead 90^th^ percentile sample summary.
>
> If one is not found, then create lead 90^th^ percentile sample
> summary.
>
> (*Developer Note: The detailed logic follows*.)

a.  Select WSF where it belongs to the Sample Schedule referenced in the
    current SSMPA. Select Monitoring Period referenced in the current
    SSMPA. If Sample Summary exists where:

    i.  Water System Facility referenced by the Sample Summary is equal
        to selected Water System Facility \[JSB/KF/SRP on 10/3/01\] AND

    ii. Monitoring Period referenced by the Sample Summary is equal to
        selected Monitoring Period AND

    iii. The Code for the Analyte referenced by the Sample Summary is
         equal to "PB90" AND

    iv. The TYPE_CODE for the Sample Summary Result referenced by the
        Sample Summary is equal to "90."

> When found
>
> If Sample Summary User ID is NOT equal to CDSSETUP,
>
> Then skip SSMPA and go to next SSMPA.

Else go to the "Lead 90^th^ Percentile Values Routine to modify."

When not found, go to the "Lead 90^th^ Percentile Values Routine to
add."

*Lead 90^th^ Percentile Values Routine*

28528. Read and temporarily store each result where:

       f.  result references the current SSMPA AND

       ```{=html}
       <!-- -->
       ```
       a.  analyte code referenced by the result is equal to 1030
           (lead).

```{=html}
<!-- -->
```
1.  Count the number of results and store as 90_Count (this will be the
    value for sample_summary_result count_qty).

2.  Determine the earliest Collection_End_Date of Samples for SARs and
    store it as Collection_Begin_Date for the Summary (this will be the
    value for sample_summary sample_collection_start_date).

3.  Determine the latest Collection_End_Date of Samples for SARs and
    store it as Collection_End_Date for the Sample Summary (this will be
    the value for sample_summary sample_collection_end_date).

4.  If all results analyzed by same Lab, store Lab IS Number as
    Lab_IS_Number and Lab_ST_Code as Lab_St_Code.

5.  Convert all results to milligrams per liter:

    b.  If UOM is UG/LCdivide concentration by 1,000.

    ```{=html}
    <!-- -->
    ```
    a.  If UOM is NG/LCdivide by 1,000,000.

    b.  If UOM is MG/LCleave as is.

    c.  If UOM is null and Less_Than_IND is equal to Y, no conversion
        needed, treat result as a zero.

    d.  If UOM is any other, the process will create an advisory
        referencing Message Number 15 and will record: the Water System
        No., name, tinwsys_is_number, and tinwsys_st_code; the water
        system facility State Asgn ID and name; the sampling point ID
        and description; the sample=s lab sample number and collection
        date; and the result=s analyte code.

    e.  Sort order the results (concentration) from least to greatest
        and sequentially number the sorted results starting with the
        lowest results (maximum 500).

    f.  Multiply the total number of lead results (90_Count) times 0.9
        and store product (called 90th_Sample here).

    g.  If 90th_Sample is an integer, locate numbered result that is
        equal to 90th_Sample and store it as the lead 90^th^ percentile
        value (90_Value).

    h.  If 90th_Sample is *not* an integer, store result of sample that
        is same as integer portion of 90th_Sample as 90_Lower and store
        result of sample that is 1+ the integer portion of 90th_Sample
        and call it 90_Upper; then calculate the difference between the
        two results (90_Upper minus 90_Lower); then multiply the
        difference times the decimal portion of 90th_Sample; then add
        this sum to 90_Lower and save as 90_Value. Round 90_Value to the
        same number of significant digits as the Lead Action Level.
        (A.D-7.a)

6.  The matrix below contains the values that will be used to either
    modify an existing Lead 90^th^ Percentile Summary or to create a new
    one.

-   To Modify B only values for fields in *italics* will be modified

-   To Add - all values in matrix will be used

+---------------------------+-------------------+----------------------+
| **Source of Data or       | **Destination**   |                      |
| Value**                   |                   |                      |
+===========================+===================+======================+
|                           | **Entity**        | **Attribute**        |
+---------------------------+-------------------+----------------------+
| Set to "Y"                | SAMPLE_SUMMARY    | COMPL_PURP_IND_CD    |
+---------------------------+-------------------+----------------------+
| Set to "CDSSETUP"         | SAMPLE_SUMMARY    | D_USERID_CODE        |
+---------------------------+-------------------+----------------------+
| IS_Number for TSAANLYT    | SAMPLE_SUMMARY    | TSAANLYT_IS_NUMBER   |
| whose Code = PB90         |                   |                      |
+---------------------------+-------------------+----------------------+
| ST_Code for TSAANLYT      | SAMPLE_SUMMARY    | TSAANLYT_ST_CODE     |
| whose Code = PB90         |                   |                      |
+---------------------------+-------------------+----------------------+
| Monitoring Period being   | SAMPLE_SUMMARY    | TMNMPRD_IS_NUMBER    |
| evaluated                 |                   |                      |
+---------------------------+-------------------+----------------------+
| Monitoring Period being   | SAMPLE_SUMMARY    | TMNMPRD_ST_CODE      |
| evaluated                 |                   |                      |
+---------------------------+-------------------+----------------------+
| Water System being        | SAMPLE_SUMMARY    | TINWSYS_IS_NUMBER    |
| evaluated                 |                   |                      |
+---------------------------+-------------------+----------------------+
| Water System being        | SAMPLE_SUMMARY    | TINWSYS_ST_CODE      |
| evaluated                 |                   |                      |
+---------------------------+-------------------+----------------------+
| Water System Facility     | SAMPLE_SUMMARY    | TINWSF_IS_NUMBER     |
| being evaluated           |                   |                      |
+---------------------------+-------------------+----------------------+
| Water System Facility     | SAMPLE_SUMMARY    | TINWSF_ST_CODE       |
| being evaluated           |                   |                      |
+---------------------------+-------------------+----------------------+
| *Collection_Begin_Date    | *SAMPLE_SUMMARY*  | *COLLECTION_STRT_DT* |
| variable*                 |                   |                      |
+---------------------------+-------------------+----------------------+
| *Collection_End_Date      | *SAMPLE_SUMMARY*  | *COLLECTION_END_DT*  |
| variable*                 |                   |                      |
+---------------------------+-------------------+----------------------+
| *Current date and time*   | *SAMPLE_SUMMARY*  | *D_LAST_UPDT_TS*     |
+---------------------------+-------------------+----------------------+
| *Lab_IS_Number variable*  | *SAMPLE_SUMMARY*  | *TSALAB_IS_NUMBER*   |
+---------------------------+-------------------+----------------------+
| *Lab_ST_Code variable*    | *SAMPLE_SUMMARY*  | *TSALAB_ST_CODE*     |
+---------------------------+-------------------+----------------------+
|                           |                   |                      |
+---------------------------+-------------------+----------------------+
| To reference record       | SAMP              | TSASMPSM_IS_NUMBER   |
| created in SAMPLE_SUMMARY | LE_SUMMARY_RESULT |                      |
+---------------------------+-------------------+----------------------+
| To reference record       | SAMP              | TSASMPSM_ST_CODE     |
| created in SAMPLE_SUMMARY | LE_SUMMARY_RESULT |                      |
+---------------------------+-------------------+----------------------+
| Set to IS_Number of SSMPA | SAMPLE_SUMMARY    | SSMPA_SASCH_IS_NO    |
| to which current summary  |                   |                      |
| is associated             |                   | SSMPA_MPRD_IS_NO     |
+---------------------------+-------------------+----------------------+
| Set to ST_Code of SSMPA   | SAMPLE_SUMMARY    | SSMPA_SASCH_ST_CO    |
| to which current summary  |                   |                      |
| is associated             |                   | SSMPA_MPRD_ST_CO     |
+---------------------------+-------------------+----------------------+
| Set to "90\"              | SAMP              | TYPE_CODE            |
|                           | LE_SUMMARY_RESULT |                      |
+---------------------------+-------------------+----------------------+
| Set to "CDSSETUP"         | SAMP              | D_USERID_CD          |
|                           | LE_SUMMARY_RESULT |                      |
+---------------------------+-------------------+----------------------+
| Set to "A"                | SAMP              | DATA_QUALITY_CODE    |
|                           | LE_SUMMARY_RESULT |                      |
+---------------------------+-------------------+----------------------+
| Set to "MG/L"             | SAMP              | UOM_CODE             |
|                           | LE_SUMMARY_RESULT |                      |
+---------------------------+-------------------+----------------------+
| 90_Count variable         | SAMP              | COUNT_QTY            |
|                           | LE_SUMMARY_RESULT |                      |
+---------------------------+-------------------+----------------------+
| 90_Value variable         | SAMP              | MEASURE              |
|                           | LE_SUMMARY_RESULT |                      |
+---------------------------+-------------------+----------------------+
| Current date and time     | SAMP              | D_LAST_UPDT_TS       |
|                           | LE_SUMMARY_RESULT |                      |
+---------------------------+-------------------+----------------------+

*End of ALead 90^th^ Percentile Values Routine*@

B. Use these detailed specifications to determine whether and how to
calculate copper 90^th^ percentile values for those Sample Schedule
Monitoring Period Assignments that are related to Copper Tap Water
Sample Schedules.

30076. Read each Sample SSMPA where:

       t.  its CDS trigger timestamp is greater than the last run date &
           time for the CDS Setup process (note that the CDS trigger
           timestamp for an SSMPA record gets updated when a new result
           is added or an existing result is modified) AND

       ```{=html}
       <!-- -->
       ```
       a.  SSMPA is associated to a Sample Schedule where:

           ?.  the Analyte Code referenced by the referenced Monitoring
               Requirement is equal to "1022\" AND

           ```{=html}
           <!-- -->
           ```
           i.  the Sample Type Code for the referenced Monitoring
               Requirement\'s Violation Type is not equal to 03 or 56 or
               that the Monitoring Requirement does not reference a
               Violation Type.

```{=html}
<!-- -->
```
7.  For each unique Sample Schedule Monitoring Period Assignment record
    identified,

    Determine whether a copper 90^th^ percentile sample summary already
    exists for the same water system facility and monitoring period as
    referenced by the SSMPA.

    If one is found,

    If USERID \<\> CDSSETUP (it is user-entered) do not calculate 90^th^
    percentile values B go on to the next SSMPA.

    Else modify existing copper 90^th^ percentile sample summary.

    If one is not found, then create copper 90^th^ percentile sample
    summary.

> (*Developer Note: The detailed logic follows.*)

l.  Select WSF where it belongs to the Sample Schedule referenced in the
    current SSMPA. Select Monitoring Period referenced in the current
    SSMPA. If Sample Summary exists where:

    i.  Water System Facility referenced by the Sample Summary is equal
        to selected Water System Facility AND

    ii. Monitoring Period referenced by the Sample Summary is equal to
        selected Monitoring Period AND

    iii. The Code for the Analyte referenced by the Sample Summary is
         equal to ACU90\" AND

    iv. The TYPE_CODE for the Sample Summary Result referenced by the
        Sample Summary is equal to "90."

> When found
>
> If Sample Summary User ID is NOT equal to CDSSETUP,
>
> Then skip SSMPA and go to next SSMPA.

Else go to the "Copper 90^th^ Percentile Values Routine to modify"

When not found, go to the "Copper 90^th^ Percentile Values Routine to
add"

*Copper 90^th^ Percentile Values Routine*

32656. Read and temporarily store each result where:

       z.  result references the current SSMPA AND

       ```{=html}
       <!-- -->
       ```
       a.  analyte code referenced by the result is equal to 1022
           (copper).

```{=html}
<!-- -->
```
8.  Count the number of results and store as 90_Count (this will be the
    value for sample_summary_result count_qty).

9.  Determine the earliest Collection_End_Date of Samples for SARs and
    store it as Collection_Begin_Date for the Summary (this will be the
    value for sample_summary sample_collection_start_date).

10. Determine the latest Collection_End_Date of Samples for SARs and
    store it as Collection_End_Date for the Sample Summary (this will be
    the value for sample_summary sample_collection_end_date).

11. If all results analyzed by same Lab, store Lab IS Number as
    Lab_IS_Number and Lab_ST_Code as Lab_St_Code.

12. Convert all results to milligrams per liter:

    a.  If UOM is UG/LCdivide concentration by 1,000.

    b.  If UOM is NG/LCdivide by 1,000,000.

    c.  If UOM is MG/LCleave as is.

    d.  If UOM is spaces and Sample Result Less Than Indicator is
        YCleave as is.

    e.  If UOM is any other, the process will create an advisory
        referencing Message Number 16 and will record: the Water System
        No., name, tinwsys_is_number, and tinwsys_st_code; the water
        system facility State Asgn ID and name; the Sampling Point ID
        and description; the sample=s lab sample number and collection
        date; and the result=s analyte code.

    f.  Sort order the results (concentration) from least to greatest
        and sequentially number the sorted results starting with the
        lowest results (maximum 500).

    g.  Multiply the total number of copper results (90_Count) times 0.9
        and store product (called A90th_Sample@ here).

    h.  If 90th_Sample is an integer, locate numbered result that is
        equal to 90th_Sample and store it as the copper 90^th^
        percentile value (90_Value).

    i.  If 90th_Sample is *not* an integer, store result of sample that
        is same as integer portion of 90th_Sample as 90_Lower and store
        result of sample that is 1+ the integer portion of 90th_Sample
        and call it 90_Upper, then calculate the difference between the
        two results (90_Upper minus 90_Lower), then multiply the
        difference times the decimal portion of 90th_Sample; then add
        this sum to 90_Lower and save as 90_Value. Round 90_Value to the
        same number of significant digits as the Copper Action Level.
        (A.D-7.b)

13. The matrix below contains the values that will be used to either
    modify an existing Copper 90^th^ Percentile Summary or to create a
    new one.

-   To Modify B only values for fields in *italics* will be modified

-   To Add - all values in matrix will be used

+---------------------------+-------------------+----------------------+
| **Source of Data or       | **Destination**   |                      |
| Value**                   |                   |                      |
+===========================+===================+======================+
|                           | **Entity**        | **Attribute**        |
+---------------------------+-------------------+----------------------+
| Set to "Y"                | SAMPLE_SUMMARY    | COMPL_PURP_IND_CD    |
+---------------------------+-------------------+----------------------+
| Set to "CDSSETUP"         | SAMPLE_SUMMARY    | D_USERID_CODE        |
+---------------------------+-------------------+----------------------+
| IS_Number for CU90        | SAMPLE_SUMMARY    | TSAANLYT_IS_NUMBER   |
+---------------------------+-------------------+----------------------+
| ST_Code for CU90          | SAMPLE_SUMMARY    | TSAANLYT_ST_CODE     |
+---------------------------+-------------------+----------------------+
| Monitoring Period being   | SAMPLE_SUMMARY    | TMNMPRD_IS_NUMBER    |
| evaluated                 |                   |                      |
+---------------------------+-------------------+----------------------+
| Monitoring Period being   | SAMPLE_SUMMARY    | TMNMPRD_ST_CODE      |
| evaluated                 |                   |                      |
+---------------------------+-------------------+----------------------+
| Water System being        | SAMPLE_SUMMARY    | TINWSYS_IS_NUMBER    |
| evaluated                 |                   |                      |
+---------------------------+-------------------+----------------------+
| Water System being        | SAMPLE_SUMMARY    | TINWSYS_ST_CODE      |
| evaluated                 |                   |                      |
+---------------------------+-------------------+----------------------+
| Water System Facility     | SAMPLE_SUMMARY    | TINWSF_IS_NUMBER     |
| being evaluated           |                   |                      |
+---------------------------+-------------------+----------------------+
| Water System Facility     | SAMPLE_SUMMARY    | TINWSF_ST_CODE       |
| being evaluated           |                   |                      |
+---------------------------+-------------------+----------------------+
| *Current date and time*   | *SAMPLE_SUMMARY*  | *D_LAST_UPDT_TS*     |
+---------------------------+-------------------+----------------------+
| *Collection_Begin_Date    | *SAMPLE_SUMMARY*  | *COLLECTION_STRT_DT* |
| variable*                 |                   |                      |
+---------------------------+-------------------+----------------------+
| *Collection_End_Date      | *SAMPLE_SUMMARY*  | *COLLECTION_END_DT*  |
| variable*                 |                   |                      |
+---------------------------+-------------------+----------------------+
| *Lab_IS_Number variable*  | *SAMPLE_SUMMARY*  | *TSALAB_IS_NUMBER*   |
+---------------------------+-------------------+----------------------+
| *Lab_ST_Code variable*    | *SAMPLE_SUMMARY*  | *TSALAB_ST_CODE*     |
+---------------------------+-------------------+----------------------+
| Set to "90\"              | SAMP              | TYPE_CODE            |
|                           | LE_SUMMARY_RESULT |                      |
+---------------------------+-------------------+----------------------+
| 90_Count variable         | SAMP              | COUNT_QTY            |
|                           | LE_SUMMARY_RESULT |                      |
+---------------------------+-------------------+----------------------+
| 90_Value variable         | SAMP              | MEASURE              |
|                           | LE_SUMMARY_RESULT |                      |
+---------------------------+-------------------+----------------------+
| Set to "MG/L"             | SAMP              | UOM_CODE             |
|                           | LE_SUMMARY_RESULT |                      |
+---------------------------+-------------------+----------------------+
| Set to ST_Code of SSMPA   | SAMPLE_SUMMARY    | SSMPA_SASCH_ST_CO    |
| to which current summary  |                   |                      |
| is associated             |                   | SSMPA_MPRD_ST_CO     |
+---------------------------+-------------------+----------------------+
| Set to ACDSSETUP@         | SAMP              | D_USERID_CD          |
|                           | LE_SUMMARY_RESULT |                      |
+---------------------------+-------------------+----------------------+
| To reference record       | SAMP              | TSASMPSM_IS_NUMBER   |
| created in SAMPLE_SUMMARY | LE_SUMMARY_RESULT |                      |
+---------------------------+-------------------+----------------------+
| To reference record       | SAMP              | TSASMPSM_ST_CODE     |
| created in SAMPLE_SUMMARY | LE_SUMMARY_RESULT |                      |
+---------------------------+-------------------+----------------------+
| Set to "A"                | SAMP              | DATA_QUALITY_CODE    |
|                           | LE_SUMMARY_RESULT |                      |
+---------------------------+-------------------+----------------------+
| Set to IS_Number of SSMPA | SAMPLE_SUMMARY    | SSMPA_SASCH_IS_NO    |
| to which current summary  |                   |                      |
| is associated             |                   | SSMPA_MPRD_IS_NO     |
+---------------------------+-------------------+----------------------+
| *Current date and time*   | *SAMPL            | *D_LAST_UPDT_TS*     |
|                           | E_SUMMARY_RESULT* |                      |
+---------------------------+-------------------+----------------------+
| *90_Count variable*       | *SAMPL            | *COUNT_QTY*          |
|                           | E_SUMMARY_RESULT* |                      |
+---------------------------+-------------------+----------------------+
| *90_Value variable*       | *SAMPL            | *MEASURE*            |
|                           | E_SUMMARY_RESULT* |                      |
+---------------------------+-------------------+----------------------+

*End of ACopper 90^th^ Percentile Values Routine*@