计算每个sessionID的税

Question

我有以下表格"Customer_Trans"并包含以下列:

tbody> <<tr>

日期	CustomerID	SessionID	金额
9/9/2020	485789	23	0, 4
9/9/2020	485789	24	620
9/9/2020	485789	25	60

试着运行这个

DROP TABLE IF EXISTS #Customer_Trans
CREATE TABLE #Customer_Trans
(
[Date] date NULL,
CustomerID int NULL,
SessionID INT NULL,
Amount DECIMAL(18,4) NULL,
Tier1 DECIMAL(18,4) NULL,
Tier2 DECIMAL(18,4) NULL
)
INSERT INTO #Customer_Trans (Date,CustomerID,SessionID,Amount)
VALUES('9/9/2020',485789,23,0.4)
,('9/9/2020',485789,24,620)
,('9/9/2020',485789,25,60)
,('9/9/2020',485789,26,160.94)
,('9/9/2020',485789,27,502.24)
SELECT 
Date,CustomerID,SessionID,Amount,
CASE WHEN Amount > 500 THEN (.15*400)+(.2*(Amount-500)) ELSE 0 END AS Tier1,
CASE WHEN Amount > 100 AND Amount<500.0001 THEN (.15*(Amount-100)) ELSE 0 END AS Tier2
FROM #Customer_Trans

作为旁注，在你的描述中，我认为你有一个缺陷，因为620的交易将是400 * 15% + 120 * 20%因为您指定的税级是100 &因此，15%只吸引100.01到500.00之间的价值，这是620的金额，最后的120是20%的等级。