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:
EXECUTE  RATETIESPREMIUM 15000, 101

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

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.
AparanjithAsked:
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.

Brian CroweDatabase AdministratorCommented:
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
AparanjithAuthor 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
Brian CroweDatabase AdministratorCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

AparanjithAuthor Commented:
Sure
0
Brian CroweDatabase AdministratorCommented:
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

Open in new window

0
AparanjithAuthor 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
Brian CroweDatabase AdministratorCommented:
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

Open in new window

0
Brian CroweDatabase AdministratorCommented:
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
AparanjithAuthor 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
                Please have a look.
0
AparanjithAuthor Commented:
If the xtype is flat, then it should take the value of that row in full and add to insurance.
0
Brian CroweDatabase AdministratorCommented:
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

Open in new window

0
AparanjithAuthor 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
Brian CroweDatabase AdministratorCommented:
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

Open in new window

0
AparanjithAuthor 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
Brian CroweDatabase AdministratorCommented:
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

Open in new window

0
Brian CroweDatabase AdministratorCommented:
Let me ask you this...what is the total insurance for a carvalue = 10100?
0
AparanjithAuthor 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
Brian CroweDatabase AdministratorCommented:
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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
AparanjithAuthor 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
AparanjithAuthor 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.