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
BassamDo:
SELECT MAX( voucher ), RNo, SUM( Amount )
FROM tbl
GROUP BY RNo, ABS( Amount ) ;
Anith|||Hi Anith,
This Query wont work for the following set of data
1 r1 100
2 r1 100
3 r1 -100
4 r1 50
5 r1 25
6 r2 30
7 r2 20
Your Query fetches voucher No 3 with values as +100 , this row is not there
in the table at all.
the right result would be to fetch voucher 1 with amount 100.
The following Query will work out for all cases
select * from vouch
where amt > 0 and voucher not in
(
select
min(v2.voucher)
from
vouch v1
inner join vouch v2 on
v1.rno = v2.rno and
abs(v1.amt) = v2.amt and
v1.voucher > v2.voucher
where v1.amt < 0
)
- Sha Anand
"Anith Sen" wrote:
> Do:
> SELECT MAX( voucher ), RNo, SUM( Amount )
> FROM tbl
> GROUP BY RNo, ABS( Amount ) ;
> --
> Anith
>
>|||>> Your Query fetches voucher No 3 with values as +100 , this row is not
Based on the OP's narrative, it is not clear if the row with voucher 1 or
voucher 2 is the "corresponding" row for the one with voucher 3. In any
case, considering any row with a positive amount value, the query can be
changed to:
SELECT MAX( CASE WHEN SIGN(Amount) <> -1. THEN voucher END )
..
Anith|||Anith,
> SELECT MAX( CASE WHEN SIGN(Amount) <> -1. THEN voucher END )
> ...
how about this data?
1 r1 100
2 r1 100
3 r1 -100
4 r1 100
5 r1 100
what is the correct output?|||Consider this set of data:
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
your query returns:
select * from table1
where amount > 0 and voucher not in
(
select
min(v2.voucher)
from
table1 v1
inner join table1 v2 on
v1.rno = v2.rno and
abs(v1.amount) = v2.amount and
v1.voucher > v2.voucher
where v1.amount < 0
)
Voucher RNo Amount
-- -- --
3 R1 100
4 R1 50
5 R1 25
6 R2 30
7 R2 20
31 R1 100
61 R2 30
62 R2 30
(8 row(s) affected)
I think it should return only 1 row for the amount of 100|||Argh...
; WITH cte AS (
SELECT Voucher, RNo, Amount,
RANK() OVER ( PARTITION BY SIGN( Amount )
ORDER BY RNo, voucher ) AS "rank"
FROM tbl )
SELECT Voucher, RNo, Amount
FROM cte c1
WHERE ( SELECT COUNT(*) FROM cte c2
WHERE c2.rank = c1.rank ) = 1 ;
Anith|||Consider this data:
delete from Table1;
INSERT INTO Table1 (Voucher, RNo, Amount)
SELECT 101 , '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 61 , 'R2' , 30 UNION ALL
SELECT 62 , 'R2' , 30 UNION ALL
SELECT 7 , 'R2' , 20 ;
I ran this:
WITH cte AS (
SELECT Voucher, RNo, Amount,
RANK() OVER ( PARTITION BY SIGN( Amount )
ORDER BY RNo, voucher ) AS "rank"
FROM table1 )
SELECT Voucher, RNo, Amount
FROM cte c1
WHERE ( SELECT COUNT(*) FROM cte c2
WHERE c2.rank = c1.rank ) = 1 ;
and got this:
Voucher RNo Amount
-- -- --
5 R1 25
13 R1 100
101 R1 100
6 R2 30
7 R2 20
61 R2 30
62 R2 30
(7 row(s) affected)
Note that 50 is missing and 100 is twice, there should be 100 only
once.
I tweaked your query as follows:
WITH cte AS (
SELECT Voucher, RNo, Amount,
RANK() OVER ( PARTITION BY Amount
ORDER BY RNo, voucher ) AS "rank"
FROM table1 )
SELECT Voucher, RNo, Amount
FROM cte c1
WHERE ( SELECT COUNT(*) FROM cte c2
WHERE c2.rank = c1.rank and c2.amount = -c1.amount) = 0 ;
and got the results which I think are correct:
Voucher RNo Amount
-- -- --
7 R2 20
5 R1 25
6 R2 30
61 R2 30
62 R2 30
4 R1 50
101 R1 100
(7 row(s) affected)
What do you think?|||Alexander Kuznetsov wrote:
> WITH cte AS (
> SELECT Voucher, RNo, Amount,
> RANK() OVER ( PARTITION BY Amount
> ORDER BY RNo, voucher ) AS "rank"
> FROM table1 )
> SELECT Voucher, RNo, Amount
> FROM cte c1
> WHERE ( SELECT COUNT(*) FROM cte c2
> WHERE c2.rank = c1.rank and c2.amount = -c1.amount) = 0 ;
This is another version using row_number (it's easier to understand for me):
WITH cte AS
(
SELECT
Voucher, RNo, Amount,
ROW_NUMBER() OVER ( PARTITION BY RNo, Amount
ORDER BY voucher ) AS rn
FROM table1
)
SELECT c1.Voucher, c1.RNo, c1.Amount
FROM cte c1
WHERE NOT EXISTS
(
SELECT * FROM cte c2
WHERE c2.RNo = c1.RNo AND c2.amount = -c1.amount AND c2.rn = c1.rn
);
Btw, if MS implemented EXCEPT ALL, this would be so simple:
select RNo, amount from table1 where amount >= 0
except all
select RNo, -amount from table1 where amount < 0
The only di
vantage is the missing RNo...
Dieter|||One more approach is to use sum() over() OLAP function, but I don't
think it is available in SS2005 yet (it sure would work in Oracle 9i
and higher). Anyway, try someting like this (untested):
select * from(
select ..., sum() over(partition by rno, abs(amount) order by amount)
rolling_total
FROM table1) t
where rolling_total>0
It is amazing how powerful and useful are OLAP functions, once you get
used to them!
