A query problem - 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)

A query problem

Author

Topic

RashidYusuf
Starting Member

6 Posts

Posted - 2005-04-12 : 04:43:11

I have a transaction table (T1) with two columns showing debits and credits
as under:

transid amount Dr/Cr
1 2000 Dr
2 -2000 Cr
3 3000 Dr
4 -3000 Cr
5 4000 Dr
6 -4000 Cr
7 4000 Dr
8 5000 Dr

Transid 1,3,5 has Dr amount and transid 2,4,6 are corresponding Cr amount.
for Transid 7,8 (Dr) has no corresponding Cr.

In attempt to flag out Transid 7,8 (i.e. records which has no corresponding
Cr records), I used following query:

Select * from T1 where amount> 0 and amount not in
(select abs(amount) from T1 where amount<0)

this query gives me result as:

transid amount Dr/Cr
8 5000 Dr

As you know both Transid 7 and 8 should appear in result. Transid 7 is not
appearing because of amount 4000.

Please guide me to tackle this case.

Querring out

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-12 : 05:04:42

Your design is going to let you down in this instance.

You should be able to achieve what you want to achieve by getting counts of values and comparing the counts for negative and positive values.

-------
Moo. :)

RashidYusuf
Starting Member

6 Posts

Posted - 2005-04-12 : 05:23:50

Thanks Mr_Mist

Sorry for showing only part of the table design. There is an another field called "Description". Corresponding records (i.e. Dr and Cr) has Description which is not exactly same. For example: Transid 1 Description is "CHQ.50011 XXX BANK 15/2/05" and Transid 2 Description is "CASH 20/2/05(CHQ.50011)ST.109 1/1/05". In these 2 Description "CHQ.50011" is common.

Can you suggest me the way to take help of this field: Description

Thanks and best regrds.

Querring out

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-12 : 05:36:32

Hmm. Problem with a description field like that is that it's of virtually no use for comparison purposes. The best you could hope out of that is to try some kind of patindex join, which will be slow and clunky.

If you could apply a set of business rules to your description then that might help matters, you could probably then manage to extract out the key bits of the fields into different tables. EG extract out the CHQ part.

-------
Moo. :)

RashidYusuf
Starting Member

6 Posts

Posted - 2005-04-12 : 09:45:35

Finally it is done. For sake of your interest writing down the query:
Select * from t1 where amount > 0 and
substring(description,charindex('CHQ.',[description]),9)+'|'+convert(varchar,amount)
not in
(select substring(description,charindex('CHQ.',[description]),9)+'|'+convert(varchar,abs(amount))
from t1 where amount<0)

Querring out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources