员工跨部门致数据重复，如何用SQL筛选当前所属部门员工？

Let's break this down into two key parts: fixing the ORA-00932 type mismatch error first, then ensuring we only pull data for agents' current departments.

1. Fix the ORA-00932 Inconsistent Datatypes Error #

The error tells us Oracle expected a DATE but got a NUMBER—this almost certainly stems from how your OracleConversion.ToOracleDate method is formatting dates. Looking at your provided OracleConversion code, I don't see a ToOracleDate implementation, but I do see ToOracleDateTime.

• Swap out ToOracleDate for ToOracleDateTime: Ensure your date parameters are wrapped in valid Oracle date syntax (like TO_DATE('2024-01-01', 'YYYY-MM-DD')). If ToOracleDate was returning a numeric representation of the date (e.g., Julian date), that's why you're getting the type mismatch.
• Verify join condition datatypes: Double-check that ALLOCATION_START and ALLOCATION_END in V_AGENT_ALLOCATION are actual DATE columns. If they're stored as strings, you'll need to convert them with TO_DATE first:

  TRUNC(kara.TIDSPUNKT) BETWEEN TO_DATE(age.ALLOCATION_START, 'YYYY-MM-DD') AND TO_DATE(age.ALLOCATION_END, 'YYYY-MM-DD')
  

2. Filter for Agents' Current Department Data #

Since agents have duplicate records for past departments, we need to add logic to only keep their active (current) department assignments. Most systems mark active records in one of two ways:

• ALLOCATION_END is NULL (no end date means the assignment is still active)
• ALLOCATION_END is set to a far-future date (like DATE '9999-12-31')

Here's how to update your JOIN clause to filter for active records:

LEFT JOIN 
KS_DRIFT.V_AGENT_ALLOCATION age 
ON 
    -- Your existing agent match logic
    (queryParams.JoinOnFirstAgent ? "F脴RSTE_AGENT" : "SIDSTE_AGENT") = age.AGENT_INITIALS 
    -- Ensure the survey date falls within the assignment period
    AND TRUNC(kara.TIDSPUNKT) BETWEEN age.ALLOCATION_START AND NVL(age.ALLOCATION_END, SYSDATE)
    -- Only keep active assignments
    AND (age.ALLOCATION_END IS NULL OR age.ALLOCATION_END >= SYSDATE)

If your system uses a far-future date for active records, replace the last line with:

AND age.ALLOCATION_END = DATE '9999-12-31'

Full Modified SQL Code #

Putting it all together, your updated C# SQL generation code would look like this:

var sql = "SELECT " + 
    "SP脴RGSM脜L_ID, " + 
    "KARAKTER, " + 
    "COUNT(*) AS COUNT " + 
    "FROM " + 
    "KS_DRIFT.KT_KARAKTER kara " + 
    "LEFT JOIN " + 
    "KS_DRIFT.KT_BESVARELSE besv ON kara.BESVARELSE_ID = besv.EKSTERN_ID AND kara.TYPE = besv.TYPE " + 
    "LEFT JOIN " + 
    "KS_DRIFT.V_AGENT_ALLOCATION age ON " + 
        (queryParams.JoinOnFirstAgent ? "F脴RSTE_AGENT" : "SIDSTE_AGENT") + " = age.AGENT_INITIALS " + 
        "AND TRUNC(kara.TIDSPUNKT) BETWEEN age.ALLOCATION_START AND NVL(age.ALLOCATION_END, SYSDATE) " + 
        "AND (age.ALLOCATION_END IS NULL OR age.ALLOCATION_END >= SYSDATE) " + 
    "WHERE " + 
    "TRUNC(kara.TIDSPUNKT) BETWEEN " + OracleConversion.ToOracleDateTime(queryParams.Interval.Lower) + " AND " + OracleConversion.ToOracleDateTime(queryParams.Interval.Upper) + 
    " AND " + 
    "SP脴RGSM脜L_ID = " + queryParams.QuestionId + 
    (!queryParams.IncludeCDNs.IsNullOrEmpty() ? "AND CDN IN (" + queryParams.IncludeCDNs.ToDelimitedString(", ") + ") " : "") + 
    (!queryParams.ExcludeCDNs.IsNullOrEmpty() ? "AND CDN NOT IN (" + queryParams.ExcludeCDNs.ToDelimitedString(", ") + ") " : "") + 
    (!queryParams.AgentIds.IsNullOrEmpty() ? " AND AGENT_ID IN (" + queryParams.AgentIds.ToDelimitedString(", ") + ") " : "") + 
    (!queryParams.TeamIds.IsNullOrEmpty() ? " AND TEAM_ID IN (" + queryParams.TeamIds.ToDelimitedString(", ") + ") " : "") + 
    "GROUP BY " + 
    "SP脴RGSM脜L_ID, " + 
    "KARAKTER";

Quick Additional Checks #

• Confirm AGENT_INITIALS is a unique identifier for agents (if not, add AGENT_ID to the JOIN condition to avoid matching duplicate initials).
• If TRUNC(TIDSPUNKT) causes performance issues, consider creating a function index on TRUNC(TIDSPUNKT) or adjusting the date condition to avoid truncation:

  kara.TIDSPUNKT BETWEEN :startDate AND :endDate + INTERVAL '1' DAY - INTERVAL '1' SECOND
  

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