Votre modèle de données pour la gestion des sinistres

-- This query extracts a process mining event log for claims processing from a Guidewire DataHub/InfoCenter Data Mart.
-- Replace placeholders: [YourDataMart], [StartDate], [EndDate], and any configuration-specific string literals.

WITH ClaimHistory AS (
    -- Pre-process claim history to identify status changes, especially for Reopened events.
    SELECT
        ClaimID,
        Status,
        UpdateTime,
        LAG(Status, 1) OVER (PARTITION BY ClaimID ORDER BY UpdateTime) AS PreviousStatus
    FROM [YourDataMart].[dbo].[ClaimHistory_dim] -- Placeholder for claim history/audit table
),
BaseClaims AS (
    -- Select the set of claims to be analyzed based on a date range.
    SELECT
        c.ClaimID AS ClaimPublicID, -- Using PublicID as it's often the user-facing ID
        c.ClaimNumber AS ClaimID,
        c.AssignedAdjusterName AS AssignedAdjuster,
        c.PolicyType AS ClaimType,
        c.ClaimStatus AS ClaimStatus,
        c.LossCause AS LossCause,
        c.CreateTime
    FROM [YourDataMart].[dbo].[Claim_dim] c
    WHERE c.CreateTime >= '[StartDate]' AND c.CreateTime < '[EndDate]'
)

-- 1. Claim Created
SELECT
    bc.ClaimID AS ClaimID,
    'Claim Created' AS ActivityName,
    bc.CreateTime AS EventTime,
    bc.AssignedAdjuster,
    bc.ClaimType,
    bc.ClaimStatus,
    bc.LossCause
FROM BaseClaims bc

UNION ALL

-- 2. Claim Assigned
-- This captures the first assignment event from the history table.
SELECT
    bc.ClaimID,
    'Claim Assigned' AS ActivityName,
    MIN(ch.UpdateTime) AS EventTime,
    bc.AssignedAdjuster,
    bc.ClaimType,
    bc.ClaimStatus,
    bc.LossCause
FROM BaseClaims bc
JOIN [YourDataMart].[dbo].[ClaimHistory_dim] ch ON bc.ClaimPublicID = ch.ClaimID
WHERE ch.EventType = 'Assignment' -- Assumes an EventType column exists to identify assignment changes
GROUP BY bc.ClaimID, bc.AssignedAdjuster, bc.ClaimType, bc.ClaimStatus, bc.LossCause

UNION ALL

-- 3. Exposure Created
SELECT
    bc.ClaimID,
    'Exposure Created' AS ActivityName,
    e.CreateTime AS EventTime,
    bc.AssignedAdjuster,
    bc.ClaimType,
    bc.ClaimStatus,
    bc.LossCause
FROM BaseClaims bc
JOIN [YourDataMart].[dbo].[Exposure_dim] e ON bc.ClaimPublicID = e.ClaimID

UNION ALL

-- 4. Initial Reserve Set
-- Finds the very first reserve transaction for any exposure on the claim.
SELECT
    x.ClaimID,
    'Initial Reserve Set' AS ActivityName,
    x.EventTime,
    x.AssignedAdjuster,
    x.ClaimType,
    x.ClaimStatus,
    x.LossCause
FROM (
    SELECT
        bc.ClaimID,
        t.CreateTime AS EventTime,
        bc.AssignedAdjuster,
        bc.ClaimType,
        bc.ClaimStatus,
        bc.LossCause,
        ROW_NUMBER() OVER(PARTITION BY bc.ClaimID ORDER BY t.CreateTime) as rn
    FROM BaseClaims bc
    JOIN [YourDataMart].[dbo].[Transaction_fact] t ON bc.ClaimPublicID = t.ClaimID
    WHERE t.TransactionType = 'Reserve'
) x
WHERE x.rn = 1

UNION ALL

-- 5. Investigation Started
-- Finds the creation of the first investigation-related activity.
SELECT
    x.ClaimID,
    'Investigation Started' AS ActivityName,
    x.EventTime,
    x.AssignedAdjuster,
    x.ClaimType,
    x.ClaimStatus,
    x.LossCause
FROM (
    SELECT
        bc.ClaimID,
        a.CreateTime AS EventTime,
        bc.AssignedAdjuster,
        bc.ClaimType,
        bc.ClaimStatus,
        bc.LossCause,
        ROW_NUMBER() OVER(PARTITION BY bc.ClaimID ORDER BY a.CreateTime) as rn
    FROM BaseClaims bc
    JOIN [YourDataMart].[dbo].[Activity_dim] a ON bc.ClaimPublicID = a.ClaimID
    WHERE a.ActivityPatternName LIKE '%Investigation%'
) x
WHERE x.rn = 1

UNION ALL

-- 6. Additional Info Requested
SELECT
    bc.ClaimID,
    'Additional Info Requested' AS ActivityName,
    a.CreateTime AS EventTime,
    bc.AssignedAdjuster,
    bc.ClaimType,
    bc.ClaimStatus,
    bc.LossCause
FROM BaseClaims bc
JOIN [YourDataMart].[dbo].[Activity_dim] a ON bc.ClaimPublicID = a.ClaimID
WHERE a.ActivityPatternName LIKE '%Request%Information%'

UNION ALL

-- 7. Additional Info Received
SELECT
    bc.ClaimID,
    'Additional Info Received' AS ActivityName,
    a.CompletionTime AS EventTime,
    bc.AssignedAdjuster,
    bc.ClaimType,
    bc.ClaimStatus,
    bc.LossCause
FROM BaseClaims bc
JOIN [YourDataMart].[dbo].[Activity_dim] a ON bc.ClaimPublicID = a.ClaimID
WHERE a.ActivityPatternName LIKE '%Request%Information%' AND a.CompletionTime IS NOT NULL

UNION ALL

-- 8. Liability Decision Made
-- Captures when an exposure's liability decision is first set.
SELECT
    x.ClaimID,
    'Liability Decision Made' AS ActivityName,
    x.EventTime,
    x.AssignedAdjuster,
    x.ClaimType,
    x.ClaimStatus,
    x.LossCause
FROM (
    SELECT
        bc.ClaimID,
        eh.UpdateTime AS EventTime,
        bc.AssignedAdjuster,
        bc.ClaimType,
        bc.ClaimStatus,
        bc.LossCause,
        ROW_NUMBER() OVER(PARTITION BY bc.ClaimID ORDER BY eh.UpdateTime) as rn
    FROM BaseClaims bc
    JOIN [YourDataMart].[dbo].[ExposureHistory_dim] eh ON bc.ClaimPublicID = eh.ClaimID
    WHERE eh.LiabilityDecision IS NOT NULL AND eh.PreviousLiabilityDecision IS NULL -- Captures the first time it was set
) x
WHERE x.rn = 1

UNION ALL

-- 9. Settlement Calculated
-- Captures the creation of a payment transaction that is pending approval.
SELECT
    bc.ClaimID,
    'Settlement Calculated' AS ActivityName,
    t.CreateTime AS EventTime,
    bc.AssignedAdjuster,
    bc.ClaimType,
    bc.ClaimStatus,
    bc.LossCause
FROM BaseClaims bc
JOIN [YourDataMart].[dbo].[Transaction_fact] t ON bc.ClaimPublicID = t.ClaimID
WHERE t.TransactionType = 'Payment' AND t.TransactionStatus = 'PendingApproval'

UNION ALL

-- 10. Payment Approved
SELECT
    bc.ClaimID,
    'Payment Approved' AS ActivityName,
    t.ApprovalDate AS EventTime,
    bc.AssignedAdjuster,
    bc.ClaimType,
    bc.ClaimStatus,
    bc.LossCause
FROM BaseClaims bc
JOIN [YourDataMart].[dbo].[Transaction_fact] t ON bc.ClaimPublicID = t.ClaimID
WHERE t.TransactionType = 'Payment' AND t.ApprovalDate IS NOT NULL AND t.TransactionStatus = 'Approved'

UNION ALL

-- 11. Payment Issued
SELECT
    bc.ClaimID,
    'Payment Issued' AS ActivityName,
    t.IssueDate AS EventTime,
    bc.AssignedAdjuster,
    bc.ClaimType,
    bc.ClaimStatus,
    bc.LossCause
FROM BaseClaims bc
JOIN [YourDataMart].[dbo].[Transaction_fact] t ON bc.ClaimPublicID = t.ClaimID
WHERE t.TransactionType = 'Payment' AND t.IssueDate IS NOT NULL AND t.TransactionStatus = 'Issued'

UNION ALL

-- 12. Claim Denied
SELECT
    bc.ClaimID,
    'Claim Denied' AS ActivityName,
    ch.UpdateTime AS EventTime,
    bc.AssignedAdjuster,
    bc.ClaimType,
    'Denied' AS ClaimStatus, -- Overriding status for clarity
    bc.LossCause
FROM BaseClaims bc
JOIN ClaimHistory ch ON bc.ClaimPublicID = ch.ClaimID
WHERE ch.Status = 'Closed' AND ch.PreviousStatus <> 'Closed'
  AND EXISTS (SELECT 1 FROM [YourDataMart].[dbo].[Claim_dim] c2 WHERE c2.ClaimID = bc.ClaimPublicID AND c2.CloseReason = 'Denied')

UNION ALL

-- 13. Claim Closed
SELECT
    bc.ClaimID,
    'Claim Closed' AS ActivityName,
    ch.UpdateTime AS EventTime,
    bc.AssignedAdjuster,
    bc.ClaimType,
    'Closed' AS ClaimStatus, -- Overriding status for clarity
    bc.LossCause
FROM BaseClaims bc
JOIN ClaimHistory ch ON bc.ClaimPublicID = ch.ClaimID
WHERE ch.Status = 'Closed' AND ch.PreviousStatus <> 'Closed'
  AND NOT EXISTS (SELECT 1 FROM [YourDataMart].[dbo].[Claim_dim] c2 WHERE c2.ClaimID = bc.ClaimPublicID AND c2.CloseReason = 'Denied')

UNION ALL

-- 14. Claim Reopened
SELECT
    bc.ClaimID,
    'Claim Reopened' AS ActivityName,
    ch.UpdateTime AS EventTime,
    bc.AssignedAdjuster,
    bc.ClaimType,
    'Open' AS ClaimStatus, -- Overriding status for clarity
    bc.LossCause
FROM BaseClaims bc
JOIN ClaimHistory ch ON bc.ClaimPublicID = ch.ClaimID
WHERE ch.PreviousStatus = 'Closed' AND ch.Status <> 'Closed';

WITH
params AS (
    SELECT
        TO_TIMESTAMP_TZ('[Start timestamp]') AS start_ts,
        TO_TIMESTAMP_TZ('[End timestamp]') AS end_ts
),
claim_created AS (
    SELECT
        CAST(c.[Claim ID column] AS VARCHAR) AS ClaimID,
        'Claim Created' AS ActivityName,
        CAST(c.[Claim created timestamp column] AS TIMESTAMP_TZ) AS EventTime,
        CAST(c.[Assigned adjuster column] AS VARCHAR) AS AssignedAdjuster,
        CAST(c.[Claim type column] AS VARCHAR) AS ClaimType,
        CAST(c.[Claim status column] AS VARCHAR) AS ClaimStatus,
        CAST(c.[Loss cause column] AS VARCHAR) AS LossCause,
        CAST(c.[Claim event identifier column] AS VARCHAR) AS SourceEventID
    FROM [Your claim source object] c
    CROSS JOIN params p
    WHERE c.[Claim created timestamp column] >= p.start_ts
      AND c.[Claim created timestamp column] < p.end_ts
      AND [Company filter]
),
claim_assigned AS (
    SELECT
        CAST(h.[Claim ID column] AS VARCHAR) AS ClaimID,
        'Claim Assigned' AS ActivityName,
        CAST(h.[Assignment event timestamp column] AS TIMESTAMP_TZ) AS EventTime,
        CAST(h.[New assigned adjuster column] AS VARCHAR) AS AssignedAdjuster,
        CAST(h.[Claim type column] AS VARCHAR) AS ClaimType,
        CAST(h.[Claim status column] AS VARCHAR) AS ClaimStatus,
        CAST(h.[Loss cause column] AS VARCHAR) AS LossCause,
        CAST(h.[Assignment event identifier column] AS VARCHAR) AS SourceEventID
    FROM [Your claim assignment history object] h
    CROSS JOIN params p
    WHERE h.[Assignment event timestamp column] >= p.start_ts
      AND h.[Assignment event timestamp column] < p.end_ts
      AND h.[New assigned adjuster column] IS DISTINCT FROM h.[Previous assigned adjuster column]
      AND [Company filter]
),
exposure_created AS (
    SELECT
        CAST(e.[Claim ID column] AS VARCHAR) AS ClaimID,
        'Exposure Created' AS ActivityName,
        CAST(e.[Exposure created timestamp column] AS TIMESTAMP_TZ) AS EventTime,
        CAST(e.[Assigned adjuster column] AS VARCHAR) AS AssignedAdjuster,
        CAST(e.[Claim type column] AS VARCHAR) AS ClaimType,
        CAST(e.[Claim status column] AS VARCHAR) AS ClaimStatus,
        CAST(e.[Loss cause column] AS VARCHAR) AS LossCause,
        CAST(e.[Exposure event identifier column] AS VARCHAR) AS SourceEventID
    FROM [Your exposure source object] e
    CROSS JOIN params p
    WHERE e.[Exposure created timestamp column] >= p.start_ts
      AND e.[Exposure created timestamp column] < p.end_ts
      AND [Company filter]
),
initial_reserve_set AS (
    SELECT
        CAST(r.[Claim ID column] AS VARCHAR) AS ClaimID,
        'Initial Reserve Set' AS ActivityName,
        CAST(r.[Reserve transaction timestamp column] AS TIMESTAMP_TZ) AS EventTime,
        CAST(r.[Assigned adjuster column] AS VARCHAR) AS AssignedAdjuster,
        CAST(r.[Claim type column] AS VARCHAR) AS ClaimType,
        CAST(r.[Claim status column] AS VARCHAR) AS ClaimStatus,
        CAST(r.[Loss cause column] AS VARCHAR) AS LossCause,
        CAST(r.[Reserve transaction identifier column] AS VARCHAR) AS SourceEventID
    FROM [Your reserve transaction source object] r
    CROSS JOIN params p
    WHERE r.[Reserve transaction timestamp column] >= p.start_ts
      AND r.[Reserve transaction timestamp column] < p.end_ts
      AND r.[Reserve transaction sequence or first reserve indicator column] = [Value identifying first reserve]
      AND [Company filter]
),
investigation_started AS (
    SELECT
        CAST(a.[Claim ID column] AS VARCHAR) AS ClaimID,
        'Investigation Started' AS ActivityName,
        CAST(a.[Activity created timestamp column] AS TIMESTAMP_TZ) AS EventTime,
        CAST(a.[Activity assigned user column] AS VARCHAR) AS AssignedAdjuster,
        CAST(a.[Claim type column] AS VARCHAR) AS ClaimType,
        CAST(a.[Claim status column] AS VARCHAR) AS ClaimStatus,
        CAST(a.[Loss cause column] AS VARCHAR) AS LossCause,
        CAST(a.[Activity identifier column] AS VARCHAR) AS SourceEventID
    FROM [Your activity source object] a
    CROSS JOIN params p
    WHERE a.[Activity created timestamp column] >= p.start_ts
      AND a.[Activity created timestamp column] < p.end_ts
      AND a.[Activity category or type column] = '[Configured investigation activity value]'
      AND [Company filter]
),
additional_info_requested AS (
    SELECT
        CAST(a.[Claim ID column] AS VARCHAR) AS ClaimID,
        'Additional Info Requested' AS ActivityName,
        CAST(a.[Activity created timestamp column] AS TIMESTAMP_TZ) AS EventTime,
        CAST(a.[Activity assigned user column] AS VARCHAR) AS AssignedAdjuster,
        CAST(a.[Claim type column] AS VARCHAR) AS ClaimType,
        CAST(a.[Claim status column] AS VARCHAR) AS ClaimStatus,
        CAST(a.[Loss cause column] AS VARCHAR) AS LossCause,
        CAST(a.[Activity identifier column] AS VARCHAR) AS SourceEventID
    FROM [Your activity source object] a
    CROSS JOIN params p
    WHERE a.[Activity created timestamp column] >= p.start_ts
      AND a.[Activity created timestamp column] < p.end_ts
      AND a.[Activity category or type column] = '[Configured additional information request value]'
      AND [Company filter]
),
additional_info_received AS (
    SELECT
        CAST(a.[Claim ID column] AS VARCHAR) AS ClaimID,
        'Additional Info Received' AS ActivityName,
        CAST(a.[Activity completion timestamp column] AS TIMESTAMP_TZ) AS EventTime,
        CAST(a.[Activity assigned user column] AS VARCHAR) AS AssignedAdjuster,
        CAST(a.[Claim type column] AS VARCHAR) AS ClaimType,
        CAST(a.[Claim status column] AS VARCHAR) AS ClaimStatus,
        CAST(a.[Loss cause column] AS VARCHAR) AS LossCause,
        CAST(a.[Activity identifier column] AS VARCHAR) AS SourceEventID
    FROM [Your activity source object] a
    CROSS JOIN params p
    WHERE a.[Activity completion timestamp column] >= p.start_ts
      AND a.[Activity completion timestamp column] < p.end_ts
      AND a.[Activity category or type column] = '[Configured additional information request value]'
      AND a.[Activity status column] = '[Configured completed status value]'
      AND [Company filter]
),
liability_decision_made AS (
    SELECT
        CAST(eh.[Claim ID column] AS VARCHAR) AS ClaimID,
        'Liability Decision Made' AS ActivityName,
        CAST(eh.[Exposure status event timestamp column] AS TIMESTAMP_TZ) AS EventTime,
        CAST(eh.[Assigned adjuster column] AS VARCHAR) AS AssignedAdjuster,
        CAST(eh.[Claim type column] AS VARCHAR) AS ClaimType,
        CAST(eh.[Claim status column] AS VARCHAR) AS ClaimStatus,
        CAST(eh.[Loss cause column] AS VARCHAR) AS LossCause,
        CAST(eh.[Exposure status event identifier column] AS VARCHAR) AS SourceEventID
    FROM [Your exposure status history object] eh
    CROSS JOIN params p
    WHERE eh.[Exposure status event timestamp column] >= p.start_ts
      AND eh.[Exposure status event timestamp column] < p.end_ts
      AND eh.[New exposure status column] = '[Configured liability decision status value]'
      AND eh.[New exposure status column] IS DISTINCT FROM eh.[Previous exposure status column]
      AND [Company filter]
),
settlement_calculated AS (
    SELECT
        CAST(pay.[Claim ID column] AS VARCHAR) AS ClaimID,
        'Settlement Calculated' AS ActivityName,
        CAST(pay.[Payment created timestamp column] AS TIMESTAMP_TZ) AS EventTime,
        CAST(pay.[Assigned adjuster column] AS VARCHAR) AS AssignedAdjuster,
        CAST(pay.[Claim type column] AS VARCHAR) AS ClaimType,
        CAST(pay.[Claim status column] AS VARCHAR) AS ClaimStatus,
        CAST(pay.[Loss cause column] AS VARCHAR) AS LossCause,
        CAST(pay.[Payment identifier column] AS VARCHAR) AS SourceEventID
    FROM [Your payment source object] pay
    CROSS JOIN params p
    WHERE pay.[Payment created timestamp column] >= p.start_ts
      AND pay.[Payment created timestamp column] < p.end_ts
      AND pay.[Payment status column] = '[Configured pending approval status value]'
      AND [Company filter]
),
payment_approved AS (
    SELECT
        CAST(ph.[Claim ID column] AS VARCHAR) AS ClaimID,
        'Payment Approved' AS ActivityName,
        CAST(ph.[Payment approval timestamp column] AS TIMESTAMP_TZ) AS EventTime,
        CAST(ph.[Approving user column] AS VARCHAR) AS AssignedAdjuster,
        CAST(ph.[Claim type column] AS VARCHAR) AS ClaimType,
        CAST(ph.[Claim status column] AS VARCHAR) AS ClaimStatus,
        CAST(ph.[Loss cause column] AS VARCHAR) AS LossCause,
        CAST(ph.[Payment status event identifier column] AS VARCHAR) AS SourceEventID
    FROM [Your payment status history object] ph
    CROSS JOIN params p
    WHERE ph.[Payment approval timestamp column] >= p.start_ts
      AND ph.[Payment approval timestamp column] < p.end_ts
      AND ph.[New payment status column] = '[Configured approved status value]'
      AND ph.[New payment status column] IS DISTINCT FROM ph.[Previous payment status column]
      AND [Company filter]
),
payment_issued AS (
    SELECT
        CAST(ph.[Claim ID column] AS VARCHAR) AS ClaimID,
        'Payment Issued' AS ActivityName,
        CAST(ph.[Payment issued timestamp column] AS TIMESTAMP_TZ) AS EventTime,
        CAST(ph.[Issuing user column] AS VARCHAR) AS AssignedAdjuster,
        CAST(ph.[Claim type column] AS VARCHAR) AS ClaimType,
        CAST(ph.[Claim status column] AS VARCHAR) AS ClaimStatus,
        CAST(ph.[Loss cause column] AS VARCHAR) AS LossCause,
        CAST(ph.[Payment status event identifier column] AS VARCHAR) AS SourceEventID
    FROM [Your payment status history object] ph
    CROSS JOIN params p
    WHERE ph.[Payment issued timestamp column] >= p.start_ts
      AND ph.[Payment issued timestamp column] < p.end_ts
      AND ph.[New payment status column] = '[Configured issued status value]'
      AND ph.[New payment status column] IS DISTINCT FROM ph.[Previous payment status column]
      AND [Company filter]
),
claim_denied AS (
    SELECT
        CAST(ch.[Claim ID column] AS VARCHAR) AS ClaimID,
        'Claim Denied' AS ActivityName,
        CAST(ch.[Claim status event timestamp column] AS TIMESTAMP_TZ) AS EventTime,
        CAST(ch.[Assigned adjuster column] AS VARCHAR) AS AssignedAdjuster,
        CAST(ch.[Claim type column] AS VARCHAR) AS ClaimType,
        CAST(ch.[New claim status column] AS VARCHAR) AS ClaimStatus,
        CAST(ch.[Loss cause column] AS VARCHAR) AS LossCause,
        CAST(ch.[Claim status event identifier column] AS VARCHAR) AS SourceEventID
    FROM [Your claim status history object] ch
    CROSS JOIN params p
    WHERE ch.[Claim status event timestamp column] >= p.start_ts
      AND ch.[Claim status event timestamp column] < p.end_ts
      AND ch.[New claim status column] = '[Configured closed status value]'
      AND ch.[Claim closure reason column] = '[Configured denied reason value]'
      AND ch.[New claim status column] IS DISTINCT FROM ch.[Previous claim status column]
      AND [Company filter]
),
claim_closed AS (
    SELECT
        CAST(ch.[Claim ID column] AS VARCHAR) AS ClaimID,
        'Claim Closed' AS ActivityName,
        CAST(ch.[Claim status event timestamp column] AS TIMESTAMP_TZ) AS EventTime,
        CAST(ch.[Assigned adjuster column] AS VARCHAR) AS AssignedAdjuster,
        CAST(ch.[Claim type column] AS VARCHAR) AS ClaimType,
        CAST(ch.[New claim status column] AS VARCHAR) AS ClaimStatus,
        CAST(ch.[Loss cause column] AS VARCHAR) AS LossCause,
        CAST(ch.[Claim status event identifier column] AS VARCHAR) AS SourceEventID
    FROM [Your claim status history object] ch
    CROSS JOIN params p
    WHERE ch.[Claim status event timestamp column] >= p.start_ts
      AND ch.[Claim status event timestamp column] < p.end_ts
      AND ch.[New claim status column] = '[Configured closed status value]'
      AND COALESCE(ch.[Claim closure reason column], '') <> '[Configured denied reason value]'
      AND ch.[New claim status column] IS DISTINCT FROM ch.[Previous claim status column]
      AND [Company filter]
),
claim_reopened AS (
    SELECT
        CAST(ch.[Claim ID column] AS VARCHAR) AS ClaimID,
        'Claim Reopened' AS ActivityName,
        CAST(ch.[Claim status event timestamp column] AS TIMESTAMP_TZ) AS EventTime,
        CAST(ch.[Assigned adjuster column] AS VARCHAR) AS AssignedAdjuster,
        CAST(ch.[Claim type column] AS VARCHAR) AS ClaimType,
        CAST(ch.[New claim status column] AS VARCHAR) AS ClaimStatus,
        CAST(ch.[Loss cause column] AS VARCHAR) AS LossCause,
        CAST(ch.[Claim status event identifier column] AS VARCHAR) AS SourceEventID
    FROM [Your claim status history object] ch
    CROSS JOIN params p
    WHERE ch.[Claim status event timestamp column] >= p.start_ts
      AND ch.[Claim status event timestamp column] < p.end_ts
      AND ch.[Previous claim status column] = '[Configured closed status value]'
      AND ch.[New claim status column] = '[Configured open status value]'
      AND ch.[New claim status column] IS DISTINCT FROM ch.[Previous claim status column]
      AND [Company filter]
),
all_events AS (
    SELECT * FROM claim_created
    UNION ALL SELECT * FROM claim_assigned
    UNION ALL SELECT * FROM exposure_created
    UNION ALL SELECT * FROM initial_reserve_set
    UNION ALL SELECT * FROM investigation_started
    UNION ALL SELECT * FROM additional_info_requested
    UNION ALL SELECT * FROM additional_info_received
    UNION ALL SELECT * FROM liability_decision_made
    UNION ALL SELECT * FROM settlement_calculated
    UNION ALL SELECT * FROM payment_approved
    UNION ALL SELECT * FROM payment_issued
    UNION ALL SELECT * FROM claim_denied
    UNION ALL SELECT * FROM claim_closed
    UNION ALL SELECT * FROM claim_reopened
),
deduplicated_events AS (
    SELECT
        ClaimID,
        ActivityName,
        EventTime,
        AssignedAdjuster,
        ClaimType,
        ClaimStatus,
        LossCause,
        SourceEventID,
        ROW_NUMBER() OVER (
            PARTITION BY ActivityName, COALESCE(SourceEventID, ClaimID || '|' || TO_VARCHAR(EventTime))
            ORDER BY EventTime
        ) AS duplicate_rank
    FROM all_events
    WHERE ClaimID IS NOT NULL
      AND ActivityName IS NOT NULL
      AND EventTime IS NOT NULL
)
SELECT
    ClaimID,
    ActivityName,
    EventTime,
    AssignedAdjuster,
    ClaimType,
    ClaimStatus,
    LossCause
FROM deduplicated_events
WHERE duplicate_rank = 1
ORDER BY ClaimID, EventTime, ActivityName;

WITH
claim_created AS (
    SELECT
        c.[Claim ID column] AS ClaimID,
        CAST('Claim Created' AS VARCHAR(100)) AS ActivityName,
        c.[Claim Created Timestamp column] AS EventTime,
        c.[Assigned Adjuster column] AS AssignedAdjuster,
        c.[Claim Type column] AS ClaimType,
        c.[Claim Status column] AS ClaimStatus,
        c.[Loss Cause column] AS LossCause
    FROM [Your claim table] c
    WHERE c.[Claim Created Timestamp column] >= [Start date parameter]
      AND c.[Claim Created Timestamp column] < [End date parameter]
),
claim_assigned AS (
    SELECT
        h.[Claim ID column] AS ClaimID,
        CAST('Claim Assigned' AS VARCHAR(100)) AS ActivityName,
        h.[Assignment Change Timestamp column] AS EventTime,
        h.[Assigned Adjuster column] AS AssignedAdjuster,
        c.[Claim Type column] AS ClaimType,
        h.[Claim Status column] AS ClaimStatus,
        c.[Loss Cause column] AS LossCause
    FROM [Your claim assignment history table] h
    INNER JOIN [Your claim table] c
        ON c.[Claim ID column] = h.[Claim ID column]
    WHERE h.[Assignment Change Timestamp column] >= [Start date parameter]
      AND h.[Assignment Change Timestamp column] < [End date parameter]
      AND h.[Assigned Adjuster column] IS NOT NULL
),
exposure_created AS (
    SELECT
        e.[Claim ID column] AS ClaimID,
        CAST('Exposure Created' AS VARCHAR(100)) AS ActivityName,
        e.[Exposure Created Timestamp column] AS EventTime,
        c.[Assigned Adjuster column] AS AssignedAdjuster,
        c.[Claim Type column] AS ClaimType,
        c.[Claim Status column] AS ClaimStatus,
        c.[Loss Cause column] AS LossCause
    FROM [Your exposure table] e
    INNER JOIN [Your claim table] c
        ON c.[Claim ID column] = e.[Claim ID column]
    WHERE e.[Exposure Created Timestamp column] >= [Start date parameter]
      AND e.[Exposure Created Timestamp column] < [End date parameter]
),
initial_reserve_set AS (
    SELECT
        x.ClaimID,
        CAST('Initial Reserve Set' AS VARCHAR(100)) AS ActivityName,
        x.EventTime,
        x.AssignedAdjuster,
        x.ClaimType,
        x.ClaimStatus,
        x.LossCause
    FROM (
        SELECT
            r.[Claim ID column] AS ClaimID,
            r.[Reserve Timestamp column] AS EventTime,
            c.[Assigned Adjuster column] AS AssignedAdjuster,
            c.[Claim Type column] AS ClaimType,
            c.[Claim Status column] AS ClaimStatus,
            c.[Loss Cause column] AS LossCause,
            ROW_NUMBER() OVER (
                PARTITION BY r.[Exposure ID column]
                ORDER BY r.[Reserve Timestamp column], r.[Reserve Transaction ID column]
            ) AS reserve_sequence
        FROM [Your reserve transaction table] r
        INNER JOIN [Your claim table] c
            ON c.[Claim ID column] = r.[Claim ID column]
        WHERE r.[Reserve Timestamp column] >= [Start date parameter]
          AND r.[Reserve Timestamp column] < [End date parameter]
    ) x
    WHERE x.reserve_sequence = 1
),
investigation_started AS (
    SELECT
        a.[Claim ID column] AS ClaimID,
        CAST('Investigation Started' AS VARCHAR(100)) AS ActivityName,
        a.[Activity Created Timestamp column] AS EventTime,
        a.[Assigned User column] AS AssignedAdjuster,
        c.[Claim Type column] AS ClaimType,
        c.[Claim Status column] AS ClaimStatus,
        c.[Loss Cause column] AS LossCause
    FROM [Your activity table] a
    INNER JOIN [Your claim table] c
        ON c.[Claim ID column] = a.[Claim ID column]
    WHERE a.[Activity Created Timestamp column] >= [Start date parameter]
      AND a.[Activity Created Timestamp column] < [End date parameter]
      AND a.[Activity Type column] IN ([Investigation activity type value])
),
additional_info_requested AS (
    SELECT
        a.[Claim ID column] AS ClaimID,
        CAST('Additional Info Requested' AS VARCHAR(100)) AS ActivityName,
        a.[Activity Created Timestamp column] AS EventTime,
        a.[Assigned User column] AS AssignedAdjuster,
        c.[Claim Type column] AS ClaimType,
        c.[Claim Status column] AS ClaimStatus,
        c.[Loss Cause column] AS LossCause
    FROM [Your activity table] a
    INNER JOIN [Your claim table] c
        ON c.[Claim ID column] = a.[Claim ID column]
    WHERE a.[Activity Created Timestamp column] >= [Start date parameter]
      AND a.[Activity Created Timestamp column] < [End date parameter]
      AND a.[Activity Type column] IN ([Additional information request activity type value])
),
additional_info_received AS (
    SELECT
        a.[Claim ID column] AS ClaimID,
        CAST('Additional Info Received' AS VARCHAR(100)) AS ActivityName,
        a.[Activity Completed Timestamp column] AS EventTime,
        a.[Assigned User column] AS AssignedAdjuster,
        c.[Claim Type column] AS ClaimType,
        c.[Claim Status column] AS ClaimStatus,
        c.[Loss Cause column] AS LossCause
    FROM [Your activity table] a
    INNER JOIN [Your claim table] c
        ON c.[Claim ID column] = a.[Claim ID column]
    WHERE a.[Activity Completed Timestamp column] >= [Start date parameter]
      AND a.[Activity Completed Timestamp column] < [End date parameter]
      AND a.[Activity Type column] IN ([Additional information request activity type value])
      AND a.[Activity Status column] = [Completed activity status value]
),
liability_decision_made AS (
    SELECT
        h.[Claim ID column] AS ClaimID,
        CAST('Liability Decision Made' AS VARCHAR(100)) AS ActivityName,
        h.[Status Change Timestamp column] AS EventTime,
        c.[Assigned Adjuster column] AS AssignedAdjuster,
        c.[Claim Type column] AS ClaimType,
        c.[Claim Status column] AS ClaimStatus,
        c.[Loss Cause column] AS LossCause
    FROM [Your exposure status history table] h
    INNER JOIN [Your claim table] c
        ON c.[Claim ID column] = h.[Claim ID column]
    WHERE h.[Status Change Timestamp column] >= [Start date parameter]
      AND h.[Status Change Timestamp column] < [End date parameter]
      AND h.[New Exposure Status column] IN ([Liability decision status value])
),
settlement_calculated AS (
    SELECT
        p.[Claim ID column] AS ClaimID,
        CAST('Settlement Calculated' AS VARCHAR(100)) AS ActivityName,
        p.[Payment Status Timestamp column] AS EventTime,
        c.[Assigned Adjuster column] AS AssignedAdjuster,
        c.[Claim Type column] AS ClaimType,
        c.[Claim Status column] AS ClaimStatus,
        c.[Loss Cause column] AS LossCause
    FROM [Your payment table] p
    INNER JOIN [Your claim table] c
        ON c.[Claim ID column] = p.[Claim ID column]
    WHERE p.[Payment Status Timestamp column] >= [Start date parameter]
      AND p.[Payment Status Timestamp column] < [End date parameter]
      AND p.[Payment Status column] = [Pending approval payment status value]
),
payment_approved AS (
    SELECT
        p.[Claim ID column] AS ClaimID,
        CAST('Payment Approved' AS VARCHAR(100)) AS ActivityName,
        p.[Approval Timestamp column] AS EventTime,
        p.[Approved By column] AS AssignedAdjuster,
        c.[Claim Type column] AS ClaimType,
        c.[Claim Status column] AS ClaimStatus,
        c.[Loss Cause column] AS LossCause
    FROM [Your payment approval history table] p
    INNER JOIN [Your claim table] c
        ON c.[Claim ID column] = p.[Claim ID column]
    WHERE p.[Approval Timestamp column] >= [Start date parameter]
      AND p.[Approval Timestamp column] < [End date parameter]
      AND p.[Approval Status column] = [Approved payment status value]
),
payment_issued AS (
    SELECT
        p.[Claim ID column] AS ClaimID,
        CAST('Payment Issued' AS VARCHAR(100)) AS ActivityName,
        p.[Issued Timestamp column] AS EventTime,
        p.[Approved By column] AS AssignedAdjuster,
        c.[Claim Type column] AS ClaimType,
        c.[Claim Status column] AS ClaimStatus,
        c.[Loss Cause column] AS LossCause
    FROM [Your payment issuance table] p
    INNER JOIN [Your claim table] c
        ON c.[Claim ID column] = p.[Claim ID column]
    WHERE p.[Issued Timestamp column] >= [Start date parameter]
      AND p.[Issued Timestamp column] < [End date parameter]
      AND p.[Payment Status column] = [Issued payment status value]
),
claim_denied AS (
    SELECT
        h.[Claim ID column] AS ClaimID,
        CAST('Claim Denied' AS VARCHAR(100)) AS ActivityName,
        h.[Status Change Timestamp column] AS EventTime,
        c.[Assigned Adjuster column] AS AssignedAdjuster,
        c.[Claim Type column] AS ClaimType,
        h.[New Claim Status column] AS ClaimStatus,
        c.[Loss Cause column] AS LossCause
    FROM [Your claim status history table] h
    INNER JOIN [Your claim table] c
        ON c.[Claim ID column] = h.[Claim ID column]
    WHERE h.[Status Change Timestamp column] >= [Start date parameter]
      AND h.[Status Change Timestamp column] < [End date parameter]
      AND h.[New Claim Status column] = [Closed claim status value]
      AND h.[Status Reason column] = [Denied status reason value]
),
claim_closed AS (
    SELECT
        h.[Claim ID column] AS ClaimID,
        CAST('Claim Closed' AS VARCHAR(100)) AS ActivityName,
        h.[Status Change Timestamp column] AS EventTime,
        c.[Assigned Adjuster column] AS AssignedAdjuster,
        c.[Claim Type column] AS ClaimType,
        h.[New Claim Status column] AS ClaimStatus,
        c.[Loss Cause column] AS LossCause
    FROM [Your claim status history table] h
    INNER JOIN [Your claim table] c
        ON c.[Claim ID column] = h.[Claim ID column]
    WHERE h.[Status Change Timestamp column] >= [Start date parameter]
      AND h.[Status Change Timestamp column] < [End date parameter]
      AND h.[New Claim Status column] = [Closed claim status value]
      AND h.[Status Reason column] <> [Denied status reason value]
),
claim_reopened AS (
    SELECT
        h.[Claim ID column] AS ClaimID,
        CAST('Claim Reopened' AS VARCHAR(100)) AS ActivityName,
        h.[Status Change Timestamp column] AS EventTime,
        c.[Assigned Adjuster column] AS AssignedAdjuster,
        c.[Claim Type column] AS ClaimType,
        h.[New Claim Status column] AS ClaimStatus,
        c.[Loss Cause column] AS LossCause
    FROM [Your claim status history table] h
    INNER JOIN [Your claim status history table] previous_h
        ON previous_h.[Claim ID column] = h.[Claim ID column]
       AND previous_h.[Status Change Timestamp column] = (
           SELECT MAX(prior_h.[Status Change Timestamp column])
           FROM [Your claim status history table] prior_h
           WHERE prior_h.[Claim ID column] = h.[Claim ID column]
             AND prior_h.[Status Change Timestamp column] < h.[Status Change Timestamp column]
       )
    INNER JOIN [Your claim table] c
        ON c.[Claim ID column] = h.[Claim ID column]
    WHERE h.[Status Change Timestamp column] >= [Start date parameter]
      AND h.[Status Change Timestamp column] < [End date parameter]
      AND previous_h.[New Claim Status column] = [Closed claim status value]
      AND h.[New Claim Status column] = [Open claim status value]
)
SELECT ClaimID, ActivityName, EventTime, AssignedAdjuster, ClaimType, ClaimStatus, LossCause FROM claim_created
UNION ALL
SELECT ClaimID, ActivityName, EventTime, AssignedAdjuster, ClaimType, ClaimStatus, LossCause FROM claim_assigned
UNION ALL
SELECT ClaimID, ActivityName, EventTime, AssignedAdjuster, ClaimType, ClaimStatus, LossCause FROM exposure_created
UNION ALL
SELECT ClaimID, ActivityName, EventTime, AssignedAdjuster, ClaimType, ClaimStatus, LossCause FROM initial_reserve_set
UNION ALL
SELECT ClaimID, ActivityName, EventTime, AssignedAdjuster, ClaimType, ClaimStatus, LossCause FROM investigation_started
UNION ALL
SELECT ClaimID, ActivityName, EventTime, AssignedAdjuster, ClaimType, ClaimStatus, LossCause FROM additional_info_requested
UNION ALL
SELECT ClaimID, ActivityName, EventTime, AssignedAdjuster, ClaimType, ClaimStatus, LossCause FROM additional_info_received
UNION ALL
SELECT ClaimID, ActivityName, EventTime, AssignedAdjuster, ClaimType, ClaimStatus, LossCause FROM liability_decision_made
UNION ALL
SELECT ClaimID, ActivityName, EventTime, AssignedAdjuster, ClaimType, ClaimStatus, LossCause FROM settlement_calculated
UNION ALL
SELECT ClaimID, ActivityName, EventTime, AssignedAdjuster, ClaimType, ClaimStatus, LossCause FROM payment_approved
UNION ALL
SELECT ClaimID, ActivityName, EventTime, AssignedAdjuster, ClaimType, ClaimStatus, LossCause FROM payment_issued
UNION ALL
SELECT ClaimID, ActivityName, EventTime, AssignedAdjuster, ClaimType, ClaimStatus, LossCause FROM claim_denied
UNION ALL
SELECT ClaimID, ActivityName, EventTime, AssignedAdjuster, ClaimType, ClaimStatus, LossCause FROM claim_closed
UNION ALL
SELECT ClaimID, ActivityName, EventTime, AssignedAdjuster, ClaimType, ClaimStatus, LossCause FROM claim_reopened
ORDER BY ClaimID, EventTime, ActivityName;