Querying for Grid like details

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 2000 Forums

SQL Server Development (2000)

Querying for Grid like details

Author

Topic

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-06-17 : 11:13:34

Please help guys,

I need to run a query that returns results based on debt buckets for my company from our sql server...

basically, for each group, say, 0 - 25,000 or 150,000 - 200,000, I need to return how many loans are outstanding, total amount of those loans, percentage of those loans to the whole amount owed to our company, the average interest rate for individuals in that bucket...the result should look like.

Buckets---#_of_Loans----BucketBalance----%BucketBal----AvgIntRate
0-25,000---------120----------23,000----------8.5%-----------12.67%
25,000-50,000----233----------49,234----------16.13%----------10.56%
50,000-100,000----98-----------98,234----------12.24%----------18.67%
etc.

each of these cells in the result is calculated in sql,
eg, #_of_loans will be Count(loan) where bal < 25,000.
I need to calculate the bucket balance, which will be sum of all balances in that bucket etc...etc.

I'm currently using a cursor to go through the table and using variables to calculate each value...could I possibly do something like this any other way

Please help

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-17 : 12:46:13

Yes, more that likely you can.

We really can't help you any more than that, since you didn't provide any information.

If yoy want any help, follow the directions in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

CODO ERGO SUM

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-06-17 : 12:53:08

quote:
Originally posted by Michael Valentine Jones

Yes, more that likely you can.

We really can't help you any more than that, since you didn't provide any information.

If yoy want any help, follow the directions in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

CODO ERGO SUM

What else should i provide

my table contains COLUMNS for
LoanNum,
AmountOwed,
InterestRate,
firstname,
lastname,
address
etc.

each row in the table represents a debt item and I want to create a grid like summary with details as shown prev.. i.e a summary for each of the debt groupings or buckets if you will

for each grouping - eg people owing 1.00 - 25,000 dollars
I want
total number of debtors,
sum of all amounts owed,
percentage of that amount in relation to total sum of all debts
average of the interest rates for same group

then same for people owing between 25,000 - 50,000
then same for people owing between 50,000 - 100,000 etc
i.e in 50,000 increaments till max amount owed to our company

and I'll be presenting the grid summary via crystal reports....
I need the most efficient way to query for these aggregates

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-17 : 13:21:11

quote:
Originally posted by tchinedu

quote:
Originally posted by Michael Valentine Jones

Yes, more that likely you can.

We really can't help you any more than that, since you didn't provide any information.

If yoy want any help, follow the directions in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

CODO ERGO SUM

What else should i provide
...

You didn't post the table DLL, sample date, or your current query, as explained in the link.

What is the calculation for "average of the interest rates for same group"

CODO ERGO SUM

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-06-17 : 13:35:53

I used a cursor originally, now I'm trying for other mean...here's my latest try

declare @TotalBal float
set @TotalBal = (select sum(balance) from LoansTable)

select count(loannumber) as "LoanCount1",
sum(Balance) as "SumBal1",
sum(Balance)/@TotalBal *100.00 as "PercentBal1",
rate*balance/sum(Balance)*100.00 as "Avg_Rate1",
from LoansTable
where balance < 25000
group by loannumber, balance

the problem with this is that the query doesn't give me an aggregate result, it lists each qualifying row instead of a sum of all rows that fall into the group....this is as a result of the group by I presume.

then, I'd have to repeat that query for each bucket and probabaly insert the results into a table and read the results from the table for my report....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-17 : 20:58:26

Something like this.

declare @LoansTable table
(
	LoanNum		int identity(1,1),
	AmountOwed	decimal(10,2),
	Rate		decimal( 4,2),
	Balance		decimal(10,2)
)

insert into @LoansTable(AmountOwed, Rate, Balance)
select	10000, 4,    9000	union all
select	30000, 4,   25000	union all
select	60000, 3.5, 50000	union all
select	70000, 3.5, 70000

select	Buckets, count(LoanNum) as [# of Loans],
	sum(Balance) as BucketBalance
from
(
	select	case 
		when Balance <= 25000 			    then '0 - 25000'
		when Balance >  25000 and Balance <=  50000 then '25000 - 50000'
		when Balance >  50000 and Balance <= 100000 then '50000 - 100000'
		end as [Buckets],
		LoanNum,
		Rate,
		Balance
	from	@LoansTable
) Loans
group by Buckets

If this is not what you are after, please provide the table DDL, some sample data and the result that you want.

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-06-18 : 15:12:34

thank u sir, looks like exactly what i'm looking for

quote:

Originally posted by khtan

Something like this.

declare @LoansTable table
(
	LoanNum		int identity(1,1),
	AmountOwed	decimal(10,2),
	Rate		decimal( 4,2),
	Balance		decimal(10,2)
)

insert into @LoansTable(AmountOwed, Rate, Balance)
select	10000, 4,    9000	union all
select	30000, 4,   25000	union all
select	60000, 3.5, 50000	union all
select	70000, 3.5, 70000

select	Buckets, count(LoanNum) as [# of Loans],
	sum(Balance) as BucketBalance
from
(
	select	case 
		when Balance <= 25000    then '0 - 25000'
		when Balance >  25000 and Balance <=  50000 then '25000 - 50000'
		when Balance >  50000 and Balance <= 100000 then '50000 - 100000'
		end as [Buckets],
		LoanNum,
		Rate,
		Balance
	from	@LoansTable
) Loans
group by Buckets

If this is not what you are after, please provide the table DDL, some sample data and the result that you want.

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-06-18 : 16:06:48

Can I select mutiple values in a case statement

ex:

insert into @mytable(label, Balsum, RateGrid)
select case
when balance <= 25000
( '0 - 25000', sum(Originalbalance), rate*Balabce)
when balance >2500 and balance < 50000
( '25000 - 50000', sum(OrigBal)*5, (rate*balance)*5)
end

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-18 : 20:02:16

No. You have to use multiple case statement

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources