How do I use a SQL Server 2012 window function to make my query?

advertisements

Let's say I have a table of repair tickets that looks like so:

Group   Ticket  Status
----------------------------
00798   299696  Open
21851   574587  Complete
21851   574588  Pending
21852   574589  Office
21866   574613  Test
21869   574617  Shipping
21870   574618  Repair
21871   574620  Open
32102   369151  Repair
43316   393920  Office
60669   433162  Office
65850   445815  Open
65999   446267  Complete
66215   446841  Repair
77818   473653  Complete
79691   477963  Office
82277   483787  Pending
86283   493697  Complete
86283   493698  Complete

I am trying to get a list of all tickets whose status is complete ONLY if every ticket in the group is complete. Thus, from this data set, I would get these tickets:

446267
473653
493697
493698

I would NOT get 574587 because there is another ticket (574588) in its group (21851) that is set to pending, not complete.

I've tried variations of ROW_NUMBER() OVER (PARTITION BY GroupNumber, [Status] ORDER BY GroupNumber) as a subquery but can't wrap my head around this. Maybe it can just be done with group by and a subquery? Thanks.


SQL Fiddle: http://sqlfiddle.com/#!6/62ecb/2

Either of these should work:

--get every record
select *
from RepairTickets a
--where the status is complete
where [status] = 'Complete'
--and the status matches every...
and [status] = all
(
    --other record's status
    select [status]
    from RepairTickets b
    --in the same group
    where b.[Group] = a.[Group]
)

or

--get all records
select *
from RepairTickets a
--where the status is complete
where [status] = 'Complete'
--where there is not
and not exists
(
    --any record
    select top 1 1
    from RepairTickets b
    --in the same group
    where b.[Group] = a.[Group]
    --but with a different status
    and b.[Status] != a.[Status]
)

NB: in both of the above, since we know we're only looking for Complete records we could optimise by replacing some of the references to a.[Status] relating to the inner queries; however what's above should be easier to modify, since you only need to declare the status you're interested in in one place.