Query to show only where there is a difference

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Query to show only where there is a difference

Author

Topic

ismailm
Starting Member

13 Posts

Posted - 2012-07-17 : 10:18:44

Hi guys,

My first post/thread on this forum, I hope I won't be disappointed!
:-)

I need help with a SQL query.

I have in the same table 1) a Sales record and 2) a Refund record. This is travel data btw.

For each record I have a transaction number, which will be the same for both a Sale and Refund record.
I also have a Refund Indicator for each record, which says ‘Y’ or ‘N’ or ‘P’ (Part).

When there is a refund of a sales transaction, the records (both sale & refund) should be identical in terms of the class travelled etc.
For some reason, i have an issue where the class is different for the sale and the refund transaction. But this is not in all cases.

What I want the query to do is to bring up only those cases where there is a sale and a refund for the same transaction number BUT the class is different. I don’t want to see instances where the class is the same for both sale and refund.

I don't want to copy into excel if I can avoid it!

Looking forward to your responses.

Thanks!

P.S. this is the query I have used, which shows cases where the sale and refund class fields have the same values as well as those that don't have the same value... I need only those that don't have the same values for the same transaction number:

quote:

SELECT id.transactionnumber, id.refundindicator, id.class
FROM dba.table1 ID WITH(NOLOCK),dba.table2 CL WITH(NOLOCK)
WHERE(id.column1=cl.column1) AND (cl.column2=id.column2)
AND ID.invoicedate between '01-jan-2012' AND '30-jun-2012'
AND ID.column1 = 'abc123'
and id.transactionnumber in
(SELECT id.transactionnumber
FROM dba.table1 ID WITH(NOLOCK),dba.table2 CL WITH(NOLOCK)
WHERE(id.column1=cl.column1) AND (cl.column2=id.column2)
AND ID.invoicedate between '01-jan-2012' AND '30-jun-2012'
AND ID.column1 = 'abc123'
and id.refundindicator !='n')
order by id.transactionnumber

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-17 : 10:31:29

[code]
SELECT t.*
FROM dba.table1 t
INNER JOIN
(
SELECT id.transactionNumber
FROM dba.table1 ID WITH(NOLOCK),dba.table2 CL WITH(NOLOCK)
WHERE(id.column1=cl.column1) AND (cl.column2=id.column2)
AND ID.invoicedate between '01-jan-2012' AND '30-jun-2012'
AND ID.column1 = 'abc123'
AND id.refundindicater IN ('Y','N')
GROUP BY transactionNumber
HAVING COUNT(DISTINCT id.class)>1
)t1
ON t1.transactionNumber = t.transactionNumber
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ismailm
Starting Member

13 Posts

Posted - 2012-07-18 : 12:06:21

Thank you very much visakh16!
Exactly what I was looking for!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-18 : 12:12:02

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources