knock off record - 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)

knock off record

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-11-17 : 03:59:36

Dear all,

I have the following two scenario

tran_no date credit Debit
Cv/0001/2006 12jun2006 100 0
Cv/0002/2006 12jun2006 100 0
CP/0001/2006 14jun2006 0 100

tran_no date credit Debit
Cv/0001/2006 12jun2006 50 0
Cv/0002/2006 12jun2006 50 0
CP/0001/2006 14jun2006 0 100
CP/0001/2006 14jun2006 0 111

1)
i just want to knock off the records in first scenario like
Cv/0002/2006 12jun2006 100 0
CP/0001/2006 14jun2006 0 100
Becuase the crdit and debit matches and i just want to show the
Cv/0001/2006 12jun2006 100 0
instead off the 3 records

2)
i just want to knock off the records in Second scenario like
Cv/0001/2006 12jun2006 50 0
Cv/0002/2006 12jun2006 50 0
CP/0001/2006 14jun2006 0 100

because the sum of credit and the debit is matched
i just want show the
CP/0001/2006 14jun2006 0 111
instead of the four records

Note :
these all records are in a single table i just want to do it into single query or cursor (here there is no perf. isues 1000 records only avail )

thanks
krishnakumar

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-17 : 04:28:23

[Scenario 1]


declare @table1 table
(
	tran_no 	varchar(20),
	date 		datetime,
	credit 		int,
	debit		int
)
insert into @table1
select	'Cv/0001/2006', '20060612', 100,   0	union all
select	'Cv/0002/2006', '20060612', 100,   0	union all
select	'CP/0001/2006', '20060614',   0, 100

select	tran_no = max(tran_no), date = max(date), credit, debit
from	@table1
group by credit, debit

/* RESULT

tran_no              date                                                   credit      debit       
-------------------- ------------------------------------------------------ ----------- ----------- 
Cv/0001/2006         2006-06-12 00:00:00.000                                100         0
CP/0001/2006         2006-06-14 00:00:00.000                                0           100

*/

Is this what you want ?

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-11-17 : 04:33:53

Thanks khtan, It's working fine. and i need the second decnario also

can u please help me

thanks
krishna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources