生成查询2004年后开立的ACTIVE定期存款账户特定字段的SQL语句

First, let's assume your FD account table is named fd_accounts (swap this with your actual table name if needed) with core columns like account_number, customer_id, initial_amount, opening_date, tenure_months, and status. Here's the SQL query that checks all your boxes:

SELECT
    account_number AS "账户号",
    customer_id AS "客户ID",
    ROUND(initial_amount / 100000, 2) AS INITIALAMTIN_LAKHS,
    CASE
        WHEN initial_amount < 50000 THEN 'Low'
        WHEN initial_amount > 5000000 THEN 'High'
        ELSE 'Medium'
    END AS AMOUNT_CATEGORY,
    opening_date AS "起始日期",
    -- Pick the date function matching your database:
    -- MySQL/MariaDB:
    DATE_ADD(opening_date, INTERVAL tenure_months MONTH) AS MATURITY_DATE
    -- Oracle/PostgreSQL:
    -- ADD_MONTHS(opening_date, tenure_months) AS MATURITY_DATE
    -- PostgreSQL alternative:
    -- opening_date + (tenure_months || ' months')::interval AS MATURITY_DATE
FROM
    fd_accounts
WHERE
    opening_date >= '2004-01-01'
    AND status = 'ACTIVE';

Key Details Explained: #

• Filtering Rules: The WHERE clause locks in accounts opened on or after Jan 1, 2004, with an exact uppercase 'ACTIVE' status. If your status values might have mixed cases, swap status = 'ACTIVE' with UPPER(status) = 'ACTIVE' to avoid missing matches.
• INITIALAMTIN_LAKHS: Converts the initial amount to lakh units by dividing by 100,000 (1 lakh = 100k) and rounds to 2 decimal places using ROUND() for clean formatting.
• AMOUNT_CATEGORY: Uses a CASE statement to bucket amounts exactly as requested:
  • 'Low' for values under 50,000
  • 'High' for values over 5,000,000
  • 'Medium' for everything in between (including exact matches for 50k or 5M)
• MATURITY_DATE: Calculates the maturity date by adding the tenure (in months) to the opening date. Date functions vary by SQL dialect, so uncomment the line that works for your database.

Quick Adjustments to Keep in Mind: #

• Replace fd_accounts and column names (like tenure_months) with your actual table/field names if they differ.
• If opening_date includes time stamps, use DATE(opening_date) >= '2004-01-01' to ignore time and filter correctly.

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