Got it, let's work through this SQL Server query problem. First, let's restate your requirements clearly to make sure we're aligned:

• For groups where ROWID = 1: Grab the last non-NULL KPI value (ordered by the Date column to ensure we get the most recent valid entry)
• For groups where ROWID = 2: Calculate the average of all non-NULL KPI values in the group

Sample Dataset #

Expected Output (for ROWID=1 group) #

SQL Query Implementation #

We can handle both ROWID cases in a single query using a combination of window functions and conditional aggregation. Here's the solution:

WITH RankedValidKPIs AS (
    SELECT 
        Name,
        ROWID,
        Type,
        Company,
        KPI,
        -- Rank valid (non-NULL) KPIs by date descending for ROWID=1 groups
        ROW_NUMBER() OVER (
            PARTITION BY Name, ROWID, Type, Company 
            ORDER BY Date DESC
        ) AS ValidKPI_Rank
    FROM YourTableName
    WHERE KPI IS NOT NULL -- Ignore NULL values for valid KPI ranking
)
SELECT 
    Name,
    ROWID,
    Type,
    Company,
    CASE 
        WHEN ROWID = 1 THEN 
            -- Grab the top-ranked (most recent) valid KPI
            MAX(CASE WHEN ValidKPI_Rank = 1 THEN KPI END)
        WHEN ROWID = 2 THEN 
            -- Calculate average of all non-NULL KPIs in the group
            AVG(KPI)
    END AS Calculated_KPI
FROM RankedValidKPIs
GROUP BY Name, ROWID, Type, Company
-- Optional: Filter to only include the ROWIDs you care about
WHERE ROWID IN (1, 2);

Query Breakdown #

1. CTE RankedValidKPIs:

   • We first filter out NULL KPI values since they don't count as valid entries
   • Use ROW_NUMBER() to rank non-NULL KPIs in each group (partitioned by Name, ROWID, Type, Company) in descending order of Date—this gives the most recent valid KPI a rank of 1.

2. Main Query:

   • For ROWID=1, conditional aggregation picks the KPI where ValidKPI_Rank=1 (the last valid value)
   • For ROWID=2, we compute the average of all non-NULL KPIs in the group
   • Grouping by the key columns ensures we get one result per unique group

Alternative Approach (Correlated Subquery) #

If you prefer subqueries over CTEs, here's another way to get the same result:

SELECT 
    Name,
    ROWID,
    Type,
    Company,
    CASE 
        WHEN ROWID = 1 THEN (
            SELECT TOP 1 KPI 
            FROM YourTableName t2
            WHERE t2.Name = t1.Name 
              AND t2.ROWID = t1.ROWID 
              AND t2.Type = t1.Type 
              AND t2.Company = t1.Company
              AND t2.KPI IS NOT NULL
            ORDER BY t2.Date DESC
        )
        WHEN ROWID = 2 THEN AVG(t1.KPI)
    END AS Calculated_KPI
FROM YourTableName t1
WHERE ROWID IN (1, 2)
GROUP BY Name, ROWID, Type, Company;

This uses a correlated subquery to fetch the most recent non-NULL KPI for each ROWID=1 group, then calculates the average for ROWID=2 groups directly.

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