Range Grouping 1

[Palpa]

hi all,

Need to create a range depending upon the min and max of the record. Let say, the field interest rate has min value 0f 2.56 and max value of 5.12. Now we need to group interest rate based on following condition:

Minimum quartile will always be .01, .26 .51, .76
Maximum quartile will always be .25, .5, .75, .00

Example: Min Interest Rate = 3.56 and Max Interest rate = 5.12 then quartiles would be as follows:
3.51-3.75
3.76-4.00
4.01-4.50
4.51-4.75
4.76-5.00
5.01-5.25

Any help regarding this will be greatly appreciated.

 

[RTag]

Why 4.01-4.50 and not 4.01-4.25 and 4.26-4.50?
I would do the preparation on the database side. What is your database ?

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[Palpa]

sorry for the typo, yes it should be 4.01 - 4.25 then 4.26 - 4.50. The database is SQL server 2008. if you have any idea, i would really appreciate your help.

 

[RTag]

You can use table function to generate a table with data based on 2 parameters : FromValue and ToValue . This script will return record between 3.01 and 7.XX. Starting value should be one of you MIN values (.01, .26, .51, .76)


DECLARE @FromValue DECIMAL(9,2),@ToValue DECIMAL(9,2)
SELECT @FromValue = 3.01,@ToValue=7.24;

WITH NumbersTable(CurrentValue) AS
(
SELECT CONVERT(DECIMAL(9,2),@FromValue)
UNION ALL
SELECT CONVERT(DECIMAL(9,2),CurrentValue + .25) FROM NumbersTable WHERE CurrentValue < @ToValue
)

SELECT CurrentValue AS MinValue, CurrentValue+.25 AS MaxValue FROM NumbersTable;

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[Palpa]

Thanks for prompt reply Rtag.But the interest rate min and max can change depending upon other factors. At that time we can't hard code the interest min and max rate but it should be coming from a field within a table. isn't there any other way other than function.


Thanks

 

[RTag]

Palpa, what do you mean by min and max interest rate ?

These 2 lines are used to define 2 variables @FromValue and @ToValue

DECLARE @FromValue DECIMAL(9,2),@ToValue DECIMAL(9,2)
SELECT @FromValue = 3.01,@ToValue=7.24

In your script you can set the values from a table instead of hard-coding them.

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[lbass]

Another approach would be to insert a group on a formula like this:

//{rangegroup}:
truncate(({table.intrate}-.01)/.25)*.25+.01

Then add another formula to display the range:

//{@rangegroupname}:
totext({@rangegroup},2)+"-"+totext({@rangegroup}+.24,2)

Then replace the groupname with {@rangegroupname}.

-LB

 

[Palpa]

The min and the max values are coming from the field - mortgageinterestrate from the table and it changes depending upon the mortgage level. so when you group the report as per mortgage level, the interest changes and so does your range.

 

[RTag]

You will always have MIN and MAX values for all records.

You may create a table for all possible values; let say min value is 0% max value is 10%. Since you have a step .25 the table will have just 40 records. Then join your mortgageinterestrate and the new table


The script with dynamic table will look like this:

DECLARE @FromValue DECIMAL(9,2),@ToValue DECIMAL(9,2)
SELECT @FromValue = 0.01,@ToValue=10;

WITH NumbersTable(CurrentValue) AS
(
SELECT CONVERT(DECIMAL(9,2),@FromValue)
UNION ALL
SELECT CONVERT(DECIMAL(9,2),CurrentValue + .25) FROM NumbersTable WHERE CurrentValue < @ToValue
)

SELECT CurrentValue AS InterestValue INTO #InterestValues FROM NumbersTable;

SELECT m.*,i.InterestValue AS MinValue,i.InterestValue +.25 AS MaxValue
FROM mortgageinterestrate m
INNER JOIN #InterestValues i ON m.MinValue<i.InterestValue AND i.InterestValue<m.MaxValue


You can simplify it by creating a hardcoded table PosibleInterestValues and run this SQL from your report:

SELECT m.*,i.InterestValue AS MinValue,i.InterestValue +.25 AS MaxValue
FROM mortgageinterestrate m
INNER JOIN PosibleInterestValues i ON m.MinValue<i.InterestValue AND i.InterestValue<m.MaxValue





You can create table PosibleInterestValues by runing once this script:
DECLARE @FromValue DECIMAL(9,2),@ToValue DECIMAL(9,2)
SELECT @FromValue = 0.01,@ToValue=10;

WITH NumbersTable(CurrentValue) AS
(
SELECT CONVERT(DECIMAL(9,2),@FromValue)
UNION ALL
SELECT CONVERT(DECIMAL(9,2),CurrentValue + .25) FROM NumbersTable WHERE CurrentValue < @ToValue
)

SELECT CurrentValue AS InterestValue INTO PosibleInterestValues
FROM NumbersTable;

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.