Tricky SQL query

SQL Problem

Select rtrim(qclient.clname) as 'Client Name',
qclient.clinum as 'Client Number',
qmatter.matnum as 'Matter Number',
qdocall.doctyp as 'Docket Type'
from (qclient inner join qmatter on qclient.clinum = qmatter.clinum)
left outer join qdocall
on qmatter.clinum = qdocall.clinum
and qmatter.matnum = qdocall.matnum
where qclient.clinum in (142)
and qdocall.doctyp is null  
union all
Select rtrim(qclient.clname) as 'Client Name',
qclient.clinum as 'Client Number',
qmatter.matnum as 'Matter Number',
qdocall.doctyp as 'Docket Type'
from (qclient inner join qmatter on qclient.clinum = qmatter.clinum)
left outer join qdocall
on qmatter.clinum = qdocall.clinum
and qmatter.matnum = qdocall.matnum
where qclient.clinum in (142)
and qdocall.doctyp not like 'Mickey Mouse'
and qdocall.doctyp not like 'Donald Duck'
and qdocall.doctyp not like 'Goofy'
and qdocall.doctyp not like 'Pluto';

The first part of the above query returns all desired records from the qmatter table
where there are no corresponding records in the qdocall table. I'm using the NULL
criteria and the UNION ALL statement because in the second part, the criteria I
use on the right table trumps the left outer join. Simple enough. But here is my
problem: In the second query, I exclude all records that have Mickey Mouse, Goofy,
Pluto, or Donald Duck in the doctyp field. I want records that have different doctyp
values. HOWEVER, if a record ONLY has one of the excluded doctype values, or even more
than one of the excluded doctyp values (say, 3 from that list), and NO OTHER
doctyp value, then (and I'm not sure if this is possible) RETURN that record only
ONCE, and put another value in the doctyp field (example, DISNEYLAND).

So, clear as mud?
crafuseAsked:
Who is Participating?
 
UnifiedISConnect With a Mentor Commented:
Okay, I think we need to adjust the case statements a bit to get all the scenarios covered:
Result = CASE WHEN (A.DisneyCount = 0 AND B.DisneyCount = 0) OR (A.DisneyCount IS NULL AND B.NotDisneyCount IS NULL)  THEN 'NoDocTyp' --no doctype exisits
      WHEN (B.NotDisneyCount > 0) THEN 'NotAllDisney'  --some non disney doctyp exists
      WHEN (A.DisneyCount > 0 AND (B.NotDisneyCount = 0 OR B.NotDisneyCount IS NULL)) THEN 'AllDisney' --all doctyp are disney
END

I think all the possibilities are covered now
0
 
UnifiedISCommented:
To avoid the where clause trumping your left join, move your criteria to the join
left outer join qdocall
on qmatter.clinum = qdocall.clinum
and qmatter.matnum = qdocall.matnum
and qdocall.doctyp not like 'Mickey Mouse'
and qdocall.doctyp not like 'Donald Duck'
and qdocall.doctyp not like 'Goofy'
and qdocall.doctyp not like 'Pluto'
where qclient.clinum in (142)

0
 
crafuseAuthor Commented:
UnifiedIS -

Nice. I may well look at that. The things is, my application is a report builder that generates dynamic SQL, and the query above was just one example of a query that I build and send to SQL Server. Otherwise, any ideas about my problem? If I incorporated your change, would it help ease a solution to my problem?

Thanks,
crafuse
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
UnifiedISCommented:
I think you'll want to incorporate counts of doctyp under a couple different scenarios and then compare them to determine what to do next.  
Select rtrim(qclient.clname) as 'Client Name',
qclient.clinum as 'Client Number',
qmatter.matnum as 'Matter Number',
qdocall.doctyp as 'Docket Type'
Result = CASE WHEN A.DisneyCount - B.NotDisneyCount<= 0 THEN "AllDisney"
ELSE "NotAllDisney"
END
from (qclient inner join qmatter on qclient.clinum = qmatter.clinum)
LEFT OUTER JOIN
(SELECT COUNT(doctyp) AS DisneyCount, clinum, matnum FROM qdocall WHERE doctyp In ('Mickey Mouse', 'Donald Duck', 'Goofy', 'Pluto') GROUP BY clinum, matnum) AS A
ON A.clinum = qmatter.clinum AND A.matnum = qmatter.matnum
LEFT OUTER JOIN
(SELECT COUNT(doctyp) AS NotDisneyCount, clinum, matnum FROM qdocall WHERE doctyp IS NOT NULL AND doctype NOT In ('Mickey Mouse', 'Donald Duck', 'Goofy', 'Pluto') GROUP BY clinum, matnum) AS B
ON B.clinum = qmatter.clinum AND B.matnum = qmatter.matnum
0
 
crafuseAuthor Commented:
UnifiedIS,

Thanks, this is going to take me a little time to sort out. I'll get back to you on this.

crafuse
0
 
crafuseAuthor Commented:
made few changes (only syntax) but getting errors. I've put in what I changed, and pasted errors below. any suggestions?

Select rtrim(qclient.clname) as 'Client Name',
qclient.clinum as 'Client Number',
qmatter.matnum as 'Matter Number',
qdocall.doctyp as 'Docket Type',   -- added comma
Result = CASE WHEN (A.DisneyCount - B.NotDisneyCount <= 0) THEN "AllDisney" --added brackets
ELSE "NotAllDisney"
END
from (qclient inner join qmatter on qclient.clinum = qmatter.clinum)
LEFT OUTER JOIN
(SELECT COUNT(qdocall.doctyp) AS DisneyCount, qdocall.clinum, qdocall.matnum    --added tablename notation
FROM qdocall WHERE qdocall.doctyp In ('Mickey Mouse', 'Donald Duck', 'Goofy', 'Pluto')  --same
GROUP BY qdocall.clinum, qdocall.matnum) AS A  ---same
ON A.clinum = qmatter.clinum AND A.matnum = qmatter.matnum  ---same
LEFT OUTER JOIN
(SELECT COUNT(qdocall.doctyp) AS NotDisneyCount, qdocall.clinum, qdocall.matnum
FROM qdocall
WHERE qdocall.doctyp IS NOT NULL
AND qdocall.doctyp NOT In ('Mickey Mouse', 'Donald Duck', 'Goofy', 'Pluto') GROUP BY qdocall.clinum, qdocall.matnum) AS B
ON B.clinum = qmatter.clinum AND B.matnum = qmatter.matnum;

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "qdocall.doctyp" could not be bound.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'AllDisney'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'NotAllDisney'.

Thanks,
crafuse
0
 
crafuseAuthor Commented:
I think I've "almost" got it working, but when I try to run it with the select including line 4 (qdocall.doctyp as 'Docket Type',) I get the following error:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "qdocall.doctyp" could not be bound.

Otherwise, this is what it looks like now...

Select rtrim(qclient.clname) as 'Client Name',
qclient.clinum as 'Client Number',
qmatter.matnum as 'Matter Number',
qdocall.doctyp as 'Docket Type',
Result = CASE WHEN (A.DisneyCount - B.NotDisneyCount <= 0) THEN 'AllDisney'
ELSE 'NotAllDisney'
END
from qclient inner join qmatter on qclient.clinum = qmatter.clinum
LEFT OUTER JOIN
(SELECT COUNT(qdocall.doctyp) AS DisneyCount, qdocall.clinum, qdocall.matnum
FROM qdocall
WHERE qdocall.doctyp In ('Mickey Mouse', 'Donald Duck', 'Goofy', 'Pluto')
GROUP BY clinum, matnum) AS A
ON A.clinum = qmatter.clinum AND A.matnum = qmatter.matnum
LEFT OUTER JOIN
(SELECT COUNT(qdocall.doctyp) AS NotDisneyCount, qdocall.clinum, qdocall.matnum
FROM qdocall
WHERE qdocall.doctyp IS NOT NULL
AND qdocall.doctyp NOT In ('Mickey Mouse', 'Donald Duck', 'Goofy', 'Pluto')
GROUP BY clinum, matnum) AS B
ON B.clinum = qmatter.clinum AND B.matnum = qmatter.matnum;

Bear in mind, please, that the results have to include all doctyps other than those excluded, and a single record for those matters that only have those doctyps explicitly exluded, with anyword\phrase in the place of the qdocall.doctyp value(s).

Thanks,
crafuse

0
 
crafuseAuthor Commented:
UnifiedIS,

Hate to bother you about this, just wondering if you're still interested in this problem?

Thanks,
crafuse
0
 
UnifiedISCommented:
qdocall is no longer a table alias
get rid of this: qdocall.doctyp as 'Docket Type',
and change result to 'Docket Type' in the line below:
'Docket Type' = CASE WHEN (A.DisneyCount - B.NotDisneyCount <= 0) THEN 'AllDisney'
0
 
crafuseAuthor Commented:
Thanks for that, I guess I had it going yesterday when I simply went with the 'Result' name.

My question, though, is this: My problem included returning qdocall.doctyp values where they are not explicitly excluded in the 'not like' list, as well as a descriptive value where there are only records in qdocall matching the excluded values (in this case, I'm assuming 'NotAllDisney' is that result?).

Thanks,
crafuse
0
 
crafuseAuthor Commented:
Sweet. Getting there. Still need to display the real doctyp values\records when 'NotAllDisney'. And maybe more. I need to find good examples to test various scenarios. Thanks so much for the help so far! Let me know if you'd like me to open a new question for the second part of the requirement, that the query displays all records for those that are not explicitly excluded but exist. I'd be ok with that cause I do think this is a pretty tricky question.

Thanks,
crafuse
0
 
UnifiedISCommented:
Either way is fine with me, I'm glad we're making some progress
0
 
crafuseAuthor Commented:
UnifiedIS -

Your logic\code is doing precisely as requested. I'm hoping now to be able to bring back all records\docket types not explicitly excluded. Do you think you can also help me with that?

Thanks,
crafuse
0
 
UnifiedISCommented:
I'm willing to help but it may be a few days before I get a chance
0
 
crafuseAuthor Commented:
Going to close this question and open another to deal with the processing of the records. Am thinking the following:

1. If there are one or more docket types per clinum\matnum that are to be excluded, return only 1 record for with a docket type = 'No Due Date'

Example, if there are 4 doctyps = 'Mickey Mouse'
return only one record and say doctyp = 'No Due Date'

2. If there are also docket types that are not in the excluded list, return each of them with their actual docktyp value.

Am thinking it might be easier to process records in a temp table stored locally, anyways will give this some thought over the weekend.

Will close this and include link to new question Monday morning.

Thanks,
crafuse

0
 
crafuseAuthor Commented:
UnifiesIS -

I opened up a new question. Thanks for your help on this one! Let me know if you think the new question is a little clearer.

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22909785.html

thanks,
crafuse
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.