September 21, 2018 at 1:15 pm
I have an SP I came across that has a long case statement (posted below is about a quarter of the options). Was asked to move those values into a table. I'm not sure how a table would work with <= values to get the result. Any suggestions?
SELECT
case
when cvehicle_purchase_price <= 40000 and cdeductible_amount = 0 and sp.splan_desc like '%500%standard%' then 'P'
when cvehicle_purchase_price <= 75000 and cdeductible_amount = 0 and sp.splan_desc like '%500%standard%' then 'Q'
when cvehicle_purchase_price <= 150000 and cdeductible_amount = 0 and sp.splan_desc like '%500%standard%' then 'R'
when cvehicle_purchase_price <= 40000 and cdeductible_amount = 0 and sp.splan_desc like '%1000%standard%' then 'D'
when cvehicle_purchase_price <= 75000 and cdeductible_amount = 0 and sp.splan_desc like '%1000%standard%' then 'E'
when cvehicle_purchase_price <= 150000 and cdeductible_amount = 0 and sp.splan_desc like '%1000%standard%' then 'F'
when cvehicle_purchase_price <= 40000 and cdeductible_amount = 0 and sp.splan_desc like '%500%open%' then 'V'
when cvehicle_purchase_price <= 75000 and cdeductible_amount = 0 and sp.splan_desc like '%500%open%' then 'W'
when cvehicle_purchase_price <= 150000 and cdeductible_amount = 0 and sp.splan_desc like '%500%open%' then 'X'
end AS PlanId,
sc.scontract_no as MemberNumber,
sc.dtcontract_effect as EffectiveDate
FROM scs_auto_eds.dbo.scs_contracts sc
September 21, 2018 at 2:10 pm
Something along these lines perhaps...
CREATE TABLE dbo.scs_plan_code_parameters (
plan_id CHAR(1) NOT NULL,
min_price_range MONEY NOT NULL,
max_price_range MONEY NOT NULL,
cdeductible_amount MONEY NOT NULL
CONSTRAINT df_scsplancodeparameters_cdeductibleamount
DEFAULT (0),
splan_desc VARCHAR(20) NOT NULL,
CONSTRAINT pk_scsplancodeparameters
PRIMARY KEY CLUSTERED (plan_id)
);
GO
INSERT dbo.scs_plan_code_parameters (plan_id, min_price_range, max_price_range, cdeductible_amount, splan_desc) VALUES
('P', 0, 40000 0,'%500%standard%'),
('Q', 40000.01, 75000, 0,'%500%standard%'),
('R', 75000.01, 150000, 0,'%500%standard%'),
('D', 0, 40000, 0, '%1000%standard%'),
('E', 40000.01, 75000, 0,'%1000%standard%'),
('F', 75000.01, 150000, 0, '%1000%standard%'),
('V', 0, 40000, 0,'%500%open%'),
('W', 40000.01, 75000, 0,'%500%open%'),
('X', 75000.01, 150000, 0,'%500%open%');
GO
--=====================================================================
SELECT
spcp.PlanId,
sc.scontract_no as MemberNumber,
sc.dtcontract_effect as EffectiveDate
FROM
scs_auto_eds.dbo.scs_contracts sc
LEFT JOIN dbo.scs_plan_code_parameters spcp
ON sc.cvehicle_purchase_price BETWEEN spcp.min_price_range AND spcp.max_price_range
AND sc.cdeductible_amount = spcp.cdeductible_amount
AND sc.splan_desc LIKE spcp.splan_desc;
September 21, 2018 at 2:30 pm
I used a slightly different approach. The problem with the previous approach is that it uses closed intervals (both end points included) when you may need to use half open intervals (one end point included, the other excluded). So, the previous approach will miss values between 40000 and 40000.01 which may or may not be acceptable.
CREATE TABLE #Plans(
Plan_ID CHAR(1) NOT NULL PRIMARY KEY,
Max_Purchase_Price MONEY NOT NULL,
Plan_Description_Template VARCHAR(25) NOT NULL
)
INSERT #Plans(Plan_ID, Max_Purchase_Price, Plan_Description_Template)
VALUES('P', 40000,'%500%standard%'),
('Q', 75000,'%500%standard%'),
('R', 150000,'%500%standard%'),
('D', 40000,'%1000%standard%'),
('E', 75000,'%1000%standard%'),
('F', 150000,'%1000%standard%'),
('V', 40000,'%500%open%'),
('W', 75000,'%500%open%'),
('X', 150000,'%500%open%')
SELECT
p.Plan_ID,
SCHEMA_ID.scontract_no AS MemberNumber,
SCHEMA_ID.dtconract_effect AS EffectiveDate
FROM scs_auto_eds.dbo.scs_contracts sc
OUTER APPLY
(
SELECT TOP(1) p.Plan_ID
FROM #Plans p
WHERE sc.splan_desc LIKE p.Plan_Description_Template
AND cdeductible_amount = 0
AND cvehicle_purchase_price <= p.Max_Purchase_Price
ORDER BY p.Max_Purchase_Price
) p
Drew
Forgot to include the ORDER BY. It should also have an index on Max_Purchase_Price.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 21, 2018 at 3:24 pm
drew.allen - Friday, September 21, 2018 2:30 PMI used a slightly different approach. The problem with the previous approach is that it uses closed intervals (both end points included) when you may need to use half open intervals (one end point included, the other excluded). So, the previous approach will miss values between 40000 and 40000.01 which may or may not be acceptable.
That was my initial thought too. I opted to go with the closed intervals for two reasons #1) it adoided the need for an unnessary sort operation #2) It allowed for simpler syntax that more people would likely be comfortable with,
Of course, as you pointed out, the proper index should be able to eliminate the sort operation in the execution plan and encapsulating the logic in an iTFV would make for easy to use syntax...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply