Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

best way to approach this query Expand / Collapse
Author
Message
Posted Thursday, September 19, 2013 5:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 8:46 AM
Points: 15, Visits: 162
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 CancelDueToCorrection CancelDueToMatch
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')




Post #1496652
Posted Thursday, September 19, 2013 5:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 12:01 AM
Points: 26, Visits: 65
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 )
Post #1496653
Posted Thursday, September 19, 2013 5:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 8:46 AM
Points: 15, Visits: 162
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.
Post #1496654
Posted Thursday, September 19, 2013 5:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 8:46 AM
Points: 15, Visits: 162
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?
Post #1496656
Posted Thursday, September 19, 2013 5:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 12:01 AM
Points: 26, Visits: 65
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 )
Post #1496657
Posted Thursday, September 19, 2013 6:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 8:46 AM
Points: 15, Visits: 162
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')
Post #1496659
Posted Thursday, September 19, 2013 7:28 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 3,590, Visits: 5,098
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1496667
Posted Thursday, September 19, 2013 7:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 8:46 AM
Points: 15, Visits: 162
That is great, too. Thank you, both.

S
Post #1496671
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse