Question about ranges in DB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Question about ranges in DB

Author

Topic

jmsewe0
Starting Member

7 Posts

Posted - 2009-02-16 : 14:01:00

Ok, so I have a table in my database named adjustments. It has 7 columns (id, program_id, ltv_min, ltv_max, cs_min, cs_max, and adjustment).

If you couldnt guess already, this is for a mortgage company :) Ok, well for any given program_id we may have three or four records with some ranges. For example:
id prog_id ltvmin ltvmax cs_min cs_max adjus
1 88 0.00 65.00 0 600 0.500
2 88 65.01 75.00 601 700 0.375
3 88 75.01 80.00 701 1000 0.250

So, someone that wanted the program with id 88 and had 78.00 LTV, and a 650 credit score would have a .375 adjustment. Make sense?

Well, I need to display these items in a .net gridview, but they need to be displayed a little cleaner. For example, the column headers would be the ranges for credit score (cs) (in this example 0-600, 601-700, 700-1000), and the first row would be the LTV ranges (0-65, 65.01-75, 75.01-80) and the table would contain the appropriate adjustment.

I may not be doing the best job of explaining this, so if you want to help and need something else in the way of info, let me know.

Thanks in advance, Josh

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-16 : 14:05:25

So what is your expected output?

jmsewe0
Starting Member

7 Posts

Posted - 2009-02-16 : 14:10:08

Ok, output would look like this:

0-600 601-700 700-1000
0-65 .500 0 0
65.01-75 0 .375 0
75.01-80 0 0 .250

Once again, this is just a small sample of all the data I am working with, so sorry if this is not clear.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-16 : 14:19:33

quote:
Originally posted by jmsewe0

Ok, output would look like this:

0-600 601-700 700-1000
0-65 .500 0 0
65.01-75 0 .375 0
75.01-80 0 0 .250

Once again, this is just a small sample of all the data I am working with, so sorry if this is not clear.

Still not clear.

jmsewe0
Starting Member

7 Posts

Posted - 2009-03-02 : 14:22:37

Ok, i have digested this a little more and I thought an Image would help you see what i was looking for.

Here you go [url]http://www.surepoint.com/test/results.gif[/url]

Does this help?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-02 : 15:12:50

a SELECT pivot table or a do while insert into a table variable might do it?
can't you take care of this in the front end, it looks like you have the result you want. Can you not change the column names to the first row of the recordset you are returning to your gridview and the rest is to populate the gridview itself.
from what I see you have all you need it is just a matter of formatting which should be done in front end imho

jmsewe0
Starting Member

7 Posts

Posted - 2009-03-02 : 15:28:29

Yeah, actually some of it could be done in the front end, but I am trying to make this as dynamic as possible. The programs, and ranges change very frequently so, i am not sure if I want to put a lot of logic in the front end. The front end may be two aspx files. one to list all the programs, and a second to display the data. But i do agree that some of that should be done in the front end...I just know the higher ups here very well, and they love to change their mind...

Thanks for the help though.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-02 : 15:37:16

ok I see now because for a certain adjustmentid you could haev many rows so it has to be dynamic ..this is a very good and tough question! I am trying it out and I will post back.

jmsewe0
Starting Member

7 Posts

Posted - 2009-03-03 : 09:23:08

Yeah, it is definitely a toughie. I am working with using some dynamic sql to create the structure of the return table and then inserting values into those rows, but I am hitting a few roadblocks...

jmsewe0
Starting Member

7 Posts

Posted - 2009-03-09 : 10:39:16

anyone?

I think I am going to have to use some sort of dynamic sql to generate a table...similar to this:

eclare @rows int,
@columns int,
@columninc int,
@SQL varchar(MAX)

set @columns = (select count(distinct ltv_min)+1
from adjustment_creditscore
where program_id = 1)

set @rows = (Select count(distinct creditscore_min) + 1
from adjustment_creditscore
where program_id = 1)

set @columninc = 1
set @SQL = 'Create Table #holding('
while (@columns > 0)
begin
--print 'Hello'
--print @sql
set @SQL = @SQL + 'col'+ cast(@columninc as varchar)+' int,'
set @columninc = @columninc + 1
set @columns = @columns - 1
end
set @SQL = Substring(@SQL, 1, (len(@Sql)-1)) + ')'

Then execute that sql to create the dynamic table...my head hurts :(

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources