Modèle de données : Traitement des sinistres

`sql
-- 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';
`

`sql
-- 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';
`

`sql
-- 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';
`