Compare 2 columns and return text in SQL-CodePudding

I want to compare amount and last amount values after pass status value. (now hard coded)

if amount > Lastamount ='up',
if amount < Lastamount ='down'
if amount == Lastamount ='equal'

My stored procedure

ALTER PROC my
    @Year int = NULL,
    @Quarter int = NULL,
    @Month int = NULL
AS 
BEGIN 
    SELECT 'ASO' AS Name
        , 'Active Serviced Outlets' AS Description
        , 'up' AS status
        , CAST(COUNT(DISTINCT Outlet)/1.0 AS DECIMAL(9,2)) AS Amount    
        , (
            SELECT CAST(COUNT(DISTINCT Outlet)/1.0 AS DECIMAL(9,2)) AS Amount
            FROM SalesAndReturns_RPT
            WHERE ((Call_ActualStartDate >= DATEFROMPARTS(@Year, 1, 1) 
            AND Call_ActualStartDate < DATEFROMPARTS(@Year 1, 1, 1)) OR @Year IS NULL)
            AND (DATEPART(QUARTER, Call_ActualStartDate) = @Quarter OR @Quarter IS NULL)
            AND (DATEPART(MONTH,Call_ActualStartDate) = @Month OR @Month IS NULL)
        ) AS LastAmount
    FROM SalesAndReturns_RPT
    WHERE ((Call_ActualStartDate >= DATEFROMPARTS(@Year-1, 1, 1)
    AND Call_ActualStartDate < DATEFROMPARTS(@Year , 1, 1)) OR @Year IS NULL)
    AND (DATEPART(QUARTER, Call_ActualStartDate) = @Quarter OR @Quarter IS NULL)
    AND (DATEPART(MONTH,Call_ActualStartDate) = @Month OR @Month IS NULL)
    OPTION (RECOMPILE);
END

Procedure output

CodePudding user response：

I guess you just want to use a CASE expression to compare Amount with LastAmount, using either a sub-query or CTE.

WITH cte AS (
    SELECT 'ASO' AS Name
        , 'Active Serviced Outlets' AS Description
        -- , 'up' AS status
        , CAST(COUNT(DISTINCT Outlet)/1.0 AS DECIMAL(9,2)) AS Amount    
        , (
            SELECT CAST(COUNT(DISTINCT Outlet)/1.0 AS DECIMAL(9,2)) AS Amount
            FROM SalesAndReturns_RPT
            WHERE ((Call_ActualStartDate >= DATEFROMPARTS(@Year, 1, 1) 
            AND Call_ActualStartDate < DATEFROMPARTS(@Year 1, 1, 1)) OR @Year IS NULL)
            AND (DATEPART(QUARTER, Call_ActualStartDate) = @Quarter OR @Quarter IS NULL)
            AND (DATEPART(MONTH,Call_ActualStartDate) = @Month OR @Month IS NULL)
        ) AS LastAmount
    FROM SalesAndReturns_RPT
    WHERE ((Call_ActualStartDate >= DATEFROMPARTS(@Year-1, 1, 1)
    AND Call_ActualStartDate < DATEFROMPARTS(@Year , 1, 1)) OR @Year IS NULL)
    AND (DATEPART(QUARTER, Call_ActualStartDate) = @Quarter OR @Quarter IS NULL)
    AND (DATEPART(MONTH,Call_ActualStartDate) = @Month OR @Month IS NULL)
)
SELECT *
    , CASE WHEN Amount > Lastamount THEN 'up' WHEN Amount < Lastamount THEN 'down' ELSE 'equal' END
FROM cte
OPTION (RECOMPILE);