SQL Queries

Hi,

This is my first question. We have a table called Assignments, in which there are CallIDs and WhoResolved them. There are more columns as well.  I am trying to write 2 queries to select the callids from this table where all have been resolved and where not. So for example, if this is the data in the Assignments Table

SeqNo                      CallID                      WhoResolved
1379450910      00368660      
1379369195      00368656      tdobb
1379369194      00368656      hdss
1379367705      00368654      bhungle
1379366943      00368653      mromin
1379449761      00368650      NULL
1379364707      00368650      atg
1379365140      00368648      cpereda
1379364458      00368648      
1379244582      00368648      asif


Then Query 1 should return to me

CallIDs
368656
368654
368653

And Query 2 should return to me

CallIDS
368660
368650
368648

But I am having difficulty in writing the queries

Thank you
RusselMetalsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SStoryCommented:
Depends if non resolves are really NULL, have the keyword NULL or are empty spaces.

For unresolved

Assuming "" string

select *
from assignments
where WhoResolved=""

Assuming "NULL" string
select *
from assignments
where WhoResolved="NULL"

Assuming actual NULL value
select *
from assignments
where ISNULL(WhoResolved)

do a NOT ISNULL()
<>"NULL"
or <>""

for the converse.
0
nishant joshiTechnology Development ConsultantCommented:
NULLIF function will solve your problem :

SELECT *
FROM Assignments
WHERE NULLIF(WhoResolved,'') IS NOT NULL

SELECT *
FROM Assignments
WHERE NULLIF(WhoResolved,'') IS NULL

Open in new window


Nishant
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Congratulations on asking your first question.  Any relation to IntegrisMetals or Ryerson?

btw Whenever you see a number with leading zeros it means it's a character value, so for it to not show leading zeros it should be converted to a number, i.e. CAST(CallID as int).
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

David ToddSenior DBACommented:
Hi,

Nishant's first query doesn't return the correct set, because there are multiple lines in Assignments with the same CallID.

But using his second query, I've been able to deduce a query that correctly returns the closed calls.

Regards
  David

use ExpertsExchange
go

if object_id( N'tempdb..#Assignments', N'U' ) is not null 
	drop table #Assignments;
	
create table #Assignments(
	SeqNo int
	, CallID int
	, WhoResolved nvarchar( 25 )
	)
	
insert #Assignments( SeqNo, CallID, WhoResolved )
	values( 1379450910,      00368660, '' )      
	, ( 1379369195,      00368656,      'tdobb' )
	, ( 1379369194,      00368656,      'hdss' )
	, ( 1379367705,      00368654,      'bhungle' )
	, ( 1379366943,      00368653,      'mromin' )
	, ( 1379449761,      00368650,      NULL )
	, ( 1379364707,      00368650,      'atg' )
	, ( 1379365140,      00368648,      'cpereda' )
	, ( 1379364458,      00368648,      '' )
	, ( 1379244582,      00368648,      'asif' )
;

select *
from #Assignments

-- Nishant 
select distinct CallID
from #Assignments
where
	nullif( WhoResolved, '' ) is not null
;

select distinct CallID
from #Assignments
where
	nullif( WhoResolved, '' ) is null
;

-- dtodd corrected
select distinct a.CallID
from #Assignments a
where
	a.CallID not in (
		select ai.CallID
		from #Assignments ai
		where
			nullif( ai.WhoResolved, '' ) is null
		)		
;

Open in new window

0
RusselMetalsAuthor Commented:
Thanks, NULL is the database Null and not a string. The callID is a string so the 00 show up
0
David ToddSenior DBACommented:
Hi,

Corrected for CallID being a string

use ExpertsExchange
go

if object_id( N'tempdb..#Assignments', N'U' ) is not null 
	drop table #Assignments;
	
create table #Assignments(
	SeqNo int
	, CallID nvarchar( 20 )
	, WhoResolved nvarchar( 25 )
	)
	
insert #Assignments( SeqNo, CallID, WhoResolved )
	values( 1379450910,      '00368660', '' )      
	, ( 1379369195,      '00368656',      'tdobb' )
	, ( 1379369194,      '00368656',      'hdss' )
	, ( 1379367705,      '00368654',      'bhungle' )
	, ( 1379366943,      '00368653',      'mromin' )
	, ( 1379449761,      '00368650',      NULL )
	, ( 1379364707,      '00368650',      'atg' )
	, ( 1379365140,      '00368648',      'cpereda' )
	, ( 1379364458,      '00368648',      '' )
	, ( 1379244582,      '00368648',      'asif' )
;

select *
from #Assignments

-- nishantcomp2512
select distinct CallID
from #Assignments
where
	nullif( WhoResolved, '' ) is not null
;

select distinct CallID
from #Assignments
where
	nullif( WhoResolved, '' ) is null
;

-- dtodd corrected
select distinct a.CallID
from #Assignments a
where
	a.CallID not in (
		select ai.CallID
		from #Assignments ai
		where
			nullif( ai.WhoResolved, '' ) is null
		)		
;

Open in new window


Regards
  David
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
-- all resolved
SELECT DISTINCT CallID
FROM #Assignments
WHERE
    WhoResolved IS NULL OR WhoResolved = ''
ORDER BY CallID

-- not all resolved
SELECT CallID
FROM #Assignments
GROUP BY CallID
HAVING MIN(CASE WHEN WhoResolved > '' THEN 1 ELSE 0 END) = 0
ORDER BY CallID
0
RusselMetalsAuthor Commented:
Thank you Everyone for your answers. The one that worked correctly for what I needed was the nested one.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.