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

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

Alexander Kuznetsov wrote:
Show quoteHide quoteVery ingenious, but it does have the disadvantage that you can't return
the Voucher number. :-)

Bassam wrote:
Show quoteHide quoteI'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));

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,

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?

Alexander Kuznetsov wrote:
Show quoteHide quoteGood catch, you're right. Needs looking at again...

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?

Cam we spell 'Impatience'?

Show quoteHide quote

And 'correct system time'?

Issue with T-SQL and SQL 7 to SQL2000 conversion
trigger/(exists) problem
Unique Constraint on Table Variables
many to many query with 1 row per result?
A tricky query