我想在通過狀態值后比較金額和最后金額值。（現在硬編碼）
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
程序輸出
uj5u.com熱心網友回復：
我猜你只是想使用一個CASE運算式來比較 Amount 和 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);
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/454761.html
上一篇：嘗試使用CTE將代碼替換為名稱
下一篇：SQL組通過忽略通配符“%”