Query - 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

Transact-SQL (2000)

Query

Author

Topic

baabak
Starting Member

3 Posts

Posted - 2007-02-27 : 16:00:36

I have 2 Tables
1-Users
with this feilds
UserID,name
2-Credits
with this feilds
ID,UserIDBuyer,UserIDSeler,Credit,Date

for example

select *
from Users
result is

UserID Name
----------- --------------------------------------------------
1 user1
2 user2
3 user3

select *
from Credit
result is

ID UserIDBuyer UserIDSeler Credit Date
--- ----------- ----------- ----------- -------------------------
1 1 2 500 2007-02-26 23:27:17.000
2 1 3 400 2007-02-26 23:27:19.000
3 3 1 100 2007-02-26 23:27:30.000

OK!
now I need this 2 query
1- UserID,BuyedCredit,SoldCredit,RemainCredit
in group
1,900,100,800
2,0,500,-500
3,100,0,100
2-UserID,BuyedCredit,SoldCredit,RemainCredit
without group
like this
1,500,0,500
1,400,0,900
1,0,100,800
thank you

pbaldy
Starting Member

7 Posts

Posted - 2007-02-27 : 16:46:18

Don't have time right now to write out the SQL, but the first could be accomplished with sub queries that summed the data for each user when they were a buyer or seller respectively. Second query looks like a basic running sum problem. Searching on that should turn up numerous methods.

Paul

baabak
Starting Member

3 Posts

Posted - 2007-03-01 : 04:19:16

help pls

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-01 : 05:12:19

[code]
declare @users table
(
UserID int,
[Name] varchar(10)
)

insert into @users
select 1, 'user1' union all
select 2, 'user2' union all
select 3, 'user3'

declare @credit table
(
[ID] int,
UserIDBuyer int,
UserIDSeler int,
Credit int,
[Date] datetime
)

insert into @credit
select 1, 1, 2, 500, '2007-02-26 23:27:17.000' union all
select 2, 1, 3, 400, '2007-02-26 23:27:19.000' union all
select 3, 3, 1, 100, '2007-02-26 23:27:30.000'

select UserID = coalesce(b.UserID, s.UserID),
BuyedCredit = isnull(b.BuyedCredit, 0),
SoldCredit = isnull(s.SoldCredit, 0),
RemainCredit = isnull(b.BuyedCredit, 0) - isnull(s.SoldCredit, 0)
from (
select u.UserID, BuyedCredit = sum(b.Credit)
from @users u inner join @credit b
on u.UserID = b.UserIDBuyer
group by u.UserID
) b
full outer join
(
select u.UserID, SoldCredit = sum(s.Credit)
from @users u inner join @credit s
on u.UserID = s.UserIDSeler
group by u.UserID
) s
on b.UserID = s.UserID

select u.UserID,
BuyedCredit = case when u.UserID = b.UserIDBuyer then b.Credit else 0 end,
SoldCredit = case when u.UserID = b.UserIDSeler then b.Credit else 0 end
from @users u inner join @credit b
on u.UserID = b.UserIDBuyer
or u.UserID = b.UserIDSeler
order by u.UserID, BuyedCredit desc, SoldCredit
[/code]

baabak
Starting Member

3 Posts

Posted - 2007-03-01 : 08:36:25

thank you Dear khtan
But for last query I need someting like this

example:
for user1 with id i

UserID BuyedCredit SoldCredit RemainCredit
----------- ----------- ----------- ------------
1 500 0 500
1 400 0 900
1 0 100 800
RemainCredit is very important To me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-01 : 09:26:02

quote:
Originally posted by baabak

thank you Dear khtan
But for last query I need someting like this

example:
for user1 with id i

UserID BuyedCredit SoldCredit RemainCredit
----------- ----------- ----------- ------------
1 500 0 500
1 400 0 900
1 0 100 800
RemainCredit is very important To me

Do the running total in your front end application. It is much easier there

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-01 : 09:31:36

Here is one way of doing the running total in T-SQL. Via a table variable / temp table


declare @users table
(
	UserID	int,
	[Name]	varchar(10)
)

insert into @users
select	1, 'user1'	union all
select	2, 'user2'	union all
select	3, 'user3'

declare @credit table
(
	[ID]		int,
	UserIDBuyer	int,
	UserIDSeler	int,
	Credit		int,
	[Date]		datetime
)

insert into @credit
select	1, 1, 2, 500, '2007-02-26 23:27:17.000' union all
select	2, 1, 3, 400, '2007-02-26 23:27:19.000' union all
select	3, 3, 1, 100, '2007-02-26 23:27:30.000'

select	UserID = coalesce(b.UserID, s.UserID),
	BuyedCredit	= isnull(b.BuyedCredit, 0),
	SoldCredit	= isnull(s.SoldCredit, 0),
	RemainCredit	= isnull(b.BuyedCredit, 0) - isnull(s.SoldCredit, 0)
from	(
		select	u.UserID, BuyedCredit = sum(b.Credit)
		from	@users u inner join @credit b
		on	u.UserID	= b.UserIDBuyer
		group by u.UserID
	) b
	full outer join
	(
		select	u.UserID, SoldCredit = sum(s.Credit)
		from	@users u inner join @credit s
		on	u.UserID	= s.UserIDSeler
		group by u.UserID
	) s
	on	b.UserID	= s.UserID

/*
UserID      BuyedCredit SoldCredit  RemainCredit 
----------- ----------- ----------- ------------ 
1           900         100         800
2           0           500         -500
3           100         400         -300
*/

declare	@result table
(
	row		int identity(1,1),
	UserID		int,
	BuyedCredit	int,
	SoldCredit	int
)

insert into @result (UserID, BuyedCredit, SoldCredit)
select	u.UserID, 
	BuyedCredit	= case when u.UserID = b.UserIDBuyer then b.Credit else 0 end,
	SoldCredit	= case when u.UserID = b.UserIDSeler then b.Credit else 0 end
from	@users u inner join @credit b
	on	u.UserID	= b.UserIDBuyer
	or	u.UserID	= b.UserIDSeler
order by u.UserID, BuyedCredit desc, SoldCredit

select	r.UserID, r.BuyedCredit, r.SoldCredit,
	RemainCredit	= (select sum(BuyedCredit - SoldCredit) from @result x where x.UserID = r.UserID and x.row <= r.row)
from	@result r
order by row

/*
UserID      BuyedCredit SoldCredit  RemainCredit 
----------- ----------- ----------- ------------ 
1           500         0           500
1           400         0           900
1           0           100         800
2           0           500         -500
3           100         0           100
3           0           400         -300
*/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources