SQL查询需求：动态筛选上年4月1日起数据并保留最新记录

Let's break down your two requirements and build the query step by step. I'll cover examples for the most common databases since you didn't specify which one you're using.

1. Dynamic Date Filter (No Hardcoded Dates) #

To get records where Insurance_Start_Date is on or after April 1st of the previous year, we'll use database-specific functions to calculate this date dynamically instead of hardcoding it:

• MySQL:

  SELECT *
  FROM TableA
  WHERE Insurance_Start_Date >= STR_TO_DATE(CONCAT(YEAR(CURDATE()) - 1, '-04-01'), '%Y-%m-%d')
  -- OR another equivalent approach:
  -- WHERE Insurance_Start_Date >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-04-01'), INTERVAL 1 YEAR)
  

  This calculates the previous year's April 1st by combining the current year minus 1 with "04-01", then converting it to a valid date format.

• SQL Server:

  SELECT *
  FROM TableA
  WHERE Insurance_Start_Date >= DATEFROMPARTS(YEAR(GETDATE()) - 1, 4, 1)
  

  DATEFROMPARTS lets us construct the exact target date using the previous year, month 4, and day 1 directly.

• PostgreSQL:

  SELECT *
  FROM TableA
  WHERE Insurance_Start_Date >= MAKE_DATE(EXTRACT(YEAR FROM CURRENT_DATE)::INTEGER - 1, 4, 1)
  

  Here we extract the current year, subtract 1, then use MAKE_DATE to build the required April 1st date.

2. Keep Only the Latest Record per Member #

To retain only the most recent Insurance_Start_Date for each member, we can use a window function like ROW_NUMBER() to rank records within each member group. Then we filter for the top-ranked record (the latest one).

Combining this with the date filter, here's the full query for each database:

MySQL (8.0+ supports window functions) #

WITH ranked_records AS (
  SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY Insurance_Start_Date DESC) AS rn
  FROM TableA
  WHERE Insurance_Start_Date >= STR_TO_DATE(CONCAT(YEAR(CURDATE()) - 1, '-04-01'), '%Y-%m-%d')
)
SELECT * EXCEPT (rn) -- Or list all columns except rn explicitly if EXCEPT isn't supported
FROM ranked_records
WHERE rn = 1;

SQL Server #

WITH ranked_records AS (
  SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY Insurance_Start_Date DESC) AS rn
  FROM TableA
  WHERE Insurance_Start_Date >= DATEFROMPARTS(YEAR(GETDATE()) - 1, 4, 1)
)
SELECT * -- Exclude rn by listing specific columns instead of using * if needed
FROM ranked_records
WHERE rn = 1;

PostgreSQL #

WITH ranked_records AS (
  SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY Insurance_Start_Date DESC) AS rn
  FROM TableA
  WHERE Insurance_Start_Date >= MAKE_DATE(EXTRACT(YEAR FROM CURRENT_DATE)::INTEGER - 1, 4, 1)
)
SELECT * EXCEPT (rn)
FROM ranked_records
WHERE rn = 1;

Quick Notes: #

• Replace member_id with the actual column name that identifies unique members in your TableA.
• If you're using an older MySQL version that doesn't support CTEs (WITH clause), swap to a subquery:

  SELECT *
  FROM (
    SELECT 
      *,
      ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY Insurance_Start_Date DESC) AS rn
    FROM TableA
    WHERE Insurance_Start_Date >= STR_TO_DATE(CONCAT(YEAR(CURDATE()) - 1, '-04-01'), '%Y-%m-%d')
  ) AS sub
  WHERE rn = 1;
  

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