# I need to calculate premium/innsurance by looking into the car value/ cost of the car.

I need to calculate  Innsurance by looking into the car value/ cost of the car.
I have a table, which has the columns COMPID, Min_value, Max_Value, Rate, Type, these are the columns which I have in the table name 'Rate_tiers'.  The values for it are as follows:
Create Table RATE_TIES
(
COMPID INT ,
MINIMUN INT ,
MAXIMUM INT,
VALUE INT,
XTYPE VARCHAR(30)
)
----------------------------------------------------------------------
INSERT INTO DBO.RATE_TIES
VALUES(101,1,1000,10,'Flat')
INSERT INTO DBO.RATE_TIES
VALUES(101,1001,10000,2,'PERCENT')
INSERT INTO DBO.RATE_TIES
VALUES(101,10001,100000,1.5,'PERCENT')
INSERT INTO DBO.RATE_TIES
VALUES(101,100001,10000000,1,'PERCENT')
------------------------------------------------------------------------
As mentioned this is the table structure and values in it.
Now, I will explain in detail, how I am calculating the Insurance,
Firstly, I need to see whether the car Value/ car price is greater than Max_Value column of first row say (if(Carvalue>Max_value))if yes then it looks for the Xtype that is whether Flat or percent, if it is percent then it calculates the percent with rate of that row, if it is flat, then it directly gives that value. After that it will subtract the amount of max_value from carvalue and goes to second column, say if carvalue is 15000 and max_value is 1000, then it will make the car value as 14000. If the carvalue is less than Max_value then, it should calculate the percentage to the given rate on carvalue amount or give the flat value directly in that column. In this way I should calculate the Insurance for the carvalue.I have made a code and wrapped that into stored procedured, but I am not getting the accurate results,have a look
CREATE PROCEDURE RATETIESINSURANCE
@CARVALUE FLOAT ,
@COMPID1 FLOAT
AS

BEGIN
DECLARE @ID FLOAT
SET @ID=@COMPID1
DECLARE @MAX1 FLOAT
DECLARE @MIN1 FLOAT
DECLARE @VALUE FLOAT
DECLARE @TYPE1  VARCHAR(20)
DECLARE @INSURANCE FLOAT
SET @INSURANCE=0
DECLARE C SCROLL CURSOR  FOR
SELECT COMPID,MINIMUM,MAXIMUM,VALUE,XTYPE FROM DBO.RATE_TIES  WHERE COMPID = @ID
open   C
FETCH NEXT FROM C into @ID,@MIN1,@MAX1,@VALUE,@TYPE1

WHILE(@@FETCH_STATUS = 0)
begin
FETCH NEXT FROM C into @ID,@MIN1,@MAX1,@VALUE,@TYPE1
SELECT @ID =COMPID , @MIN1 =MINIMUM, @MAX1= MAXIMUM , @VALUE = VALUE, @TYPE1=XTYPE FROM DBO.RATE_TIES WHERE COMPID = @ID
if(@CARVALUE>@MAX1)
BEGIN
IF(@TYPE1='PERENT')

BEGIN
SET @CARVALUE=@CARVALUE-@MAX1
SET @INSURANCE=@INSURANCE+(@VALUE/100)*@MAX1
END

ELSE
BEGIN
SET @CARVALUE=@CARVALUE-@MAX1
SET @INSURANCE=@INSURANCE+(@value)
END
END
ELSE
BEGIN
IF(@TYPE1='PERENT')

BEGIN
SET @CARVALUE=@MAX1-@CARVALUE
SET @INSURANCE=@INSURANCE+(@VALUE/100)*@CARVALUE
END

ELSE
BEGIN
SET @INSURANCE=@INSURANCE+(@value)
END
END

PRINT 'TOTAL INSURANCE = ' + CAST( @INSURANCE AS VARCHAR(30))
close c
end
deallocate c
END
------------------------------------------------------------------

For Execute:

--------------------------------------------------------------------------------------------------------------------

This is the code I have, I need to do this only by using cursors, because that is what I have been said to do, but it is giving some errors, we can wrap the code into function, as it returns a value, if anyone could help in this I would be thankful.
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The first thing that catches my eye is...

FETCH NEXT FROM C into @ID,@MIN1,@MAX1,@VALUE,@TYPE1
SELECT @ID =COMPID , @MIN1 =MINIMUM, @MAX1= MAXIMUM , @VALUE = VALUE, @TYPE1=XTYPE FROM DBO.RATE_TIES WHERE COMPID = @ID

Why are you fetching the data and immediately writing over it?
0
Author Commented:
Hi BriCrowe,
I am completely new to this tech, I have build this code by someones help, correct me, if I am wrong in code, as I want the result as stated in my topic description. Please help me in this.

Thanks,
Aparanjit
0
I'm not in a place where i can really work on it but when I get back to the office in the morning i'll work through the code you provided.
0
Author Commented:
Sure
0
Try this one procedure...it is still only printing out the data instead of returning it but let's get the logic verified first.

``````CREATE PROCEDURE RATETIESINSURANCE
@CARVALUE FLOAT ,
@COMPID1 FLOAT
AS

--DECLARE @CARVALUE FLOAT,
--	@COMPID1 FLOAT

--SELECT @COMPID1 = 101,
--	@CARVALUE = 15000

BEGIN
DECLARE @ID FLOAT
SET @ID=@COMPID1
DECLARE @MAX1 FLOAT
DECLARE @MIN1 FLOAT
DECLARE @VALUE FLOAT
DECLARE @TYPE1  VARCHAR(20)
DECLARE @INSURANCE FLOAT
SET @INSURANCE=0

DECLARE C SCROLL CURSOR  FOR
SELECT COMPID,MINIMUM,MAXIMUM,VALUE,XTYPE
FROM dbo.RATE_TIES
WHERE COMPID = @ID
ORDER BY COMPID, MINIMUM

OPEN C
FETCH NEXT FROM C INTO @ID, @MIN1, @MAX1, @VALUE, @TYPE1

WHILE(@@FETCH_STATUS = 0)
BEGIN
SELECT @CARVALUE =
CASE
WHEN @CARVALUE > @MAX1 AND @TYPE1 = 'PERCENT' THEN @CARVALUE - @MAX1
ELSE @CARVALUE
END

SELECT @INSURANCE =
CASE @TYPE1
WHEN 'PERCENT' THEN @INSURANCE + (@VALUE / 100) * @CARVALUE
WHEN 'FLAT' THEN @INSURANCE + @VALUE
ELSE @INSURANCE
END

PRINT 'TOTAL INSURANCE = ' + CAST( @INSURANCE AS VARCHAR(30))
FETCH NEXT FROM C INTO @ID, @MIN1, @MAX1, @VALUE, @TYPE1
END

CLOSE C
DEALLOCATE C
END
``````
0
Author Commented:
Hi BriCrowe,

SELECT @CARVALUE =
CASE
WHEN @CARVALUE > @MAX1 AND @TYPE1 = 'PERCENT' THEN @CARVALUE - @MAX1
ELSE @CARVALUE
END
SELECT @INSURANCE =
CASE @TYPE1
WHEN 'PERCENT' THEN @INSURANCE + (@VALUE / 100) * @CARVALUE
WHEN 'FLAT' THEN @INSURANCE + @VALUE
ELSE @INSURANCE
END
In the above code, we are calculating the insurance only when the carvalue > max, but we should also calculate the insurance when carvalue < max, then we should calculate the percentage to the carvalue or give the flat value if it is flat.
Hope you got my logic.

example: In first row it is 1000 the max value then it will see
(carvalue>max) which is (15000>1000) true then it sees whether it is flat or percent, in our case it is flat value 10, so it returns 10, then it subtracts the max value from car value, (15000-1000) = 14000, now the carvalue is 14000, it looks for the second row in the table, in second row the max value is 10000, so it checks condition (carvalue>max)= (14000>10000) which is true, then it sees for percent or flat, it is percent of 2 from max value which is maxvalue*2%= 10000*2%= 200, now it subtracts max value from
carvalue= (14000-10000)=4000, now it looks into 3rd row, condition (carvalue>max) = (4000>100000) which is false, then it should calculate the percent or flat for the carvalue now instead of the max value, as the condition is false, say in 3rd row it is 1 percent, so it should be 1% of carvalue ,,  that is 4000*1%=40. So now we should sum all the insurances value we got from three rows, that is 10+200+40= 250, so we should get 250 as total insurance when we give 15000 as carvalue.
0
I think I got it this time...

``````CREATE PROCEDURE RATETIESINSURANCE
@CARVALUE FLOAT ,
@COMPID1 FLOAT
AS

--DECLARE @CARVALUE FLOAT,
--	@COMPID1 FLOAT

--SELECT @COMPID1 = 101,
--	@CARVALUE = 15000

BEGIN
DECLARE @ID FLOAT
SET @ID=@COMPID1
DECLARE @MAX1	FLOAT,
@MIN1		FLOAT,
@VALUE		FLOAT,
@TYPE1		VARCHAR(20),
@INSURANCE	FLOAT,
@EffValue	FLOAT
SET @INSURANCE=0

DECLARE C SCROLL CURSOR  FOR
SELECT COMPID,MINIMUM,MAXIMUM,VALUE,XTYPE
FROM dbo.RATE_TIES
WHERE COMPID = @ID
ORDER BY COMPID, MINIMUM

OPEN C
FETCH NEXT FROM C INTO @ID, @MIN1, @MAX1, @VALUE, @TYPE1

WHILE(@@FETCH_STATUS = 0)
BEGIN
SELECT @EffValue =
CASE
WHEN @CARVALUE > @MAX1 THEN @MAX1
ELSE @CARVALUE
END,
@CarValue =
CASE
WHEN @CARVALUE > @MAX1 THEN @CARVALUE - @MAX1
ELSE 0
END

SELECT @INSURANCE =
CASE @TYPE1
WHEN 'PERCENT' THEN @INSURANCE + (@VALUE / 100) * @EffValue
WHEN 'FLAT' THEN @INSURANCE + @VALUE
ELSE @INSURANCE
END

PRINT 'TOTAL INSURANCE = ' + CAST( @INSURANCE AS VARCHAR(30))
FETCH NEXT FROM C INTO @ID, @MIN1, @MAX1, @VALUE, @TYPE1
END

CLOSE C
DEALLOCATE C
END
``````
0
I did have a question.  I udnerstand that the insurance amount for a given row is prorated based on the MaxValue (effective car value for the current row) in the case of XTYPE = 'PERCENT' but what happens if XTYPE = 'FLAT'?  Is the flat amount prorated as well or is it applied in full?
0
Author Commented:
Hey Bricrowe,

I am still not getting the result, my output is showing as
TOTAL INSURANCE = 10
TOTAL INSURANCE = 10
TOTAL INSURANCE = 10
TOTAL INSURANCE = 10
0
Author Commented:
If the xtype is flat, then it should take the value of that row in full and add to insurance.
0
Try this one...I got the following output:

TOTAL INSURANCE = 10
TOTAL INSURANCE = 210
TOTAL INSURANCE = 250

with:

EXECUTE RATETIESINSURANCE @CARVALUE = 14000, @COMPID1 = 101

``````ALTER PROCEDURE RATETIESINSURANCE
@CARVALUE FLOAT ,
@COMPID1 FLOAT
AS

--DECLARE @CARVALUE FLOAT,
--	@COMPID1 FLOAT

--SELECT @COMPID1 = 101,
--	@CARVALUE = 15000

BEGIN
DECLARE @ID FLOAT
SET @ID=@COMPID1
DECLARE @MAX1	FLOAT,
@MIN1		FLOAT,
@VALUE		FLOAT,
@TYPE1		VARCHAR(20),
@INSURANCE	FLOAT,
@EffValue	FLOAT,
@LastMax	FLOAT
SET @INSURANCE=0

DECLARE C SCROLL CURSOR  FOR
SELECT COMPID,MINIMUM,MAXIMUM,VALUE,XTYPE
FROM dbo.RATE_TIES
WHERE COMPID = @ID
ORDER BY COMPID, MINIMUM

OPEN C
FETCH NEXT FROM C INTO @ID, @MIN1, @MAX1, @VALUE, @TYPE1

WHILE(@@FETCH_STATUS = 0)
BEGIN
IF @CARVALUE >= @MIN1
BEGIN
SELECT @EffValue =
CASE
WHEN @CARVALUE > @MAX1 THEN @MAX1
ELSE @CARVALUE - @LastMax
END

SELECT @INSURANCE =
CASE @TYPE1
WHEN 'PERCENT' THEN @INSURANCE + (@VALUE / 100) * @EffValue
WHEN 'FLAT' THEN @INSURANCE + @VALUE
ELSE @INSURANCE
END

PRINT 'TOTAL INSURANCE = ' + CAST( @INSURANCE AS VARCHAR(30))
SELECT @LastMax = @MAX1
END

FETCH NEXT FROM C INTO @ID, @MIN1, @MAX1, @VALUE, @TYPE1
END

CLOSE C
DEALLOCATE C
END
``````
0
Author Commented:
I DONT KNOW WHY BUT i AM GETTING THE OUTPUT AS
TOTAL INSURANCE = 10
TOTAL INSURANCE = 10
TOTAL INSURANCE = 10

AND ONE MORE THING CAN'T WE GET ONLY ONE LINE STATING TOTAL INSURANCE IS 250?
0
I found a better way

``````ALTER PROCEDURE RATETIESINSURANCE
@CARVALUE FLOAT ,
@COMPID1 FLOAT
AS

WITH cteRT (RowNumber, COMPID, MINIMUM, MAXIMUM, VALUE, XTYPE, AggMax)
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY MINIMUM) AS RowNumber,
COMPID, MINIMUM, MAXIMUM, VALUE, XTYPE,
(SELECT SUM(MAXIMUM) FROM RATE_TIES WHERE COMPID = RT.COMPID AND MAXIMUM <= RT.MAXIMUM) AS AggMax
FROM RATE_TIES AS RT
WHERE COMPID = @COMPID1
)
SELECT SUM(CASE RT1.XTYPE
WHEN 'PERCENT' THEN
CASE
WHEN @CARVALUE < RT1.MINIMUM THEN 0
WHEN @CARVALUE > RT1.MAXIMUM THEN RT1.MAXIMUM
ELSE @CARVALUE - ISNULL(RT0.MAXIMUM, 0)
END * (RT1.VALUE / 100.0)
WHEN 'FLAT' THEN RT1.VALUE
END) AS Insurance
FROM cteRT AS RT1
LEFT OUTER JOIN cteRT AS RT0
ON RT1.RowNumber = RT0.RowNumber + 1
``````
0
Author Commented:
Hey Bricrowe,
The insurance calculation is not correct in the code which you have sent previously, because when we give 14000 as carvalue, it should give result as follows:

(14000>1000), so first row returns flat value of 10, then it subtracts 1000 from 14000 which is 13000, then in second row its 2% of max value which is 2%(10000)=200, now (13000-10000)=3000, now in third row the condition fails which id (3000>100000) which is false, so it should calculate the 1% of carvalue now, which is 1%(3000)=30.

So, now sum of insurance should be, 10+200+30= 240, but for us the insurance is giving as 250. please have a look.
0
``````ALTER PROCEDURE RATETIESINSURANCE
@CARVALUE FLOAT ,
@COMPID1 FLOAT
AS
SET NOCOUNT ON;

WITH cteRT (RowNumber, COMPID, MINIMUM, MAXIMUM, VALUE, XTYPE, AggMax)
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY MINIMUM) AS RowNumber,
COMPID, MINIMUM, MAXIMUM, VALUE, XTYPE,
(SELECT SUM(MAXIMUM) FROM RATE_TIES WHERE COMPID = RT.COMPID AND MAXIMUM <= RT.MAXIMUM) AS AggMax
FROM RATE_TIES AS RT
WHERE COMPID = @COMPID1
)
SELECT SUM(CASE RT1.XTYPE
WHEN 'PERCENT' THEN
CASE
WHEN @CARVALUE < RT1.MINIMUM THEN 0
WHEN @CARVALUE > RT1.MAXIMUM THEN RT1.MAXIMUM
ELSE @CARVALUE - RT0.AggMax
END * (RT1.VALUE / 100.0)
WHEN 'FLAT' THEN RT1.VALUE
END) AS Insurance
FROM cteRT AS RT1
LEFT OUTER JOIN cteRT AS RT0
ON RT1.RowNumber = RT0.RowNumber + 1
``````
0
Let me ask you this...what is the total insurance for a carvalue = 10100?
0
Author Commented:
(10100>1000) - True, so it gives the flat value as output which is 10.
Now, maxvalue shouldbe subtracted from carvalue which is
10100-1000=9100
Now checking the condition (carvalue>max) for second row which is,
(9100>10000) _ false,

So 2%(9100)- 182

Sum= 10+182=192
0
One small adjustment to handle the case I mentioned above.

``````ALTER PROCEDURE RATETIESINSURANCE
@CARVALUE FLOAT ,
@COMPID1 FLOAT
AS
SET NOCOUNT ON;

WITH cteRT (RowNumber, COMPID, MINIMUM, MAXIMUM, VALUE, XTYPE, AggMax)
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY MINIMUM) AS RowNumber,
COMPID, MINIMUM, MAXIMUM, VALUE, XTYPE,
(SELECT SUM(MAXIMUM) FROM RATE_TIES WHERE COMPID = RT.COMPID AND MAXIMUM <= RT.MAXIMUM) AS AggMax
FROM RATE_TIES AS RT
WHERE COMPID = @COMPID1
)
SELECT SUM(CASE RT1.XTYPE
WHEN 'PERCENT' THEN
CASE
WHEN @CARVALUE - RT0.AggMax < 0 THEN 0
WHEN @CARVALUE - RT0.AggMax > RT1.MAXIMUM THEN RT1.MAXIMUM
ELSE @CARVALUE - RT0.AggMax
END * (RT1.VALUE / 100.0)
WHEN 'FLAT' THEN RT1.VALUE
END) AS Insurance
FROM cteRT AS RT1
LEFT OUTER JOIN cteRT AS RT0
ON RT1.RowNumber = RT0.RowNumber + 1
``````
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Hey Bricrowe,
This one is working fine, can you update this code in the previous code too, as I need to present the solution by using cursors only, so if you could update this code, I would be thankful.

Thanks,
Aparanjit
0
Author Commented:
Hi Bricrowe,
In the below code which you have built, in this after every row it should whether it is (@carvalue>@max1) or (@carvalue<@max1), after every row when it checks for the condition it should subtract the max value from carvalue which is @carvalue-@max, but in this code it is not doing that, can you please have a look into it.

ALTER PROCEDURE RATETIESINSURANCE
@CARVALUE FLOAT ,
@COMPID1 FLOAT
AS

--DECLARE @CARVALUE FLOAT,
--      @COMPID1 FLOAT

--SELECT @COMPID1 = 101,
--      @CARVALUE = 15000

BEGIN
DECLARE @ID FLOAT
SET @ID=@COMPID1
DECLARE @MAX1      FLOAT,
@MIN1            FLOAT,
@VALUE            FLOAT,
@TYPE1            VARCHAR(20),
@INSURANCE      FLOAT,
@EffValue      FLOAT,
@LastMax      FLOAT
SET @INSURANCE=0

DECLARE C SCROLL CURSOR  FOR
SELECT COMPID,MINIMUM,MAXIMUM,VALUE,XTYPE
FROM dbo.RATE_TIES
WHERE COMPID = @ID
ORDER BY COMPID, MINIMUM

OPEN C
FETCH NEXT FROM C INTO @ID, @MIN1, @MAX1, @VALUE, @TYPE1

WHILE(@@FETCH_STATUS = 0)
BEGIN
IF @CARVALUE >= @MIN1
BEGIN
SELECT @EffValue =
CASE
WHEN @CARVALUE > @MAX1 THEN @MAX1
ELSE @CARVALUE - @LastMax
END

SELECT @INSURANCE =
CASE @TYPE1
WHEN 'PERCENT' THEN @INSURANCE + (@VALUE / 100) * @EffValue
WHEN 'FLAT' THEN @INSURANCE + @VALUE
ELSE @INSURANCE
END

PRINT 'TOTAL INSURANCE = ' + CAST( @INSURANCE AS VARCHAR(30))
SELECT @LastMax = @MAX1
END

FETCH NEXT FROM C INTO @ID, @MIN1, @MAX1, @VALUE, @TYPE1
END

CLOSE C
DEALLOCATE C
END

Thanks,
Aparanjit
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.