December 29, 2009 at 9:43 am
tacy.highland (12/29/2009)
See, the thing is, I'm writing the query in SQL2008 Management Studio, but I'm running it against a SQL2000 db. (perhaps this post shouldn't be in the SS2K8 section?). So the Except option won't work for me. At any rate, any other suggestions? I'm running out of ideas.All I want is to get a list of the cases that have all of their caselogs closed. (all I want... Sounds so simple. ha.)
Help?
Tacy, I think sturner's solution should have worked for you. One thing I am seeing is that you are using empty strings in your where clauses looking for dates...
SELECT D.CaseID
FROM dbo.tblCase A
INNER JOIN dbo.tblCaseLog D
ON A.CaseID = D.CaseID
WHERE A.CloseDate = ' ' --<<< Like here
AND A.CaseTypeID in(100, 101, 102, 131)
GROUP BY D.CaseID,D.ReportDate,A.CasePriorityID
HAVING SUM(CASE WHEN D.CloseDate = ' ' THEN 1 ELSE 0 END) = 0 --<<< And here
Does your front end send empty strings of one character legnth for this field? If so, then you may not have an issue. However, if the field is really NULL, then it will never evaluate to anything, in which case you would need to change this...
WHERE A.CloseDate = ' '
to this...
WHERE A.CloseDate IS NULL
And so forth for the rest of your code. Also, the create table code and sample data you posted don't match. And, is there a reason why you are doing this in your sample data...?
SELECT '138196','57571',' ' UNION ALL
You are inserting string of 10 or so spaces, and I am wondering if this is something your app does, or if you meant for them to be NULL. Take a look at this, and I made some assumptions which are: 1) made the table columns match what is in your insert statements 2) put the identity property on CaseLogID since there are duplicates in the CaseID column, so it can not be the primary key, and 3) changed the empty strings on the inserts to NULL.
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
CaseID INT, --Is an IDENTITY column on real table
CaseLogID INT IDENTITY(1,1),--<<<Should this be the IDENTITY column?
CloseDate SMALLDATETIME
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #MYTABLE
(CASEID, CASELOGID, CLOSEDATE)
SELECT '137903','57150',NULL UNION ALL
SELECT '137957','57231',NULL UNION ALL
SELECT '137969','57249','12/10/2009' UNION ALL
SELECT '137986','57269',NULL UNION ALL
SELECT '137986','57271','11/27/2009' UNION ALL
SELECT '138002','57291',NULL UNION ALL
SELECT '138002','57293','11/25/2009' UNION ALL
SELECT '138004','57296',NULL UNION ALL
SELECT '138004','57297','11/27/2009' UNION ALL
SELECT '138030','57327','12/01/2009' UNION ALL
SELECT '138030','57328','12/11/2009' UNION ALL
SELECT '138032','57336',NULL UNION ALL
SELECT '138037','57354','12/09/2009' UNION ALL
SELECT '138037','57355',NULL UNION ALL
SELECT '138037','57356','12/08/2009' UNION ALL
SELECT '138047','57374',NULL UNION ALL
SELECT '138050','57380','12/10/2009' UNION ALL
SELECT '138051','57382',NULL UNION ALL
SELECT '138055','57387','12/09/2009' UNION ALL
SELECT '138068','57403','12/07/2009' UNION ALL
SELECT '138068','57405','12/10/2009' UNION ALL
SELECT '138096','57431',NULL UNION ALL
SELECT '138098','57433','12/10/2009' UNION ALL
SELECT '138098','57434','12/10/2009' UNION ALL
SELECT '138102','57438',NULL UNION ALL
SELECT '138106','57449','12/02/2009' UNION ALL
SELECT '138106','57450','12/10/2009' UNION ALL
SELECT '138109','57454','12/10/2009' UNION ALL
SELECT '138109','57455',NULL UNION ALL
SELECT '138112','57458',NULL UNION ALL
SELECT '138128','57460',NULL UNION ALL
SELECT '138132','57467','12/10/2009' UNION ALL
SELECT '138132','57468','12/10/2009' UNION ALL
SELECT '138144','57485',NULL UNION ALL
SELECT '138160','57509',NULL UNION ALL
SELECT '138168','57535',NULL UNION ALL
SELECT '138168','57536',NULL UNION ALL
SELECT '138196','57571',NULL UNION ALL
SELECT '138196','57574','12/09/2009' UNION ALL
SELECT '138291','57692',NULL
Now, back to what sturner was showing you, but in two steps. This will show you all the CaseID's that do not have any open line items. You should be able to take the concept and apply it to your own test environment. I'm doing it on SQL 2008, but I am confident it will also work for 2000.
SELECT
CaseID
FROM
(--Derived table t1 counts a 1 for every line item with a null
--CloseDate field. The group by CaseID will aggregate the 1's
--with the result being that any CaseID with any value greater
--then 0 will have open cases, and should not be displayed.
SELECT
CaseID,
OpenCases = SUM(CASE WHEN CloseDate IS NULL THEN 1 ELSE 0 END)
FROM #MyTable
GROUP BY CaseID
) t1
WHERE OpenCases = 0
ORDER BY CaseID
I hope this helps simplify things for you.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
December 29, 2009 at 10:34 am
Oh man, that's great stuff. Perhaps I got a tad confused before looking at it from a different perspective. Basically eliminating the open complaints in order to find the ones I wanted. (gotta train the brain to look at it from different angles)
I modified the query to suit my needs and pull from the necessary tables and it works beautifully.
Thank you Greg, and everyone else who jumped in, for the time and effort expended on this. It is very much appreciated!
December 29, 2009 at 10:05 pm
this should work as well and would allow you to bring back other fields from the tblCase table
Select A.CaseID
from dbo.tblCase A
where A.closedate = ' '
and A.CaseTypeID = 100
and(select count(Caseid)
from dbo.view_CaseLogDetail
where closedate = ' '
and CaseID=A.CaseID) =0
December 30, 2009 at 9:53 am
Excellent! Thank you for that variation!
This one works even better for me since I do need to pull other columns.
This is so great. Thank you again!
December 30, 2009 at 12:55 pm
Oman. Ok, I sound like a broken record here, but my initial validation process didn't identify a hidden issue...
I just noticed that the last query is including cases that don't have ANY record in the caselog table, which isn't going to work. Arrggggg. What did I miss here?
December 30, 2009 at 1:45 pm
I was wondering how long it would take you to realize you still had issues. You indicated you liked the last solution better because you could pull in extra columns. My previous solution was simply to show you how to isolate your desired cases, and it can be used against other views or tables, like so, where I am simply incorporating my last post into another query...
SELECT
cld.*
FROM view_CaseLogDetail cld INNER JOIN
(--Yet another derived table, and could be eliminated with CTE's
--if you were not using 2000
SELECT
CaseID
FROM
(
SELECT
CaseID,
OpenCases = SUM(CASE WHEN CloseDate IS NULL THEN 1 ELSE 0 END)
FROM #MyTable
GROUP BY CaseID
) t1
WHERE OpenCases = 0
--ORDER BY CaseID
) t2
ON cld.CaseID = t2.CaseID
And I don't have the details of viewCaseLogDetail, so I can't test it, but you should be able to get the idea. You could also probably incorporate the two derived tables into one, but I like to keep tasks separate so it is easier for me to trouble shoot.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
December 30, 2009 at 1:55 pm
You might find this one easier to look at.
SELECT
cld.*
FROM viewCaseLogDetail cld INNER JOIN
(
SELECT
CaseID
FROM #MyTable
GROUP BY CaseID
HAVING SUM(CASE WHEN CloseDate IS NULL THEN 1 ELSE 0 END) = 0
) t1
ON cld.CaseID = t1.CaseID
Let us know if this helps you.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
December 30, 2009 at 4:41 pm
I'm so sorry, but I'm just not getting it. (due to my limited SQL knowledge entirely) Your queries left me banging my head against a wall trying to understand what they're doing so I went back to basics and wrote out what exactly I needed, and ended up with a couple of temp tables, like so:
select caseid
into #temp
from tblcase
where closedate = ''
and casetypeid in(100, 101, 102, 131)
SELECT CL.CASEID
into #temp2
FROM tblCaseLog CL
WHERE CL.Caseid not in
(select caseid
from tblcaselog
where closedate = '')
AND CL.CASEID IN
(SELECT CASEID
FROM #TEMP)
select distinct caseid
from #temp2
drop table #temp, #temp2
It sure ain't pretty, but it looks like the right results. (famous last words) Now I just need to go back and clean it up and add a few fields from the tblCase table, so that will be the next alteration. At least it appears that this basic code will return the right case numbers, which is a start, right?
Here's hoping I don't break it!
; )
February 17, 2011 at 2:01 pm
This query came back to haunt me. Of course the last variation didn't do exactly what I needed it to so now it's the monkey on my back again...
I tried the last idea but couldn't get it to work:
SELECT
cld.*
FROM view_CaseLogDetail cld INNER JOIN
(--Yet another derived table, and could be eliminated with CTE's
--if you were not using 2000
SELECT
CaseID
FROM
(
SELECT
CaseID,
OpenCases = SUM(CASE WHEN CloseDate IS NULL THEN 1 ELSE 0 END)
FROM #MyTable
GROUP BY CaseID
) t1
WHERE OpenCases = 0
--ORDER BY CaseID
) t2
ON cld.CaseID = t2.CaseID
(what/where does #MyTable come from?)
Anyone else out there have any ideas of how to solve this? To reiterate, I have two tables tblCase and tblCaseLog. I need to find all the CaseIDs in the tblCase table that are not yet closed (closedate = '') AND that have ALL of the CaseLogs (in tblCaseLog) associated with it closed as well. It won't do to have 2 of the 3 logs closed, etc. ALL of the CaseLogs (however many there may be) must be closed before I want to see the actual CaseID. Make sense?
My brain is mush right about now so I'm calling on you SQL gurus out there for your vast knowledge and clever angles....please help!
Thanks!
February 17, 2011 at 2:25 pm
tacy.highland (2/17/2011)
(what/where does #MyTable come from?)
Look back at Greg's post, it's a temp table that's built previous to this process.
Anyone else out there have any ideas of how to solve this? To reiterate, I have two tables tblCase and tblCaseLog. I need to find all the CaseIDs in the tblCase table that are not yet closed (closedate = '') AND that have ALL of the CaseLogs (in tblCaseLog) associated with it closed as well. It won't do to have 2 of the 3 logs closed, etc. ALL of the CaseLogs (however many there may be) must be closed before I want to see the actual CaseID. Make sense?
So, you want something like this (still on a SQL 2k engine, I assume)?
select
c.caseID
FROM
tblCase AS c
LEFT JOIN
(SELECT DISTINCT caseID FROM tblCaseLog WHERE closedate = '') AS drv
ONc.caseID = drv.CaseID
WHERE
c.Closedate = ''
AND drv.CaseID IS NULL
I couldn't find sample DDL and data to test this against in this thread, sorry if I missed it, so I couldn't test it, but I'm pretty sure that gets you where you need to be.
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 17, 2011 at 4:16 pm
Oh Craig, this is so close. I may not necessarily understand it right off the bat, but it looks so close.
I had to add two more specifications to narrow the results, but whatever I did didn't quite work right either. I took your query and, just to see all the necessary closedates of the caseIDs and caselogiIDs so I could see if they fit the criteria, here's what i did:
SELECT c.caseid, c.closedate caseClose, cl.CASELOGID, cl.CLOSEDATE logClose
FROM tblCase c
LEFT JOIN tblCaseLog cl on c.CaseID = cl.CaseID
WHERE c.CaseID IN
(select
c.caseID
FROM
tblCase AS c
LEFT JOIN
(SELECT DISTINCT caseID
FROM tblCaseLog
WHERE closedate = '') AS drv
ON c.caseID = drv.CaseID
WHERE
c.Closedate = ''
AND drv.CaseID IS NULL)
and c.casetypeid in(100, 102, 131)
AND cl.ActivateStatus = 1
Notice the last two lines specifying a casetypeid and activatestatus. I looked through my data and I can find caselogs where the ActivateStatus is = 1, and all of them are closed for a particular CaseID, but that CaseID is still not showing up in my full list of CaseIDs that are ready to close.
I also did this little variation (commenting the last line above):
...FROM
tblCase AS c
LEFT JOIN
(SELECT DISTINCT caseID
FROM tblCaseLog
WHERE (closedate = ''
OR cl.ActivateStatus = 0) ) AS drv
ON c.caseID = drv.CaseID...
Which also didn't quite get the right results either.
Where, oh where did I go wrong?
February 17, 2011 at 4:30 pm
tacy.highland (2/17/2011)
Oh Craig, this is so close. I may not necessarily understand it right off the bat, but it looks so close.
Let's fix that before we continue, or the repairs won't make sense either.
First, look at the drv subquery.
SELECT DISTINCT caseID FROM tblCaseLog WHERE closedate = ''
Simple enough, I hope. The Distinct just makes sure I'm only returning a caseID once if it ever has a caseLog with an opened CloseDate.
Next, our join:
select
c.caseID
FROM
tblCase AS c
LEFT JOIN
(SELECT DISTINCT caseID FROM tblCaseLog WHERE closedate = '') AS drv
ON c.caseID = drv.CaseID
WHERE
c.Closedate = ''
This gives me ALL caseIDs (bear with, the where clause comes into play shortly) that have a blanked closedate, and join in any caseIDs in the drv table that match on caseID.
Add in the last part of the where clause:
AND drv.CaseID IS NULL
This is the part that makes sure that if it has found anything in the subselect, it doesn't get returned.
Hopefully that helps. Now, to the rest:
I had to add two more specifications to narrow the results, but whatever I did didn't quite work right either. I took your query and, just to see all the necessary closedates of the caseIDs and caselogiIDs so I could see if they fit the criteria, here's what i did:
Hmmmm... this will take some further discussion.
Notice the last two lines specifying a casetypeid and activatestatus. I looked through my data and I can find caselogs where the ActivateStatus is = 1, and all of them are closed for a particular CaseID, but that CaseID is still not showing up in my full list of CaseIDs that are ready to close.
What you want to do is adjust the subquery here. The subquery is used to show if all caselogs are closed or not for a particular caseid. If anything appears in the subquery, you don't want that caseID returned. In this case, my guess is those caseids with caselogs with ActivateStatus equals 1 also have caselogs that have closedates = ''?
I'm just not quite sure exactly what you want. Let me state what you've got in SQL here in English:
You want all caseIDs with a blank closedate and a casetypeId of 100, 102, or 131, that don't have any associated caseLogs that have a closedate of blank, and then you want to see all assocaited caselogs, if any, associated with those caseIDs that have an activatestatus of 1.
Where in there is the error?
Also, is it possible for you to provide some DDL and sample data like you'll find in the first link in my signature? This will help us better understand what's happening with your evolving requirements.
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 17, 2011 at 4:57 pm
It did take me a minute to get it, with the NULL for drv table, (looking at it the opposite way that i was, why didn't I think of that??) but I did eventually get it. Thank you....
As for the DDL, I'll put together two tables (I'm afraid the earlier data I posted in this topic was just convoluted and didn't really reflect the actual situation, so that's my bad) and I'll post them in a bit.
You're close on your explanation, here's the slight modification:
"You want all caseIDS with a blank closedate and a casetypeid of 100, 102 or 131, that don't have any associated caselogs that have a closedate of blank [or that have an ActivateStatus of 0]"
Does that clarify things at all?
Thank you for your help on this.
I'll post the data shortly....
February 17, 2011 at 7:56 pm
tacy.highland (2/17/2011)
"You want all caseIDS with a blank closedate and a casetypeid of 100, 102 or 131, that don't have any associated caselogs that have a closedate of blank [or that have an ActivateStatus of 0]"
Helps a lot, try this:
select
c.caseID
FROM
tblCase AS c
LEFT JOIN
(SELECT DISTINCT caseID FROM tblCaseLog WHERE closedate = '' OR 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 4:25 pm
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 caseID
FROM tblCaseLog
WHERE closedate = ''
OR ActivateStatus = 0
OR (CloseDate = '' and ActivateStatus = 0)) AS drv
Or am I just way off base?
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply