# View: dt_contract_mech_deduction_history

**View Name:** dt_contract_mech_deduction_history
**Table Source:** `Derived from: Mech_History_All_Records, Orchard_App_Reporting_V2, Orchard_App_Reporting_V2.Prod_Royalty_Accounting_Royalty_Accounting, Prod_Royalty_Accounting_Royalty_Accounting.Contract_Mechanical_Deduction, Prod_Royalty_Accounting_Royalty_Accounting.Contract_Mechanical_Deduction_History`
**File Path:** `views/dt_contract_mech_deduction_history.view.lkml`

## Overview

- **File Size:** 4407 bytes
- **Lines of Code:** 151
- **Dimensions:** 14
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `record_status` | string |
| `contract_mechanical_deduction_history_id` | string |
| `contract_mechanical_deduction_id` | string |
| `contract_id` | string |
| `territory` | string |
| `mechanical_type` | string |
| `admin_type` | string |
| `admin_fee` | string |
| `created_at` | date_time |
| `last_modified_at` | date_time |
| `last_modified_by` | string |
| `created_by` | string |
| `deleted_by` | string |
| `deleted_at` | date_time |

## Derived Table

```sql
sql:
        WITH mech_history_all_records AS (
            SELECT
                contract_mechanical_deduction_history_id,
                h.contract_mechanical_deduction_id,
                h.contract_id,
                h.territory,
                h.mechanical_type,
                h.admin_type,
                h.admin_fee,
                h.created_by,
                h.created_at,
                h.last_modified_by,
                h.last_modified,
                COALESCE(h.deleted_by, new.deleted_by) AS deleted_by,
                COALESCE(h.deleted_at, new.deleted_at) AS deleted_at,
                h._fivetran_deleted,
                h._fivetran_synced
            FROM
                orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.contract_mechanical_deduction_history h
                LEFT JOIN orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.contract_mechanical_deduction new
                    ON h.contract_id = new.contract_id
                    AND h.territory = new.territory
                    AND h.contract_mechanical_deduction_id = new.contract_mechanical_deduction_id
                    AND new.deleted_at IS NOT NULL
            UNION ALL
            SELECT
                9999999 AS contract_mechanical_deduction_history_id,
                contract_mechanical_deduction_id,
                contract_id,
                territory,
                mechanical_type,
                admin_type,
                admin_fee,
                created_by,
                created_at,
                last_modified_by,
                last_modified,
                deleted_by,
                deleted_at,
                _fivetran_deleted,
                _fivetran_synced
            FROM
                orchard_app_reporting_v2.prod_royalty_accounting_royalty_accounting.contract_mechanical_deduction n
        )
        SELECT
            CASE
                WHEN contract_mechanical_deduction_history_id = 9999999
                AND deleted_at IS NULL THEN 'New'
                ELSE 'Old'
            END AS record_status,
            CASE
                WHEN contract_mechanical_deduction_history_id = 9999999
                AND deleted_at IS NOT NULL THEN LEAD(contract_mechanical_deduction_history_id) OVER (
                    PARTITION BY contract_mechanical_deduction_id, contract_id,
                    territory
                    ORDER BY
                        contract_mechanical_deduction_history_id DESC
                )
                ELSE contract_mechanical_deduction_history_id
            END AS contract_mechanical_deduction_history_id,
            contract_mechanical_deduction_id,
            contract_id,
            territory,
            mechanical_type,
            admin_type,
            admin_fee,
            created_by,
            created_at,
            last_modified_by,
            last_modified,
            deleted_by,
            deleted_at
        FROM
            mech_history_all_records
        ORDER BY
            last_modified DESC;;
```