October 1, 2015 at 8:54 am
Is there any way to reduce this code??
SELECT
N.insuredID
,CASE
WHEN N.creditScore BETWEEN '0' AND '565' THEN 1.350
WHEN N.creditScore BETWEEN '566' AND '602' THEN 1.285
WHEN N.creditScore BETWEEN '603' AND '657' THEN 1.225
WHEN N.creditScore BETWEEN '658' AND '693' THEN 1.150
WHEN N.creditScore BETWEEN '694' AND '727' THEN 1.075
WHEN N.creditScore BETWEEN '728' AND '757' THEN 1.000
WHEN N.creditScore BETWEEN '758' AND '787' THEN 0.925
WHEN N.creditScore BETWEEN '788' AND '819' THEN 0.850
WHEN N.creditScore BETWEEN '820' AND '849' THEN 0.775
WHEN N.creditScore BETWEEN '850' AND '880' THEN 0.715
WHEN N.creditScore BETWEEN '881' AND '997' THEN 0.650
WHEN N.creditScore > 997 THEN 0.950
END
,CASE
WHEN C.creditScore BETWEEN '0' AND '565' THEN 1.350
WHEN C.creditScore BETWEEN '566' AND '602' THEN 1.285
WHEN C.creditScore BETWEEN '603' AND '657' THEN 1.225
WHEN C.creditScore BETWEEN '658' AND '693' THEN 1.150
WHEN C.creditScore BETWEEN '694' AND '727' THEN 1.075
WHEN C.creditScore BETWEEN '728' AND '757' THEN 1.000
WHEN C.creditScore BETWEEN '758' AND '787' THEN 0.925
WHEN C.creditScore BETWEEN '788' AND '819' THEN 0.850
WHEN C.creditScore BETWEEN '820' AND '849' THEN 0.775
WHEN C.creditScore BETWEEN '850' AND '880' THEN 0.715
WHEN C.creditScore BETWEEN '881' AND '997' THEN 0.650
WHEN C.creditScore > 997 THEN 0.950
END
FROM #TmpNewCreditScore as N
INNER JOIN #TmpOldCreditScore as C on N.insuredID = C.insuredID
SELECT DISTINCT
I.fname1 + ' ' + I.lname1 as 'Insured Name'
,P.policyNum as 'Current Policy Number'
,CONVERT(VARCHAR,P.effectiveDate,101) as 'Effective Date'
,CASE
WHEN I.creditScore > I.creditScore2 THEN I.creditScore
WHEN I.creditScore < I.creditScore2 THEN I.creditScore2
END AS 'Credit Score Currently Used'
,CASE
WHEN I.creditScore > I.creditScore2 THEN
CASE
WHEN I.creditScore BETWEEN '0' AND '565' THEN 1.350
WHEN I.creditScore BETWEEN '566' AND '602' THEN 1.285
WHEN I.creditScore BETWEEN '603' AND '657' THEN 1.225
WHEN I.creditScore BETWEEN '658' AND '693' THEN 1.150
WHEN I.creditScore BETWEEN '694' AND '727' THEN 1.075
WHEN I.creditScore BETWEEN '728' AND '757' THEN 1.000
WHEN I.creditScore BETWEEN '758' AND '787' THEN 0.925
WHEN I.creditScore BETWEEN '788' AND '819' THEN 0.850
WHEN I.creditScore BETWEEN '820' AND '849' THEN 0.775
WHEN I.creditScore BETWEEN '850' AND '880' THEN 0.715
WHEN I.creditScore BETWEEN '881' AND '997' THEN 0.650
WHEN I.creditScore > 997 THEN 0.950
END
WHEN I.creditScore < I.creditScore2 THEN
CASE
WHEN I.creditScore2 BETWEEN '0' AND '565' THEN 1.350
WHEN I.creditScore2 BETWEEN '566' AND '602' THEN 1.285
WHEN I.creditScore2 BETWEEN '603' AND '657' THEN 1.225
WHEN I.creditScore2 BETWEEN '658' AND '693' THEN 1.150
WHEN I.creditScore2 BETWEEN '694' AND '727' THEN 1.075
WHEN I.creditScore2 BETWEEN '728' AND '757' THEN 1.000
WHEN I.creditScore2 BETWEEN '758' AND '787' THEN 0.925
WHEN I.creditScore2 BETWEEN '788' AND '819' THEN 0.850
WHEN I.creditScore2 BETWEEN '820' AND '849' THEN 0.775
WHEN I.creditScore2 BETWEEN '850' AND '880' THEN 0.715
WHEN I.creditScore2 BETWEEN '881' AND '997' THEN 0.650
WHEN I.creditScore2 > 997 THEN 0.950
END
END AS 'Credit Factor Currently Used'
,NCS.creditScore AS 'Latest Credit Score'
,CF.newCF AS 'Latest Credit Factor'
,CS.creditScore AS 'Oldest Credit Score'
,CF.oldCF AS 'Oldest Credit Factor'
FROM
Policy as P
INNER JOIN NCF_Request as N on P.policyID = N.policyID
INNER JOIN Insured as I on P.insuredID = I.insuredID
INNER JOIN #TmpInsured as T on P.insuredID = T.insuredID
INNER JOIN #TmpOldCreditScore as CS on P.insuredID = CS.insuredID
INNER JOIN #TmpNewCreditScore as NCS on P.insuredID = NCS.insuredID
INNER JOIN #TmpCF as CF on P.insuredID = CF.insuredID
WHERE
P.policyNum NOT LIKE 'Q%'
AND
(P.status = 0)
AND (CF.newCF > CF.oldCF OR CF.newCF < CF.oldCF)
GROUP BY I.fname1, I.lname1, P.policyNum, P.effectiveDate, NCS.creditScore, I.creditScore, I.creditScore2, CF.newCF, CS.creditScore, CF.oldCF
ORDER BY P.policyNum
October 1, 2015 at 9:10 am
You could put values into a table and do a lookup.
You could also use a CROSS APPLY to assign an alias name to the result of the first CASE statement, then use that alias name instead of having to repeat the entire expression.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 1, 2015 at 12:13 pm
Embedding lookup tables tends to result in complicated, repeated, hard to maintain, and inconsistent SQL code. Consider joining a meta-data reference table like so:
create table CreditScoreRate
(
constraint PK_CreditScoreRate primary key clustered ( LowerCreditScore ),
LowerCreditScore smallint not null,
UpperCreditScore smallint not null,
CreditRate numeric(6,3) not null
);
select I.InsuredID, R.CreditRate
from Insured as I
join CreditScoreRate as R
on I.CreditScore between R.LowerCreditScore and R.UpperCreditScore;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 1, 2015 at 1:20 pm
Is there any way to reduce this code??
Lose that ORDER BY clause; it seems like it's just for display - let display stuff get handled at the application level.
-- Itzik Ben-Gan 2001
October 1, 2015 at 3:41 pm
The quickest, most sure-fire way to reduce the code would be to use CTRL+A followed by CTRL+X. :w00t::w00t:
How about this for the first query?
DECLARE @NewCreditScores TABLE (insuredID INTEGER, creditScore INTEGER);
INSERT INTO @NewCreditScores (insuredID, creditScore)
VALUES (1, 575),
(2, 604),
(3, 750);
DECLARE @OldCreditScores TABLE (insuredID INTEGER, creditScore INTEGER);
INSERT INTO @OldCreditScores (insuredID, creditScore)
VALUES (1, 675),
(2, 600),
(3, 770);
WITH CreditScores AS
(
SELECT *
FROM (VALUES ( 0, 565, 1.350),
(566, 602, 1.285),
(603, 657, 1.225),
(658, 693, 1.150),
(694, 727, 1.075),
(728, 757, 1.000),
(758, 787, 0.925),
(788, 819, 0.850),
(820, 849, 0.775),
(850, 880, 0.715),
(881, 997, 0.650),
(997, 2000, 0.950)
) dt(StartRange, EndRange, RangeValue)
)
SELECT N.insuredID,
c1.RangeValue AS NewRangeValue,
c2.RangeValue AS OldRangeValue
FROM @NewCreditScores N
JOIN @OldCreditScores O ON N.insuredID = O.insuredID
JOIN CreditScores c1 ON N.creditScore BETWEEN c1.StartRange AND c1.EndRange
JOIN CreditScores c2 ON O.creditScore BETWEEN c2.StartRange AND c2.EndRange
Oh... see how I build some tables, and populated them with test data? Many of the volunteers here (yep, we are all volunteers helping out) just won't even start to help if you haven't done that as a minimum. Read the link in my signature for how to better post questions for getting good answers.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 2, 2015 at 6:04 am
WayneS (10/1/2015)
The quickest, most sure-fire way to reduce the code would be to use CTRL+A followed by CTRL+X. :w00t::w00t:
Good anwser.
I would like to make a little change.
Maybe it is not significant for the given problem, but might be significant for similar problems.
...*
FROM (VALUES ( 0, 566, 1.350), -- End value is not included
(566, 603, 1.285), --
(603, 658, 1.225), --
(658, ...
... , 998, 0.650),
(997, 2001, 0.950)
) dt(StartRange, EndRange, RangeValue)
:
:
JOIN CreditScores c1 ON N.creditScore >= c1.StartRange AND N.creditScore < c1.EndRange
JOIN CreditScores c2 ON O.creditScore >= c2.StartRange AND O.creditScore < c2.EndRange
In the Between construction the value 602.5 would not fall into any category.
With the adjusted script it falls in a category, which makes the script more robust.
(1000 <= distance < 2000) -- (all values in meters)
-- is equivalent to
(1 <= distance < 2) -- (all values in kilometers)
Even if the distances are converted to mm or miles or parsecs, the code still works. With the BETWEEN construction, the end distance has to be adjusted to the scale we are working on.
Especially Floats and Datetime constructions are vunarable with BETWEEN construct. Example:
(DAY BETWEEN @startdate and '2013-12-31 23:59:59.999'), which did not give the 'correct; result because 1 jan 2014 is included. Yes I have seen this actually used in code.
The BETWEEN construction is not scale invariant, where the ">= AND <" is scale invariant.
And an advantage might be that the starting value of the next category is always the same as the end value of the previous category. So it is possible to enter only the starting values of the ranges and derive the endpoints. This prevents that there are holes or overlaps in the ranges. Overlaps in the ranges are often detected because of the double appearance of the values, but holes are often missed because some value's go missing.
Ben
October 2, 2015 at 7:23 am
Thanks a Lot!!
October 2, 2015 at 8:36 am
ben.brugman (10/2/2015)
WayneS (10/1/2015)
The quickest, most sure-fire way to reduce the code would be to use CTRL+A followed by CTRL+X. :w00t::w00t:Good anwser.
I would like to make a little change.
Maybe it is not significant for the given problem, but might be significant for similar problems.
...*FROM (VALUES ( 0, 566, 1.350), -- End value is not included
(566, 603, 1.285), --
(603, 658, 1.225), --
(658, ...
... , 998, 0.650),
(997, 2001, 0.950)
) dt(StartRange, EndRange, RangeValue)
:
:
JOIN CreditScores c1 ON N.creditScore >= c1.StartRange AND N.creditScore < c1.EndRange
JOIN CreditScores c2 ON O.creditScore >= c2.StartRange AND O.creditScore < c2.EndRange
In the Between construction the value 602.5 would not fall into any category.
With the adjusted script it falls in a category, which makes the script more robust.
(1000 <= distance < 2000) -- (all values in meters)
-- is equivalent to
(1 <= distance < 2) -- (all values in kilometers)
Even if the distances are converted to mm or miles or parsecs, the code still works. With the BETWEEN construction, the end distance has to be adjusted to the scale we are working on.
Especially Floats and Datetime constructions are vunarable with BETWEEN construct. Example:
(DAY BETWEEN @startdate and '2013-12-31 23:59:59.999'), which did not give the 'correct; result because 1 jan 2014 is included. Yes I have seen this actually used in code.
The BETWEEN construction is not scale invariant, where the ">= AND <" is scale invariant.
And an advantage might be that the starting value of the next category is always the same as the end value of the previous category. So it is possible to enter only the starting values of the ranges and derive the endpoints. This prevents that there are holes or overlaps in the ranges. Overlaps in the ranges are often detected because of the double appearance of the values, but holes are often missed because some value's go missing.
Ben
Hi Ben,
Good advise here. The reason that I didn't do this is because the code is dealing with a credit score, and in my experience I've only seen credit scores as integers, so what I did would work. If the value isn't an integer, then I would go your route, and change the between to ">= StartRange and < EndRange" like you did.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply