SQL Case语句逻辑问题

Question

试图让逻辑在情况下的声明工作。如果第一个表中的Qty大于第二个表中的ALOS，我想运行这个计算:

DRG_Rate=DRG_Rate + ([GMC Claims 2019].Qty-BCBS_DRG_REIMBURS.ALOS)* BCBS_DRG_REIMBURS.Per_Diem_High_Trim_Outlier

我得到了连接工作，它正在返回DRG的匹配在两个表以及DRG_Rate。现在我想根据上面的条件语句调整DRG_Rate。如果你们还需要其他信息，请告诉我。我很感激任何指导，我是相当新的SQL!

*期望结果是:第二个表中的11513.43 + (15-8)2014.85 = 27,632.23

15为第一张表中的QTY, 8为第二张表中的ALOS, 2014.85为第二张表中的每日异常值* *

这是我的尝试:

SELECT 
CASE
DRG_Rate
WHEN [GMC Claims 2019].Qty > BCBS_DRG_REIMBURS.ALOS 
THEN DRG_Rate=DRG_Rate + ([GMC Claims 2019].Qty-BCBS_DRG_REIMBURS.ALOS)* BCBS_DRG_REIMBURS.Per_Diem_High_Trim_Outlier
FROM [GMC Claims 2019] LEFT JOIN BCBS_DRG_REIMBURS ON
[GMC Claims 2019].DRG=BCBS_DRG_REIMBURS.DRG_Code
WHERE [GMC Claims 2019].DRG=BCBS_DRG_REIMBURS.DRG_Code

And my tables:

CREATE TABLE [GMC Claims 2019] (

PatNo INT NOT NULL ,
PName VARCHAR(50) NOT NULL ,
HSSV VARCHAR(50) NOT NULL,
DateOfService INT,
InsName VARCHAR(50),
DRG INT,
RevCode INT,
CPT VARCHAR(50),
Qty INT  , 
BilledCharges DECIMAL (9,2) NOT NULL,

);

INSERT INTO [GMC Claims 2019] (PatNo, PName, HSSV, DateOfService, InsName, DRG, RevCode, CPT, Qty, BilledCharges)
VALUES (5, 'Hisham', 'SIP', 5, 'BCBS', 439, 344, '44', 15, 500), 
;
CREATE TABLE [BCBS DRG CARVE OUT 07012016] (

DRG INT NOT NULL ,
ServiceCat VARCHAR(50) NOT NULL ,
Pmt DECIMAL (8,2) NOT NULL       
);
INSERT INTO [BCBS DRG CARVE OUT 07012016] (DRG, ServiceCat, Pmt)
VALUES (28, 'DRG Carve Out', 13537),
(29, 'DRG Carve Out', 13537),
(30, 'DRG Carve Out', 13537),
(101, 'DRG Carve Out', 13537);

CREATE TABLE BCBS_DRG_REIMBURS(
DRG_Code                   INTEGER  NOT NULL PRIMARY KEY 
,Description                VARCHAR(200)
,DRG_Weight                 NUMERIC(8,5) NOT NULL
,ALOS                       INTEGER  NOT NULL
,High_Trim                  INTEGER  NOT NULL
,Effective_Date             DATE 
,DRG_Rate                   NUMERIC(9,2) NOT NULL
,Per_Diem_High_Trim_Outlier NUMERIC(8,2) NOT NULL
);

INSERT INTO BCBS_DRG_REIMBURS(DRG_Code,Description,DRG_Weight,ALOS,High_Trim,Effective_Date,DRG_Rate,Per_Diem_High_Trim_Outlier) VALUES (439,'DISORDERS OF PANCREAS EXCEPT MALIGNANCY W CC',0.86230,4,7,NULL,11513.43,2014.85);

Answer 1

我觉得你是有条件地向现有的DRG率添加一个值，所以我将其表示为通常为零的调整。不管怎样，你的问题主要是语法上的。

DRG_Rate +
CASE WHEN [GMC Claims 2019].Qty > BCBS_DRG_REIMBURS.ALOS 
THEN ([GMC Claims 2019].Qty - BCBS_DRG_REIMBURS.ALOS) * BCBS_DRG_REIMBURS.Per_Diem_High_Trim_Outlier
ELSE 0
END