sql server programming: create table #seq(n int)
go
insert into #seq values(1)
insert into #seq values(2)
insert into #seq values(3)
go
create table #t(
Units int, Project_Desc char(6))
go
insert into #t values(10, 'Desc_a')
insert into #t values(-10, 'Desc_a')
insert into #t values(12, 'Desc_a')
insert into #t values(10, 'Desc_a')
insert into #t values(5, 'desc_b')
insert into #t values(5, 'desc_b')
insert into #t values(5, 'desc_b')
go
select t_positive.units, t_positive.Project_Desc
--, t_positive.cnt, coalesce(t_negative.cnt, 0) nct, #seq.n
from
(select units, Project_Desc, count(*) cnt
from #t where units>0
group by units, Project_Desc
) t_positive
left outer join
(select units, Project_Desc, count(*) cnt
from #t where units<0
group by units, Project_Desc
) t_negative
on t_positive.units = - t_negative.units
and t_positive.Project_Desc = t_negative.Project_Desc
cross join #seq
where #seq.n <= (t_positive.cnt - coalesce(t_negative.cnt, 0))

[quoted text, click to view]

I'm including assumed DDL and your sample data. Test carefully, it is
trickier than it looks.

CREATE TABLE Table1 (Voucher INT PRIMARY KEY, RNo VARCHAR(20) NOT NULL,
Amount INT NOT NULL);

INSERT INTO Table1 (Voucher, RNo, Amount)
SELECT 1 , 'R1' , 100 UNION ALL
SELECT 2 , 'R1' ,-100 UNION ALL
SELECT 3 , 'R1' , 100 UNION ALL
SELECT 4 , 'R1' , 50 UNION ALL
SELECT 5 , 'R1' , 25 UNION ALL
SELECT 6 , 'R2' , 30 UNION ALL
SELECT 7 , 'R2' , 20 ;

SELECT Voucher, RNo, Amount
FROM Table1 AS t
WHERE NOT EXISTS
(SELECT 1
FROM Table1 AS cr
JOIN Table1 AS dr
ON dr.Voucher > cr.Voucher
AND cr.Amount = -dr.Amount
AND dr.Amount < 0
AND cr.RNo = dr.RNo
GROUP BY dr.Voucher
HAVING t.Voucher IN (MIN(cr.voucher), dr.voucher));

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

A small change will make a nice improvement in the performance of this:

SELECT Voucher, RNo, Amount
FROM Table1 AS t
WHERE NOT EXISTS
(SELECT 1
FROM Table1 AS cr
JOIN Table1 AS dr
ON dr.Voucher > cr.Voucher
AND cr.Amount = -dr.Amount
AND dr.Amount < 0
AND cr.RNo = dr.RNo
AND t.Voucher BETWEEN cr.voucher AND dr.voucher
GROUP BY dr.Voucher
HAVING t.Voucher IN (MIN(cr.voucher), dr.voucher));

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Cam we spell 'Impatience'?

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam


[quoted text, click to view]

[quoted text, click to view]

Very ingenious, but it does have the disadvantage that you can't return
the Voucher number. :-)

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

David,

I modified your test data a little bit:

INSERT INTO Table1 (Voucher, RNo, Amount)
SELECT 1 , 'R1' , 100 UNION ALL
SELECT 2 , 'R1' ,-100 UNION ALL
SELECT 3 , 'R1' , 100 UNION ALL
SELECT 12 , 'R1' ,-100 UNION ALL
SELECT 13 , 'R1' , 100 UNION ALL
SELECT 4 , 'R1' , 50 UNION ALL
SELECT 5 , 'R1' , 25 UNION ALL
SELECT 6 , 'R2' , 30 UNION ALL
SELECT 7 , 'R2' , 20 ;

and ran your final version:

Voucher RNo Amount
----------- -------------------- -----------
3 R1 100
4 R1 50
5 R1 25
6 R2 30
7 R2 20
13 R1 100

(6 row(s) affected)

I have a suspicion that there should be only one row for the amount of
100, but there are 2. What do you think?

[quoted text, click to view]

Good catch, you're right. Needs looking at again...

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

David,

I added more test data:

delete from table1
go
INSERT INTO Table1 (Voucher, RNo, Amount)
SELECT 1 , 'R1' , 100 UNION ALL
SELECT 2 , 'R1' ,-100 UNION ALL
SELECT 3 , 'R1' , 100 UNION ALL
SELECT 21 , 'R1' ,-100 UNION ALL
SELECT 31 , 'R1' , 100 UNION ALL
SELECT 4 , 'R1' , 50 UNION ALL
SELECT 5 , 'R1' , 25 UNION ALL
SELECT 6 , 'R2' , 30 UNION ALL
SELECT 61 , 'R2' , 30 UNION ALL
SELECT 62 , 'R2' , 30 UNION ALL
SELECT 7 , 'R2' , 20 ;
go

and the query:

select * from table1 t where t.amount > 0
and (select count(1) from table1 t1 where t.rno=t1.rno
and t1.amount > 0
and t1.Voucher > t.Voucher)
< (select count(1) from table1 t1 where t.rno=t1.rno
and t1.amount > 0) -
(select count(1) from table1 t1 where t.rno=t1.rno
and t1.amount < 0)

the results:

Voucher RNo Amount
----------- -------------------- -----------
4 R1 50
5 R1 25
6 R2 30
7 R2 20
31 R1 100
61 R2 30
62 R2 30

(7 row(s) affected)

look correct to me, 1 row for amount of 100, 3 rows for the amount of
30. What do you think?

[quoted text, click to view]

And 'correct system time'?

Hello, this is a table fragment - Table1

Voucher (Int) , RNo (Varchar(20) , Amount (int)

this is a data example
Voucher , RNo , Amount
----------------------------
1 , R1 , 100
2 , R1 , -100
3 , R1 , 100
4 , R1 , 50
5 , R1 , 25
6 , R2 , 30
7, R2 , 20
-----------------
now i need to select rows for all amounts in same RNo that does not have
corresponsing amount in opposite sign, like that

Result needed
Voucher , RNo , Amount
----------------------------
3, R1 , 100
4, R1 , 50
5, R1 , 25
6 , R2 , 30
7, R1 , 20
--------------------------
the first 2 rows have same RNo='R1' and they have same value but opposite
sign 100 , -100 so they will make each other disappear
the third row with voucher=3 then does not find corresponding -100 for same
RNo because it disappeared in first comparison.

i hope im clear but i can't figure out a way to select this retult rather
than using a cursor ! any help
Thank you
Bassam