December 11, 2009 at 12:28 pm
I think I'm overthinking this query...if anyone can point me in the right direction it would be most appreciated...
I have a "Case" table with "caseID" for numerous cases. I have another table "CaseDetails" with the detail records for each Case in the Case table. (caseID is the primary key) One case could have one or multiple case detail records. Each Case and each Detail has a close status, so one open case could have mutliple details and some or all may be listed as closed.
I'm trying to find all of the open cases in the Case table that have all of their associated case details in the CaseDetails table which have been closed. I ONLY want to see the CaseID if ALL of its associated casedetails are closed.
In the hopes of avoiding using cursors, I'm using temp tables and trying to compare the total (count)number of details for each case, and if it's equal to the number of details for each case that are closed, then i think that would give me what I'm looking for (....right?). I may be barking up the wrong tree. This is the start of what I put together:
select caseid
into #OpenCases
from dbo.tblCase
where closedate = ' '
and CaseTypeID = 100
--If this total result set below for total casedetails for each caseID is equal to...
select caseid, count(caselogid)as caselognumber
from dbo.view_CaseLogDetail
where caseid in
(select * from #OpenCases)
group by caseid
--...the resultset below in which all the details are closed, then the caseIDs with the
--numbers that match would be cases where all of it's details are closed...(?)
select caseid, count(caselogid)as caselognumber
from dbo.view_CaseLogDetail
where caseid in
(select * from #OpenCases)
and closedate <> ' '
group by caseid
drop table #OpenCases
Am I making any sense here? How do I go about comparing those two result sets to see if they're exactly equal? Would I just do a join on those as tables on caseID??
Help, please!
December 11, 2009 at 12:44 pm
try something like this:
select D.caseid, sum(case when D.closedate = ' ' then 1 else 0 end) as stillOpen
from dbo.tblCase A
JOIN dbo.view_CaseLogDetail D
ON A.caseid = D.caseid
where A.closedate = ' '
group by D.caseid
having sum(case when D.closedate = ' ' then 1 else 0 end) = 0
The probability of survival is inversely proportional to the angle of arrival.
December 11, 2009 at 1:25 pm
Thanks for the effort, but that's not quite right.
Based on the following query, I've posted some sample data to give a better idea:
select caseid
into #OpenCases
from dbo.tblCase
where closedate = ' '
and CaseTypeID = 100
select caseid, caselogid, closedate
into #OpenCasesDetail
from dbo.view_CaseLogDetail
where caseid in
(select * from #OpenCases)
Select *
INTO #TEMP
from #OpenCasesDetail
Here's the sample data from that query:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
DateValue DATETIME,
Value MONEY,
YearValue INT,
Monthvalue INT )
--===== 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',' ' UNION ALL
SELECT '137957','57231',' ' UNION ALL
SELECT '137969','57249','12/10/2009' UNION ALL
SELECT '137986','57269',' ' UNION ALL
SELECT '137986','57271','11/27/2009' UNION ALL
SELECT '138002','57291',' ' UNION ALL
SELECT '138002','57293','11/25/2009' UNION ALL
SELECT '138004','57296',' ' 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',' ' UNION ALL
SELECT '138037','57354','12/09/2009' UNION ALL
SELECT '138037','57355',' ' UNION ALL
SELECT '138037','57356','12/08/2009' UNION ALL
SELECT '138047','57374',' ' UNION ALL
SELECT '138050','57380','12/10/2009' UNION ALL
SELECT '138051','57382',' ' 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',' ' UNION ALL
SELECT '138098','57433','12/10/2009' UNION ALL
SELECT '138098','57434','12/10/2009' UNION ALL
SELECT '138102','57438',' ' 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',' ' UNION ALL
SELECT '138112','57458',' ' UNION ALL
SELECT '138128','57460',' ' UNION ALL
SELECT '138132','57467','12/10/2009' UNION ALL
SELECT '138132','57468','12/10/2009' UNION ALL
SELECT '138144','57485',' ' UNION ALL
SELECT '138160','57509',' ' UNION ALL
SELECT '138168','57535',' ' UNION ALL
SELECT '138168','57536',' ' UNION ALL
SELECT '138196','57571',' ' UNION ALL
SELECT '138196','57574','12/09/2009' UNION ALL
SELECT '138291','57692',' '
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #MYTABLE OFF
I'm going to be turning this query into an SSRS report...eventually.
Does this make the solution any clearer?
December 11, 2009 at 3:31 pm
I was only trying to give you a general idea of how that can be done.
While I don't totally understand how your data is set up (and I have no idea what that detail view is) I think the solution you are attempting is way more complicated than necessary.
The probability of survival is inversely proportional to the angle of arrival.
December 11, 2009 at 3:46 pm
Hehe. Too complicated? Yep, that is entirely possible. Like I said, I'm probably overthinking it. But when I first got the request I thought "Oh! that's easy!" and promptly wrote out the first draft of the query below ...which didn't exactly provide the right data:
select cd.caseid
, cld.closedate AS DetailCloseDate
FROM dbo.view_CaseDetail cd
LEFT JOIN dbo.view_CaseLogDetail cld on cld.caseid = cd.caseid
WHERE cld.closedate <> ' '
AND cd.closedate = ' '
AND cd.CaseTypeID = 100
ORDER BY dayspending desc
As always, there are so many different ways to achieve the same result with SQL. I'm definitely open to other (more simpler?) approaches...
December 11, 2009 at 3:58 pm
You know what? I was looking at the query sturner posted and I think it's actually right after all. I don't know what i did when i tried running it before (I think i got like 32000 records which was waaaay too many). That's my bad.
Looks like this one is going to work!
That's awesome, thanks so much, sturner!!!!
December 13, 2009 at 9:46 pm
SELECT caseid FROM dbo.view_CaseLogDetail
EXCEPT
SELECT caseid FROM dbo.view_CaseLogDetail WHERE closedate <> ' ';
?
December 14, 2009 at 8:24 am
Hmmm. I've never used "except" before. But when I run that query, it says "Incorrect syntax near the keyword 'EXCEPT'." ...?
But, wouldn't this theoretically just pull up all the caseid's that have at least one closed casedetail?
December 14, 2009 at 2:06 pm
tacy.highland (12/14/2009)
Hmmm. I've never used "except" before. But when I run that query, it says "Incorrect syntax near the keyword 'EXCEPT'." ...?But, wouldn't this theoretically just pull up all the caseid's that have at least one closed casedetail?
EXCEPT was new in 2005. The query would list all distinct case ids except where any detail line was not closed - so giving all cases where all detail lines were closed.
December 14, 2009 at 2:26 pm
Well then, that would appear to be a much more elegant and simpler solution! I'm running it in 2008. Why won't it accept the "EXCEPT"? I copied it directly from your post. Did I miss some other syntax for that?
(It'll be great if I get this to work...one more trick in the ol' SQL arsenal)
Thanks!
####Hold on a sec,...does the database itself have to be in 2005? I think the db I'm querying from is 2000, ...even though I'm using Microsoft SQL Server Management Studio 2008. That's the issue, isn't it?
December 14, 2009 at 3:07 pm
tacy.highland (12/14/2009)
####Hold on a sec,...does the database itself have to be in 2005? I think the db I'm querying from is 2000, ...even though I'm using Microsoft SQL Server Management Studio 2008. That's the issue, isn't it?
Yes 🙂
December 14, 2009 at 3:18 pm
:crying:
Well, ...bummer.
I'll hold onto that code for when we migrate the db to 2008, which should be in the near future.
Thanks for your help!
December 28, 2009 at 1:33 pm
I'm bringing this post back up for discussion.
The query I have didn't work out after all when I spent a little time validating it. (bummer) Here's what I got:
SELECT D.CaseID
FROM dbo.tblCase A
INNER JOIN dbo.tblCaseLog D
ON A.CaseID = D.CaseID
WHERE A.CloseDate = ' '
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
Once again, I'm trying to identify all of the Cases in tblCase which have all associated CaseLogs in tblCaseLog that have been closed. (And I'm running this against SQL2000).
I'm going cross-eyed trying to get this right. Thought it was working before, but found some results that didn't fit the criteria. Anyone else out there want to take a stab at this? (sample data in previous posts) Be much appreciated!!
December 29, 2009 at 7:54 am
tacy,
I had tried except in 2008, It is working well for me. Try it again.
---------------------------------------------------------------------------
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 29, 2009 at 8:28 am
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?
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply