September 19, 2013 at 5:10 pm
Hi all,
Looking for the best way to approach this with SQL.
In this test (dml/ddl below), I am looking to retrieve only rows that meet these conditions:
where a transaction number exists more than once and where at least one existence has CancelDueToCorrection ='Y',
any row that may not exist more than once - but when only once then those where CancelDueToCorrection ='Y' need to be omitted.
In this example, the query would return all rows except where TransactionNumber is 3.
TransactionNumber CancelDueToCorrectionCancelDueToMatch
1 N NULL
1 Y NULL
2 N NULL
3 Y NULL
4 N NULL
5 N NULL
5 N NULL
Thank you! S
script to create test scenario:
create table test
(TransactionNumber int, CancelDueToCorrection char(1))
insert into test
(TransactionNumber, CancelDueToCorrection)
values (1,'N')
insert into test
(TransactionNumber, CancelDueToCorrection)
values (1,'Y')
insert into test
(TransactionNumber, CancelDueToCorrection)
values (2,'N')
insert into test
(TransactionNumber, CancelDueToCorrection)
values (3,'Y')
insert into test
(TransactionNumber, CancelDueToCorrection)
values (4,'N')
insert into test
(TransactionNumber, CancelDueToCorrection)
values (5,'N')
insert into test
(TransactionNumber, CancelDueToCorrection)
values (5,'N')
September 19, 2013 at 5:24 pm
Try this.
select distinct TransactionNumber from
(
select TransactionNumber, CancelDueToCorrection, count(1) over( partition by TransactionNumber) as TransCount
, sum(case when CancelDueToCorrection = 'Y' then 1
else 0
end) over( partition by TransactionNumber) as CancelCount
from test
) A
where ( TransCount > 1 and CancelCount >=1 )
or ( TransCount = 1 and CancelCount = 0 )
September 19, 2013 at 5:36 pm
Thank you, Grasshopper. One of the criteris is not met with this query:
where a transaction number exists more than once and where at least one existence has CancelDueToCorrection ='Y',
The query you've provided does not return #5.
September 19, 2013 at 5:51 pm
I'm rereading the criteria, and maybe I need to state it differently:
If group TransactionNumber has at least one row with CancelDueToCorrection = 'Y', then that group count must be > 1 for that CancelDueToCorrection = 'Y' to be included.
Anything else is included.
Make sense?
September 19, 2013 at 5:55 pm
Please try this.
select distinct TransactionNumber from
(
select TransactionNumber, CancelDueToCorrection, count(1) over( partition by TransactionNumber) as TransCount
, sum(case when CancelDueToCorrection = 'Y' then 1
else 0
end) over( partition by TransactionNumber) as CancelCount
from test
) A
where ( CancelCount >=1 and TransCount > 1 )
or ( CancelCount < 1 )
September 19, 2013 at 6:08 pm
Grasshopper,
That works, thank you! I don't understand why, so I am going to read up on how this query is constructed. 🙂
Thank you again.
I suppose another alternative would be to use a subquery where TransactionNumber not in (identify groups with count(*) = 1 and CancelDueToCorrection = 'Y')
September 19, 2013 at 7:28 pm
Avoiding DISTINCT and perhaps a little simpler logic is:
SELECT TransactionNumber
FROM (
SELECT TransactionNumber, CancelDueToCorrection
,rn=ROW_NUMBER() OVER (PARTITION BY TransactionNumber ORDER BY CancelDueToCorrection)
FROM #Test
) a
WHERE rn=1 AND CancelDueToCorrection='N';
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 19, 2013 at 7:56 pm
That is great, too. Thank you, both.
S
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply