SQL去重并保留索赔记录的最早/最晚日期

安全阿岳

2026-5-29

Hey Alan, no need to apologize at all—this is a totally common data wrangling task, and it’s straightforward once you know the right grouping/aggregation tricks. Let’s break down how to get your desired output using two popular tools, since you didn’t specify which workflow you’re using:

Solution 1: Using SQL

If your data is stored in a database, grouping by Customer and Claim_ID while aggregating the min/max dates is the perfect approach. Here’s the query you can use:

SELECT 
  Customer,
  Claim_ID,
  MIN(From_Date) AS From_Date,
  MAX(To_Date) AS To_Date
FROM your_table_name
GROUP BY Customer, Claim_ID
ORDER BY Customer, Claim_ID;

This query groups all rows by each unique Customer + Claim_ID pair, grabs the earliest start date (MIN(From_Date)) and latest end date (MAX(To_Date)), and drops the Code and Charge columns as you requested. Running this on your sample input will produce exactly the output you listed.

Solution 2: Using Python (Pandas)

If you’re working with the data in a Pandas DataFrame, here’s a step-by-step implementation:

import pandas as pd

# Load your sample data (replace with your actual data source)
data = pd.DataFrame({
    'Customer': ['023718421001', '023718421002', '023718421002', '023718421002'],
    'Claim_ID': ['300038310129', '300039554280', '300039554280', '300039573833'],
    'Code': ['A360', 'A360', 'A361', 'A100'],
    'From_Date': ['06/05/2017', '31/07/2017', '02/09/2017', '01/09/2017'],
    'To_Date': ['13/05/2017', '15/08/2017', '12/09/2017', '02/09/2017'],
    'Charge': [47, 47, 160, 100]
})

# Convert date columns to datetime type (critical for accurate min/max calculations)
data['From_Date'] = pd.to_datetime(data['From_Date'], format='%d/%m/%Y')
data['To_Date'] = pd.to_datetime(data['To_Date'], format='%d/%m/%Y')

# Group by Customer and Claim_ID, then aggregate dates
result = data.groupby(['Customer', 'Claim_ID']).agg(
    From_Date=('From_Date', 'min'),
    To_Date=('To_Date', 'max')
).reset_index()

# Convert dates back to your original dd/mm/yyyy string format
result['From_Date'] = result['From_Date'].dt.strftime('%d/%m/%Y')
result['To_Date'] = result['To_Date'].dt.strftime('%d/%m/%Y')

print(result)

This code first ensures your date columns are recognized as datetime objects (so Pandas can correctly compare and find the earliest/latest dates), groups the data, and then converts the dates back to your preferred string format. The final output will match exactly what you’re looking for.

Either of these methods should solve your problem—pick whichever fits your workflow best!

内容的提问来源于stack exchange，提问作者alanW