tsql 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

Transact-SQL (2000)

tsql help

Author

Topic

gongxia649
So Suave

344 Posts

Posted - 2006-05-24 : 20:25:09

2 tables, TRANSACTIONS and UNPAIRED

TRANSACTIONS is defined as:
TransactionCode int
PairedWith int
Amount decimal(19,2)

Assume TRANSACTIONS contains the following records

TransactionCode PairedWith Amount
1 2 $10
2 NULL $20
3 NULL $50
4 NULL $30
5 3 $10
6 NULL $20
7 10 $20
8 NULL $30

UNPAIRED is defined as:
TransactionCode int
LinkedTo int

Assume UNPAIRED contains no records

Write a T-SQL procedure that will go through each record in the TRANSACTIONS table, and print to screen (using the PRINT function) the TransactionCode, the TransactionCode of its pair (if applicable), and the sum of the pair's amounts. In cases where the Transaction has a PairedWith value, but no matching transaction is found, insert the TransactionCode and its PairedWith code to the UNPAIRED table.

Expected Output:

In the example above, the correct output would be:
1 - 2 - $30
2 - 1 - $30
3 - 5 - $60
4 - NULL - $30
5 - 3 - $60
6 - NULL - $20
8 - NULL - $30

and the UNPAIRED table would contain one row
TransactionCode PairedWith
7 10

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-24 : 20:44:15

Homework assignment ?

gongxia649
So Suave

344 Posts

Posted - 2006-05-24 : 20:45:19

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-24 : 20:51:22

Can you show us the code you have written already, and tell us what problems you are having with it?

It would also be helpful if you posted the create table statements for the tables.

CODO ERGO SUM

gongxia649
So Suave

344 Posts

Posted - 2006-05-24 : 21:03:50

ok for the insert is this right?: cause i think i should write small queries first then turn it into tsql

insert into unpaired (transactionCode, linkedTo)
select transactionCode, pairedWith from transactions
where transactionCode is not null
and pairedWith is not null
and pairedwith > 8

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-24 : 21:15:37

the UNPAIRED should be easy to obtain, just apply the condition of NOT EXISTS for TransactionCode & PairedWith

for the PAIRED, you can INNER JOIN back the same table using similar condition as the NOT EXISTS

gongxia649
So Suave

344 Posts

Posted - 2006-05-24 : 21:18:07

what i did is wrong ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-24 : 21:19:12

And in future as MVJ suggest try posting your table structure and sample data in CREATE TABLE statements. Most of us are lazy to type and preferred easy way of COPY & PASTE to work on.
For me, i use table variable approach


-- Table Structure
declare @transaction table
(
	transactioncode	int,
	pairedwith	int,
	amount		int
)

declare @unpaired table
(
	transactioncode	int,
	linkedto	int
)
-- Sample Data
insert into @transaction
select	1, 2, 	 10	union all
select	2, NULL, 20	union all
select	3, NULL, 50	union all
select	4, NULL, 30	union all
select	5, 3, 	 10	union all
select	6, NULL, 20	union all
select	7, 10, 	 20	union all
select	8, NULL, 30

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-24 : 21:21:31

quote:
Originally posted by gongxia649

what i did is wrong ?

It's the logic. UNPAIRED is where you can't find the PairedWith record and also no record (PairedWith) is linked to the TransactionCode

select	a.transactioncode, a.pairedwith
from	@transaction a 
where	a.pairedwith is not null
and	not exists (select * from @transaction x 
			where 	x.pairedwith 		= a.transactioncode
			or	x.transactioncode	= a.pairedwith)

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2006-05-25 : 09:44:59

how do you do the paired?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-25 : 09:57:58

[code]-- PAIRED
select trans_code = a.transactioncode,
paired_with = coalesce(a.pairedwith, b.transactioncode),
amount = (a.amount + isnull(b.amount, 0))
from @transaction a left join @transaction b
on (
a.pairedwith = b.transactioncode
or a.transactioncode = b.pairedwith
)
where a.pairedwith is null
or exists (select * from @transaction x
where x.pairedwith = a.transactioncode
or x.transactioncode = a.pairedwith)[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources