How to add revenue calculation to existing query for MTD,YTD and QTD

Question

I have a query that works perfectly but I need somehow need to add a calculation of the following for DT,YTD,QTD. The calculation has to be NetRentals = result.MoveIns - result.MoveOuts + result.Transfers + result.MoveOutUndo + result.NonRevenueMI;. But I need to have it for DT,YTD,QTD.

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

Answer 1

If you're using SSRS, you would do this in the table and not the query.

Normally you would use =SUM(Fields!intDT.Value) but a different calcualtion is needed.

NetRentals = result.MoveIns - result.MoveOuts + result.Transfers + result.MoveOutUndo + result.NonRevenueM

Since the fields you sum are the same but add or subtract based on strType:

=SUM(Fields!intDT.Value * IIF(Fields!strType.Value = "Move-Outs", -1, 1))

Of course the intDT would need to be changed for the other date ranges.