比较2列并返回SQL中的文本

Question

我想在传递状态值之后比较金额和最后金额值。(现在硬编码)

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

我的存储过程

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

过程输出

​

​

Answer 1

我想您只是想使用CASE表达式来比较LastAmount的数量，使用子查询或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);