All the kids have brown hair but the Dad is a red head

Question

All the kids have brown hair but the Dad is a red head

sextonk

Old Hand

Points: 326
More actions
December 3, 2009 at 2:24 pm

#143282

We have a ticketing system where people close their individual assignments but they forget to close the main ticket. (I need to find those tickets)
Main ticket is stored in callLog table.
Assignments are stored in Asgnmnt table:
select l.* from calllog l, asgnmnt a
where a.callid = l.callid and
l.callstatus<>'Closed' and
a.Resolution = 'Completed' --but this needs to be true for all assignments
thanks,
Kevin

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

A usual way to do this is with either a Not Exists statement, or by counting the total records and the number of records in the status you want and checking that the counts are equal. Not Exists usually works better, but a count check can do more complex validations.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

sextonk Old Hand Points: 326 More actions · Answer 2

Let me provide an example:

Ticket# = 001

Status='Open'

Assignment A='Closed'

Assignment B='Open' --that's fine. Not interested in this ticket

Ticket# = 002

Status='Open'

Assignment A='Closed'

Assignment B='Closed'

Assignment C='Closed' --these are the tickets I need to find. It is a one-to-many relationship with parent table.

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 3

SELECTcl.*

FROMdbo.CallLog

INNER JOIN(

SELECTCallID

FROMAsgnmnt

GROUP BYCallID

HAVINGMIN(CASE WHEN Resolution = 'Completed' THEN 1 ELSE 0 END) = 1

) AS x ON x.CallID = cl.CallID

N 56°04'39.16"
E 12°55'05.25"

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Untested as you didn't provide the necessary DDL or sample data:

select

cl.*

from

dbo.calllog cl

where

not exists(select 1 from dbo.asgnmnt asg where cl.callid = asg.callid and asg.Resolution = 'Completed')

and cl.callstatus <> 'Closed';