|
|
You're in the
sql server programming
group:can a SELECT return this result ?!
sql server programming:
SELECT MAX( Voucher ), RNo, SUM( Amount ) FROM tbl GROUP BY RNo, ABS( Amount ) ; -- Anith
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
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 [quoted text, click to view] "Anith Sen" wrote:
> Do: > > SELECT MAX( Voucher ), RNo, SUM( Amount ) > FROM tbl > GROUP BY RNo, ABS( Amount ) ; > > -- > Anith > >
Anith,
[quoted text, click to view] > 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
[quoted text, click to view]
>> 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
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?
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
[quoted text, click to view]
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...
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!
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 [quoted text, click to view] "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? >
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!
[quoted text, click to view]
>> 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.
[quoted text, click to view]
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! But R1/50 and R1/25 were missing, i had to add another condition to the join: AND T1.Amount = -T2.Amount
[quoted text, click to view]
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) [quoted text, click to view] > 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 :-)
[quoted text, click to view]
> 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 [quoted text, click to view] "Dieter Noeth" <dnoeth@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
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 [quoted text, click to view] "--CELKO--" <jcelko212@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. >
[quoted text, click to view]
>> 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.
[quoted text, click to view]
> 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 [quoted text, click to view] "--CELKO--" <jcelko212@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. >
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 [quoted text, click to view] Roji. P. Thomas wrote:
> > 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--" <jcelko212@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. > >
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: [quoted text, click to view] > > 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?
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 [quoted text, click to view] Alexander Kuznetsov wrote:
> 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?
[quoted text, click to view]
> Select Max(Voucher),Code,Sum(Amount) From Table2 group by Jatinder,> Code,abs(Amount) > 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?
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |