如何在现有SQL查询中添加MTD、YTD、QTD维度的NetRentals计算

现有SQL查询运行正常，需为DT、YTD、QTD维度添加NetRentals计算，公式为：
NetRentals = MoveIns - MoveOuts + Transfers + MoveOutUndo + NonRevenueMI

原SQL代码 #

DECLARE @EndDate As Date
DECLARE @Props as int
 
SELECT
    'Move-Ins' strType,
    IsNull(SUM(CASE WHEN dtReport = @EndDate THEN intMoveIn ELSE 0 END),0) AS intDT,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfMonth AND @EndDate THEN intMoveIn ELSE 0 END),0) AS intMTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfQuarter AND @EndDate THEN intMoveIn ELSE 0 END),0) AS intQTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfYear AND @EndDate THEN intMoveIn ELSE 0 END),0) AS intYTD
FROM tblUBMReport WITH (NOLOCK)
INNER JOIN tblDate WITH (NOLOCK) ON tblDate.dtDate = @EndDate
WHERE intProp IN (@Props) AND dtReport BETWEEN @StartDate AND @EndDate
    
UNION

SELECT
    'Move-Outs' AS strType,
    IsNull(SUM(CASE WHEN dtReport = @EndDate THEN intMoveOut ELSE 0 END),0) AS intDT,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfMonth AND @EndDate THEN intMoveOut ELSE 0 END),0) AS intMTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfQuarter AND @EndDate THEN intMoveOut ELSE 0 END),0) AS intQTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfYear AND @EndDate THEN intMoveOut ELSE 0 END),0) AS intYTD
FROM tblUBMReport WITH (NOLOCK)
INNER JOIN tblDate WITH (NOLOCK) ON tblDate.dtDate = @EndDate
WHERE intProp IN (@Props) AND dtReport BETWEEN @StartDate AND @EndDate
    
UNION

SELECT
    'Transfers' AS strType,
    IsNull(SUM(CASE WHEN dtReport = @EndDate THEN intTransfer ELSE 0 END),0) AS intDT,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfMonth AND @EndDate THEN intTransfer ELSE 0 END),0) AS intMTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfQuarter AND @EndDate THEN intTransfer ELSE 0 END),0) AS intQTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfYear AND @EndDate THEN intTransfer ELSE 0 END),0) AS intYTD
FROM tblUBMReport WITH (NOLOCK)
INNER JOIN tblDate WITH (NOLOCK) ON tblDate.dtDate = @EndDate
WHERE intProp IN (@Props) AND dtReport BETWEEN @StartDate AND @EndDate
    
UNION

SELECT
    'Non-Revenue Move_Ins' AS strType,
    ISNULL(SUM(CASE WHEN dtReport = @EndDate THEN intNonRevenueMI ELSE 0 END),0) AS intDT,
    ISNULL(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfMonth AND @EndDate THEN intNonRevenueMI ELSE 0 END),0) AS intMTD,
    ISNUll(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfQuarter AND @EndDate THEN intNonRevenueMI ELSE 0 END),0) AS intQTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfYear AND @EndDate THEN intNonRevenueMI ELSE 0 END),0) AS intYTD
FROM tblUBMReport WITH (NOLOCK)
INNER JOIN tblDate WITH (NOLOCK) ON tblDate.dtDate = @EndDate
WHERE intProp IN (@Props) AND dtReport BETWEEN @StartDate AND @EndDate
    
UNION

SELECT
    'Move_out Undo' AS strType,
    IsNull(SUM(CASE WHEN dtReport = @EndDate THEN intNonRevenueMI ELSE 0 END),0) AS intDT,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfMonth AND @EndDate THEN intMoveOutUndo ELSE 0 END),0) AS intMTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfQuarter AND @EndDate THEN intMoveOutUndo ELSE 0 END),0) AS intQTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfYear AND @EndDate THEN intMoveOutUndo ELSE 0 END),0) AS intYTD
FROM tblUBMReport WITH (NOLOCK)
INNER JOIN tblDate WITH (NOLOCK) ON tblDate.dtDate = @EndDate
WHERE intProp IN (@Props) AND dtReport BETWEEN @StartDate AND @EndDate

修改后的SQL代码 #

DECLARE @EndDate As Date
DECLARE @Props as int
 
SELECT
    'Move-Ins' strType,
    IsNull(SUM(CASE WHEN dtReport = @EndDate THEN intMoveIn ELSE 0 END),0) AS intDT,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfMonth AND @EndDate THEN intMoveIn ELSE 0 END),0) AS intMTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfQuarter AND @EndDate THEN intMoveIn ELSE 0 END),0) AS intQTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfYear AND @EndDate THEN intMoveIn ELSE 0 END),0) AS intYTD
FROM tblUBMReport WITH (NOLOCK)
INNER JOIN tblDate WITH (NOLOCK) ON tblDate.dtDate = @EndDate
WHERE intProp IN (@Props) AND dtReport BETWEEN @StartDate AND @EndDate
    
UNION

SELECT
    'Move-Outs' AS strType,
    IsNull(SUM(CASE WHEN dtReport = @EndDate THEN intMoveOut ELSE 0 END),0) AS intDT,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfMonth AND @EndDate THEN intMoveOut ELSE 0 END),0) AS intMTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfQuarter AND @EndDate THEN intMoveOut ELSE 0 END),0) AS intQTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfYear AND @EndDate THEN intMoveOut ELSE 0 END),0) AS intYTD
FROM tblUBMReport WITH (NOLOCK)
INNER JOIN tblDate WITH (NOLOCK) ON tblDate.dtDate = @EndDate
WHERE intProp IN (@Props) AND dtReport BETWEEN @StartDate AND @EndDate
    
UNION

SELECT
    'Transfers' AS strType,
    IsNull(SUM(CASE WHEN dtReport = @EndDate THEN intTransfer ELSE 0 END),0) AS intDT,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfMonth AND @EndDate THEN intTransfer ELSE 0 END),0) AS intMTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfQuarter AND @EndDate THEN intTransfer ELSE 0 END),0) AS intQTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfYear AND @EndDate THEN intTransfer ELSE 0 END),0) AS intYTD
FROM tblUBMReport WITH (NOLOCK)
INNER JOIN tblDate WITH (NOLOCK) ON tblDate.dtDate = @EndDate
WHERE intProp IN (@Props) AND dtReport BETWEEN @StartDate AND @EndDate
    
UNION

SELECT
    'Non-Revenue Move_Ins' AS strType,
    ISNULL(SUM(CASE WHEN dtReport = @EndDate THEN intNonRevenueMI ELSE 0 END),0) AS intDT,
    ISNULL(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfMonth AND @EndDate THEN intNonRevenueMI ELSE 0 END),0) AS intMTD,
    ISNUll(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfQuarter AND @EndDate THEN intNonRevenueMI ELSE 0 END),0) AS intQTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfYear AND @EndDate THEN intNonRevenueMI ELSE 0 END),0) AS intYTD
FROM tblUBMReport WITH (NOLOCK)
INNER JOIN tblDate WITH (NOLOCK) ON tblDate.dtDate = @EndDate
WHERE intProp IN (@Props) AND dtReport BETWEEN @StartDate AND @EndDate
    
UNION

SELECT
    'Move_out Undo' AS strType,
    IsNull(SUM(CASE WHEN dtReport = @EndDate THEN intMoveOutUndo ELSE 0 END),0) AS intDT,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfMonth AND @EndDate THEN intMoveOutUndo ELSE 0 END),0) AS intMTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfQuarter AND @EndDate THEN intMoveOutUndo ELSE 0 END),0) AS intQTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfYear AND @EndDate THEN intMoveOutUndo ELSE 0 END),0) AS intYTD
FROM tblUBMReport WITH (NOLOCK)
INNER JOIN tblDate WITH (NOLOCK) ON tblDate.dtDate = @EndDate
WHERE intProp IN (@Props) AND dtReport BETWEEN @StartDate AND @EndDate

UNION

SELECT
    'NetRentals' AS strType,
    -- DT维度计算
    IsNull(SUM(CASE WHEN dtReport = @EndDate THEN intMoveIn - intMoveOut + intTransfer + intMoveOutUndo + intNonRevenueMI ELSE 0 END),0) AS intDT,
    -- MTD维度保持原逻辑（可选，不需要可移除）
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfMonth AND @EndDate THEN intMoveIn - intMoveOut + intTransfer + intMoveOutUndo + intNonRevenueMI ELSE 0 END),0) AS intMTD,
    -- QTD维度计算
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfQuarter AND @EndDate THEN intMoveIn - intMoveOut + intTransfer + intMoveOutUndo + intNonRevenueMI ELSE 0 END),0) AS intQTD,
    -- YTD维度计算
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfYear AND @EndDate THEN intMoveIn - intMoveOut + intTransfer + intMoveOutUndo + intNonRevenueMI ELSE 0 END),0) AS intYTD
FROM tblUBMReport WITH (NOLOCK)
INNER JOIN tblDate WITH (NOLOCK) ON tblDate.dtDate = @EndDate
WHERE intProp IN (@Props) AND dtReport BETWEEN @StartDate AND @EndDate

修改说明 #

1. 新增NetRentals分支，按照公式分别计算DT、QTD、YTD维度的数值，同时保留了MTD维度的计算（不需要可直接删除对应字段）。
2. 修正了原查询中"Move_out Undo"分支的intDT字段错误：原代码错误引用intNonRevenueMI，改为正确的intMoveOutUndo。

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