February 18, 2011 at 4:49 pm
How about something really simple like this:
SELECT
C.CaseID
FROM tblcase C
WHERE
C.closedate = ''
AND c.casetypeid in(100, 101, 102, 131)
AND EXISTS (SELECT NULL FROM tblCaseLog CL WHERE CL.Caseid = C.Caseid)
AND NOT EXISTS (SELECT NULL FROM tblCaseLog CL
WHERE CL.Caseid = C.Caseid AND CL.closedate = '');
On EXISTS to make sure there is a row in the case log table, and one EXISTS to make sure there isn't an open one. Can you let me know if that works? (I'm not sure about the ActiveStatus criteria you mentioned, so it may need tweaked.)
You may have to change the CL.closedate = '' to CL.closedate IS NULL. (The little sample data you posted used NULL for the closedate column.)
I didn't see a script to setup sample data or I would have tested it.
February 18, 2011 at 4:51 pm
tacy.highland (2/18/2011)
That doesn't quite work either. I tried that in the earlier post (the "variation"). I've found other Cases which should be included in my resultset but aren't. And the ones which aren't are ones that have that extra case with ActivateStatus of 0 (and those happen to also have a blank closedate). Would that last part, the closedate being blank have anything to do with why they're being excluded? Do we need to add an extra part to the where clause...?something like this:
(SELECT DISTINCT caseIDFROM tblCaseLog
WHERE closedate = ''
OR ActivateStatus = 0
OR (CloseDate = '' and ActivateStatus = 0)) AS drv
Or am I just way off base?
I thought I was following this up until now, but now I am really confused. You are saying that cases which should have been included in your resultset are ones which have an ActivateStatus of 0 and a blank closedate? (I'm presuming that when you say "blank closedate" here you are referring to the tblCaseLog.CloseDate and not the tblCase.closedate.) If so, that seems to contradict the part where you said that you only wanted cases where there are no records where tblCaseLog.CloseDate is blank. Your code above would make no change to the results, but I am not clear on what results you actually want.
Another thing: you said earlier (much earlier) that it would not work if it returned results where there was an entry in tblCase but no entries in tblCaseLog but in this latest round you seem to have abandoned that as a requirement (at least none of the code nor your verbal descriptions acknowledge it Note: I see now that UMG Developer's post did acknowledge it but, apparently, he posted it while I was busy writing mine). Presuming that this latter item is truly a requirement and this latest bit about ActivateStatus is just some big misunderstanding, I will post the following as my take on the solution but I may just have no idea what you are really after:
select DISTINCT c.caseID
FROM tblCase AS c
INNER JOIN tblCaseLog cl
ON c.caseID = cl.caseID
WHERE c.Closedate = ''
and c.casetypeid in(100, 102, 131)
AND NOT EXISTS
(
SELECT 1
FROM tblCaseLog cl2
WHERE cl2.caseID = cl.caseID
AND (cl2.closedate = '' OR cl2.ActivateStatus = 0)
)
- Les
February 18, 2011 at 4:57 pm
Les,
I see you basically came up with the same solution as mine, but you didn't alias the tblCaseLog table in your NOT EXISTS query, so it has ambiguous columns and that can cause problems.
February 18, 2011 at 5:05 pm
UMG Developer (2/18/2011)
Les,I see you basically came up with the same solution as mine, but you didn't alias the tblCaseLog table in your NOT EXISTS query, so it is ambiguous and can cause problems.
Really? I've been doing correlated subqueries that way for quite awhile and never had any problems. I would have thought that if the compiler did not give precedence to the table within the context of the subquery (which is how I believed that it worked) that it would cite it as ambiguous and error out.
Thanks for the tip.
-- Les
February 18, 2011 at 5:08 pm
lnoland (2/18/2011)
Really? I've been doing correlated subqueries that way for quite awhile and never had any problems. I would have thought that if the compiler did not give precedence to the table within the context of the subquery (which is how I believed that it worked) that it would cite it as ambiguous and error out.
Maybe it is just Oracle that has this problem, but I alias everything and know that I will always get exactly what I want.
Also, you would need to add a DISTINCT to your query to prevent duplicate CaseIDs from appearing when there is more than one detail record.
February 18, 2011 at 5:10 pm
One of the problems came back to me that occurs when you use a column from a table outside the sub-query without qualifying it, because it is unique now, and someone adds a column by the same name to a table contained inside your sub-query. At that point the results of your query change, and you get no warnings.
February 18, 2011 at 5:14 pm
UMG Developer (2/18/2011)
lnoland (2/18/2011)
UMG Developer (2/18/2011)
Really? I've been doing correlated subqueries that way for quite awhile and never had any problems. I would have thought that if the compiler did not give precedence to the table within the context of the subquery (which is how I believed that it worked) that it would cite it as ambiguous and error out.Maybe it is just Oracle that has this problem, but I alias everything and know that I will always get exactly what I want.
Also, you would need to add a DISTINCT to your query to prevent duplicate CaseIDs from appearing when there is more than one detail record.
At any rate, the alias does have the advantage of disambiguating the situation for the reader whether SQL Server needs it or not. As for the DISTINCT, that was a pure oversight from writing off-the-cuff code. I have corrected both to avoid misleading anyone.
Thanks again.
- Les
February 18, 2011 at 6:10 pm
tacy.highland (2/18/2011)
That doesn't quite work either. I tried that in the earlier post (the "variation"). I've found other Cases which should be included in my resultset but aren't. And the ones which aren't are ones that have that extra case with ActivateStatus of 0 (and those happen to also have a blank closedate). Would that last part, the closedate being blank have anything to do with why they're being excluded? Do we need to add an extra part to the where clause...?something like this:
(SELECT DISTINCT caseIDFROM tblCaseLog
WHERE closedate = ''
OR ActivateStatus = 0
OR (CloseDate = '' and ActivateStatus = 0)) AS drv
Or am I just way off base?
UMG and Imoland have pretty much summed up my new confusion above.
I've found other Cases which should be included in my resultset but aren't.
By what rules?
And the ones which aren't are ones that have that extra case with ActivateStatus of 0
Alright, let's try restating this then...
You want all items from tblCase with a blank closedate that do not have an item in tblCaseLog with a blank closedate, but ignoring any records with activestatus = 0 while checking for this exclusion rule?
In that case I believe you want this:
select
c.caseID
FROM
tblCase AS c
LEFT JOIN
(SELECT DISTINCT caseID FROM tblCaseLog WHERE closedate = '' and ActiveStatus <> 0) AS drv
ON c.caseID = drv.CaseID
WHERE
c.Closedate = ''
AND drv.CaseID IS NULL
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 18, 2011 at 7:53 pm
ON A.caseid = D.caseid
where A.closedate = ' '
group by D.caseid
having sum(case when D.closedate = ' ' then 1 else 0 end) = 0
February 22, 2011 at 11:08 am
Craig! I think that's it! Wow, what a difference a little <> makes. hehe
Your query does bring up all the caseids that are still open, and that have associated caselogids which are all closed and exclude any caselogids that have an activatestatus of 0. (so I basically only want to see the caselogids that are activatestatus of 1 and are all closed for the caseid).
Thank you! This has been such a great help, I very much appreciate everyone's input on this.
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply