can a SELECT return this result ?!

21 Jun 2006 9:03 PM

Bassam

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

21 Jun 2006 9:56 PM

Anith Sen

Do:

SELECT MAX( Voucher ), RNo, SUM( Amount )
FROM tbl
GROUP BY RNo, ABS( Amount ) ;

--
Anith

22 Jun 2006 7:18 AM

Sha Anand

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

Show quote

"Anith Sen" wrote:

> Do:
>
> SELECT MAX( Voucher ), RNo, SUM( Amount )
> FROM tbl
> GROUP BY RNo, ABS( Amount ) ;
>
> --
> Anith
>
>
>

22 Jun 2006 1:01 PM

Anith Sen

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

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

22 Jun 2006 1:31 PM

Alexander Kuznetsov

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?

22 Jun 2006 3:13 PM

Anith Sen

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

22 Jun 2006 3:41 PM

Alexander Kuznetsov

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?

22 Jun 2006 6:57 PM

Dieter Noeth

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 disadvantage is the missing RNo...

Dieter

25 Jun 2006 1:19 AM

Alexander Kuznetsov

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!

26 Jun 2006 2:18 PM

Dieter Noeth

Alexander Kuznetsov wrote:

> 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

Just tried it on my Teradata and it works (if there are more positive
than negative values)

> It is amazing how powerful and useful are OLAP functions, once you get
> used to them!

I use them a lot since several years (with old proprietary Teradata
syntax even before 1999) and they're really great :-)

Dieter

26 Jun 2006 5:52 AM

Roji. P. Thomas

What about

SELECT ISNULL(T1.Voucher, T2.Voucher) AS Voucher,
ISNULL(T1.Rno, T2.Rno) As Rno,
ISNULL(T1.Amount, T2.amount) As Amount
FROM
(SELECT Voucher, RNo, Amount,
(SELECT COUNT(1) FROM Table1 T2
   WHERE T2.RNo = T1.RNo
   AND T2.Amount = T1.Amount
   AND T2.Voucher > = T1.Voucher) AS SNo
FROM Table1 T1
WHERE T1.Amount >= 0) T1
FULL OUTER JOIN
(SELECT Voucher, RNo, Amount,
(SELECT COUNT(1) FROM Table1 T2
   WHERE T2.RNo = T1.RNo
   AND T2.Amount = T1.Amount
   AND T2.Voucher > = T1.Voucher) As Sno
FROM Table1 T1
WHERE T1.Amount < 0) T2
ON T1.Rno = T2.Rno
AND T1.Sno = T2.Sno
WHERE T1.Voucher IS NULL OR T2.Voucher IS NULL
ORDER BY 1

--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com

Show quote

"Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message
news:1150990877.767166.282540@y41g2000cwy.googlegroups.com...
> 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?
>

26 Jun 2006 1:48 PM

Alexander Kuznetsov

Roji,

I used this test data:

delete from Table1;
INSERT INTO Table1 (Voucher, RNo, Amount)
SELECT 101 , 'R1' , 100 UNION ALL
SELECT 2 , 'R1' ,-100 UNION ALL
SELECT 12 , '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 ;

and your query correctly returned a row for -100. That's a feat some
other queries in this thread failed to accomplish!

26 Jun 2006 2:12 PM

Dieter Noeth

Alexander Kuznetsov wrote:

Show quote

> delete from Table1;
> INSERT INTO Table1 (Voucher, RNo, Amount)
> SELECT 101 , 'R1' , 100 UNION ALL
> SELECT 2 , 'R1' ,-100 UNION ALL
> SELECT 12 , '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 ;
>
> and your query correctly returned a row for -100. That's a feat some
> other queries in this thread failed to accomplish!

But R1/50 and R1/25 were missing, i had to add another condition to the
join:
AND T1.Amount = -T2.Amount

Dieter

26 Jun 2006 3:10 PM

Roji. P. Thomas

> But R1/50 and R1/25 were missing, i had to add another condition to the
> join:
> AND T1.Amount = -T2.Amount

You are right :)

--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com

Show quote

"Dieter Noeth" <dno***@gmx.de> wrote in message
news:ulR6YqSmGHA.4952@TK2MSFTNGP04.phx.gbl...
> Alexander Kuznetsov wrote:
>
>> delete from Table1;
>> INSERT INTO Table1 (Voucher, RNo, Amount)
>> SELECT 101 , 'R1' , 100 UNION ALL
>> SELECT 2 , 'R1' ,-100 UNION ALL
>> SELECT 12 , '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 ;
>>
>> and your query correctly returned a row for -100. That's a feat some
>> other queries in this thread failed to accomplish!
>
>
> Dieter

26 Jun 2006 4:55 PM

--CELKO--

>>

how about this data?

1       r1              100
2       r1              100
3       r1              -100
4       r1              100
5       r1              100

what is the correct output? <<

The real problem is that the specs are too vague. What if we took a
differetn approach altogether and build a table like this?

CREATE TABLE VoucherPairs
(voucher_ref CHAR(2) NOT NULL,
debit_voucher_nbr INTEGER NOT NULL
   REFERENCES Vouchers(voucher_nbr)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
credit_voucher_nbr INTEGER -- null is unpaid
   REFERENCES Vouchers(voucher_nbr)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
CHECK (debit_voucher_nbr <> credit_voucher_nbr),
debit_voucher_amt INTEGER NOT NULL
CHECK debit_voucher_amt <= 0),
credit_voucher_amt INTEGER NOT NULL,
CHECK credit_voucher_amt >= 0),
CHECK (ABS (debit_voucher_amt) = crdit_voucher_amt),
PRIMARY KEY (voucher_ref, debit_voucher_nbr)
);

Having said all of this, SQL Server is going to chock on the REFERENCES
constraints as written, so drop the Vouchers table and just keep this
table. Ugly, but not ambigous.

27 Jun 2006 5:03 AM

Roji. P. Thomas

Ugh!
Who said the schema is correct? The OP posted this question many times and
disappeared.
We were just after an interesting T-SQL challenge. If we fix the schema then
what is the fun?

--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com

Show quote

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1151340926.205055.312150@u72g2000cwu.googlegroups.com...
>>>
> how about this data?
>
> 1       r1              100
> 2       r1              100
> 3       r1              -100
> 4       r1              100
> 5       r1              100
>
> what is the correct output? <<
>
> The real problem is that the specs are too vague. What if we took a
> differetn approach altogether and build a table like this?
>
> CREATE TABLE VoucherPairs
> (voucher_ref CHAR(2) NOT NULL,
> debit_voucher_nbr INTEGER NOT NULL
>   REFERENCES Vouchers(voucher_nbr)
>   ON DELETE CASCADE
>   ON UPDATE CASCADE,
> credit_voucher_nbr INTEGER -- null is unpaid
>   REFERENCES Vouchers(voucher_nbr)
>   ON DELETE CASCADE
>   ON UPDATE CASCADE,
> CHECK (debit_voucher_nbr <> credit_voucher_nbr),
> debit_voucher_amt INTEGER NOT NULL
> CHECK debit_voucher_amt <= 0),
> credit_voucher_amt INTEGER NOT NULL,
> CHECK credit_voucher_amt >= 0),
> CHECK (ABS (debit_voucher_amt) = crdit_voucher_amt),
> PRIMARY KEY (voucher_ref, debit_voucher_nbr)
> );
>
> Having said all of this, SQL Server is going to chock on the REFERENCES
> constraints as written, so drop the Vouchers table and just keep this
> table. Ugly, but not ambigous.
>

27 Jun 2006 2:06 PM

--CELKO--

>> We were just after an interesting T-SQL challenge. If we fix the schema then what is the fun? <<

LOL! Hey, I do SQL for a living and I hate challenges and adventures.
If I want fun, I go to the porno sites.

Seriously, I am getting up a PowerPoint slide show on how bad DDL leads
to bad DML as people try to construct what should have been base tables
via elaborate self-joins, etc. This is a mild example.

28 Jun 2006 5:31 AM

Roji. P. Thomas

> LOL! Hey, I do SQL for a living and I hate challenges and adventures.
> If I want fun, I go to the porno sites.

Hmmm. Remember SQL Puzzles and Answers ?

--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com

Show quote

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1151417163.162937.298230@u72g2000cwu.googlegroups.com...
>>> We were just after an interesting T-SQL challenge. If we fix the schema
>>> then what is the fun? <<
>
> LOL! Hey, I do SQL for a living and I hate challenges and adventures.
> If I want fun, I go to the porno sites.
>
> Seriously, I am getting up a PowerPoint slide show on how bad DDL leads
> to bad DML as people try to construct what should have been base tables
> via elaborate self-joins, etc. This is a mild example.
>

3 Jul 2006 12:21 PM

jsfromynr

Hi There,

You may like to try this
where tmpdata2 is your table

Select * from tmpdata2 T1
where Not Exists (
Select 1 from tmpData2 T2
Where T1.Code = T2.Code
And T1.Amount = -1 * T2.Amount
)

I hope this helps.

With warm regards
Jatinder Singh
http://jatindersingh.blogspot.com

Roji. P. Thomas wrote:
Show quote

> > LOL! Hey, I do SQL for a living and I hate challenges and adventures.
> > If I want fun, I go to the porno sites.
>
> Hmmm. Remember SQL Puzzles and Answers ?
>
> --
> Regards
> Roji. P. Thomas
> http://toponewithties.blogspot.com
> "--CELKO--" <jcelko***@earthlink.net> wrote in message
> news:1151417163.162937.298230@u72g2000cwu.googlegroups.com...
> >>> We were just after an interesting T-SQL challenge. If we fix the schema
> >>> then what is the fun? <<
> >
> > LOL! Hey, I do SQL for a living and I hate challenges and adventures.
> > If I want fun, I go to the porno sites.
> >
> > Seriously, I am getting up a PowerPoint slide show on how bad DDL leads
> > to bad DML as people try to construct what should have been base tables
> > via elaborate self-joins, etc. This is a mild example.
> >

3 Jul 2006 4:16 PM

Alexander Kuznetsov

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;
go

your query returns nothing:

>
> Select * from tmpdata2 T1
> where Not Exists (
> Select 1 from tmpData2 T2
> Where T1.Code = T2.Code
> And T1.Amount = -1 * T2.Amount
> )
>

Do you think it is correct?

4 Jul 2006 3:29 PM

jsfromynr

Thanks !!! Alexander
Even I think query should be tested by me.
Sorry for providing wron info.

What abt this??

Select Max(Voucher),Code,Sum(Amount) From Table2 group by
Code,abs(Amount)

With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com

Alexander Kuznetsov wrote:
Show quote

> 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;
> go
>
> your query returns nothing:
>
> >
> > Select * from tmpdata2 T1
> > where Not Exists (
> > Select 1 from tmpData2 T2
> > Where T1.Code = T2.Code
> > And T1.Amount = -1 * T2.Amount
> > )
> >
>
> Do you think it is correct?

4 Jul 2006 10:39 PM

Alexander Kuznetsov

> Select Max(Voucher),Code,Sum(Amount) From Table2 group by
> Code,abs(Amount)
>
Jatinder,

Consider this set of data:

INSERT INTO Table1 (Voucher, RNo, Amount)
SELECT 1 , 'R1' , 100 UNION ALL
SELECT 31 , 'R1' , 100;
go

I think the correct query should return 2 rows. What do you think?

22 Jun 2006 1:36 PM

Alexander Kuznetsov

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