Sql Query help. - 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 2000 Forums

SQL Server Development (2000)

Sql Query help.

Author

Topic

Snigdha030
Starting Member

13 Posts

Posted - 2007-10-30 : 03:14:23

Hi all, Could anyboby please help me providing sql query for following requirement.

Req -

A table is having 2 columns and data as below -

Account balance
100120 100
100121 500
100122 410
100123 509
100130 300
100131 290
100140 950

select the sum of the balance field for each account as per below logic.
ex for account 100123, find sum of balance where left(account,5) = 10012 and right(account,1) <= 3.

Similarly if the account is xxxxxi then find sum of balance where left(account,5) = xxxxx and right(account,1) <= i.
for all accounts.

regrds.
Snig.

Kristen
Test

22859 Posts

Posted - 2007-10-30 : 03:45:24

If the last digit is a separate attribute of the data put it in a separate column.

If necessary have three columns:

Account - e.g. 100123
Account1 - e.g. 10012
Account2 - e.g. 3

and use [Account] for display, and Account1 & Account2 for queries

See "database normalisation" for more details

Kristen

Snigdha030
Starting Member

13 Posts

Posted - 2007-10-30 : 04:24:10

Hello Kris, thanks for the response, but this is not resolving my prob. If you can help me providing the query would be great.

regrd,
Snig.

quote:
Originally posted by Kristen

If the last digit is a separate attribute of the data put it in a separate column.

If necessary have three columns:

Account - e.g. 100123
Account1 - e.g. 10012
Account2 - e.g. 3

and use [Account] for display, and Account1 & Account2 for queries

See "database normalisation" for more details

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-30 : 04:29:35

So basically do you want to take into consideration only first 5 digits of Account when grouping?

Select Left(Account, 5) as Account, sum(Balance) as Balance
From Table
Group by Left(Account, 5)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Snigdha030
Starting Member

13 Posts

Posted - 2007-10-30 : 04:47:02

As I have already specified, I do need the Sum of balance where first 5 digit same and the last digit <= i (here i is the end digit of account)

Ex for account 100123 it would sum the balance of all the accounts - 100120,100121,100122,100123

similarly for account 100121 it should add balance of accounts 100120, 100121 only.

So for accounts
100123 & 100121

the sum(balance) should be 1519, 600.
hope this makes sense.

quote:
Originally posted by harsh_athalye

So basically do you want to take into consideration only first 5 digits of Account when grouping?

Select Left(Account, 5) as Account, sum(Balance) as Balance
From Table
Group by Left(Account, 5)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-30 : 05:10:48

Is this what you want?

-- Prepare sample data
declare @t table
(
	account int,
	balance int
)

insert @t
select 100120, 100 union all
select 100121, 500 union all
select 100122, 410 union all
select 100123, 509 union all
select 100130, 300 union all
select 100131, 290 union all
select 100140, 950

-- Final query
select account, (select sum(balance) from @t t2 where t2.account <= t1.account) balance
from @t t1

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Snigdha030
Starting Member

13 Posts

Posted - 2007-10-30 : 05:28:48

Harsh, What I need is as below -

Given table is

A table is having 2 columns and data as below -

Account balance
100120 100
100121 500
100122 410
100123 509
100130 300
100131 290
100140 950

OUTPUT should be

Account balance Sum(balance)
100120 100 100
100121 500 600
100122 410 1010
100123 509 1519
100130 300 300
100131 290 590
100140 950 950

Any more help would be much appreciated.
Snig.

quote:

Originally posted by harsh_athalye

Is this what you want?

-- Prepare sample data
declare @t table
(
	account int,
	balance int
)

insert @t
select 100120, 100 union all
select 100121, 500 union all
select 100122, 410 union all
select 100123, 509 union all
select 100130, 300 union all
select 100131, 290 union all
select 100140, 950

-- Final query
select account, (select sum(balance) from @t t2 where t2.account <= t1.account) balance
from @t t1

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-30 : 05:40:08

[code]declare @t table
(
account int,
balance int
)

insert @t
select 100120, 100 union all
select 100121, 500 union all
select 100122, 410 union all
select 100123, 509 union all
select 100130, 300 union all
select 100131, 290 union all
select 100140, 950

select
account,
balance,
(select sum(balance) from @t t2
where t2.account <= t1.account and
left(t2.account, 5) = left(t1.account,5)) [sum(balance)]
from @t t1[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-30 : 06:01:06

If you want to show the running total in Reports, then you can make use of it's Running Total feature

Madhivanan

Failing to plan is Planning to fail

Snigdha030
Starting Member

13 Posts

Posted - 2007-10-30 : 07:23:57

Thanks Harsh & All.

It's working for the few couple of reords. But since the table is having 17lakh Data Accounts and while executing the Query it's taking hell lot of time, Any idea how to optimize?

quote:
Originally posted by madhivanan

If you want to show the running total in Reports, then you can make use of it's Running Total feature

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-30 : 07:47:55

"Any idea how to optimize?"

if you normalise the data like I suggested that would enable the query to be much more efficient.

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-30 : 07:59:14

This can be another approach, but as Kristen says you have to normalize your data to speed up the query.

Declare @t table
(
	account int,
	balance int
)

insert @t
select 100120, 100 union all
select 100121, 500 union all
select 100122, 410 union all
select 100123, 509 union all
select 100130, 300 union all
select 100131, 290 union all
select 100140, 950

Select t2.Account, t2.Balance, t1.Sum_balance [Sum(Balance)]
from
	(
	select t1.account, sum(t2.balance) as Sum_balance
	from @t t1 cross join @t t2
	where left(t2.account, 5) = left(t1.account,5) and t2.account <= t1.account 
	group by t1.account
	) t1
	join @t t2 on t1.Account = t2.Account
order by 1

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Snigdha030
Starting Member

13 Posts

Posted - 2007-10-31 : 00:36:32

Thanks Harsh & Kristen for all your help.
The query is working much faster now.

Regards,
Snig.

quote:

Originally posted by harsh_athalye

This can be another approach, but as Kristen says you have to normalize your data to speed up the query.

Declare @t table
(
	account int,
	balance int
)

insert @t
select 100120, 100 union all
select 100121, 500 union all
select 100122, 410 union all
select 100123, 509 union all
select 100130, 300 union all
select 100131, 290 union all
select 100140, 950

Select t2.Account, t2.Balance, t1.Sum_balance [Sum(Balance)]
from
	(
	select t1.account, sum(t2.balance) as Sum_balance
	from @t t1 cross join @t t2
	where left(t2.account, 5) = left(t1.account,5) and t2.account <= t1.account 
	group by t1.account
	) t1
	join @t t2 on t1.Account = t2.Account
order by 1

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources