sql - 工作 Access SQL 查询的 T-SQL 迁移，在编写 IIF 的 CASE 替换时出现问题

Question

我有两张 table BMReports_FPN_Curves 和 BMReports_BOA_Curves 每个都由名称、日期时间、期间和值组成，例如:

BM_UNIT_NAME   RunDate               Period  FPN (or BOA)
T_DRAXX-1      2010-12-01 00:03:00   1       497

RunDate 字段增加一分钟(每天约 1440 条记录)，Periods 为 1-48。在 BMReports_FPN_Curves 我有每个时间段的完整数据集和 BMReports_BOA_Curves 包含将替换这些基值的值。

中通常有重复的 BOA 值和嵌套的 IIF 语句。 Access SQL 语句包含一条规则，用于选择任意时间点的 FPN、最大 BOA 值或最小 BOA 值之一。该规则规定:

1)如果没有BOA值，使用FPN值
2)如果有一个BOA值并且它小于FPN，找到并使用Min BOA值
3)如果有一个BOA值并且它大于FPN，找到并使用Max BOA值

Access SQL 查询 工作完美，如下所示:

SELECT 
dbo_BMReports_FPN_Curves.BM_Unit_Name, 
dbo_BMReports_FPN_Curves.RunDate, 
dbo_BMReports_FPN_Curves.Period, 
dbo_BMReports_FPN_Curves.PN_Level, 

IIf(IIf(Min([dbo_BMReports_BOA_Curves]![PN_Level]) <[dbo_BMReports_FPN_Curves]![PN_Level],Min([dbo_BMReports_BOA_Curves]! [PN_Level]),Max([dbo_BMReports_BOA_Curves]![PN_Level])) Is Null, [dbo_BMReports_FPN_Curves]![PN_Level],
IIf(Min([dbo_BMReports_BOA_Curves]![PN_Level])<[dbo_BMReports_FPN_Curves]! [PN_Level],Min([dbo_BMReports_BOA_Curves]! [PN_Level]),Max([dbo_BMReports_BOA_Curves]![PN_Level]))) AS BOA

FROM dbo_BMReports_FPN_Curves LEFT JOIN dbo_BMReports_BOA_Curves ON  (dbo_BMReports_FPN_Curves.RunDate = dbo_BMReports_BOA_Curves.RunDate) AND  (dbo_BMReports_FPN_Curves.BM_Unit_Name = dbo_BMReports_BOA_Curves.BM_Unit_Name)

GROUP BY dbo_BMReports_FPN_Curves.BM_Unit_Name, dbo_BMReports_FPN_Curves.RunDate, dbo_BMReports_FPN_Curves.Period, dbo_BMReports_FPN_Curves.PN_Level

HAVING (((dbo_BMReports_FPN_Curves.BM_Unit_Name)='T_DRAXX-1'));

我已经在 T-SQL 中重写了大部分查询(查询相同的 SQL Server 数据源)，并且 LEFT JOIN、GROUP BY 和 HAVING 元素都可以正常工作，但是我陷入了 CASE WHEN 替换 IFF 的问题如果有人有空闲时间，我会非常感激。

目前的 SQL 查询:

SELECT 
BMReports_FPN_Curves.BM_Unit_Name, 
BMReports_FPN_Curves.RunDate, 
BMReports_FPN_Curves.Period,
AVG(BMReports_FPN_Curves.PN_Level) AS FPN,

    CASE
      WHEN BMReports_BOA_Curves.PN_Level IS NULL THEN AVG(BMReports_FPN_Curves.PN_Level)
      WHEN MIN(BMReports_BOA_Curves.PN_Level) IS <  AVG(BMReports_FPN_Curves.PN_Level) THEN MIN(BMReports_BOA_Curves.PN_Level)
      ELSE MAX(BMReports_BOA_Curves.PN_Level)
    END AS BOA

FROM BMReports_FPN_Curves 
  LEFT JOIN BMReports_BOA_Curves ON BMReports_FPN_Curves.BM_Unit_Name = BMReports_BOA_Curves.BM_Unit_Name
  AND  BMReports_FPN_Curves.RunDate = BMReports_BOA_Curves.RunDate

GROUP BY BMReports_FPN_Curves.BM_Unit_Name, BMReports_FPN_Curves.RunDate, BMReports_FPN_Curves.Period
HAVING BMReports_FPN_Curves.BM_Unit_Name = 'T_DRAXX-1'
ORDER BY BMReports_FPN_Curves.BM_Unit_Name, BMReports_FPN_Curves.RunDate, BMReports_FPN_Curves.Period

Answer 1

SELECT  fc.BM_Unit_Name
        , fc.RunDate
        , fc.Period
        , CASE 
          WHEN AVG(bc.PN_Level) IS NULL THEN AVG(fc.PN_Level)             -- No BOA Value, use the FPN Value
          WHEN MIN(bc.PN_Level) < AVG(fc.PN_Level) THEN MIN(bc.PN_Level) -- BOA Value is less than the FPN, use the BOA Value
          ELSE MAX(bc.PN_Level)                                          -- BOA Value is greater than the FPN, use the BOA Value
          END 
FROM    dbo.BMReports_FPN_Curves fc
        LEFT JOIN dbo.BMReports_BOA_Curves bc ON fc.RunDate = bc.RunDate        
                                                 AND fc.BM_Unit_Name = bc.BM_Unit_Name
WHERE   fc.BM_Unit_Name ='T_DRAXX-1'
GROUP BY
        fc.BM_Unit_Name
        , fc.RunDate
        , fc.Period