Need to add another count... 1

[TheBugSlayer]

Hello all.

I have a quite complex multi-part query so to speak. The first query is in a CTE that I join to another query to produce my final result set and looks like this:

WITH CaseSpecCount_CTE(CaseID, SpecCount)
AS
(
            SELECT C.ObjectID, Count(S.ObjectID)
            FROM CaseTable C WITH (NOLOCK)
            JOIN SpecimenTable S WITH (NOLOCK) ON S.[CASE] = C.ObjectID
            WHERE (((C.[Number] LIKE 'ABC%') AND (LOWER(C.[CurrentNeeds]) LIKE 'abcdef%')) OR 
                   (((C.[Number] LIKE 'DEF%') OR (C.[Number] LIKE 'GHI%') OR (C.[Number] LIKE 'KLM%')) AND 
                      ((C.[FINALSIGNOUTDATE] ='None') OR (C.[FINALSIGNOUTDATE] IS NULL)) AND (((
                SELECT [t2].[Gross]
                FROM (
                    SELECT TOP (1) [t1].[Gross]
                    FROM [dbo].[SpecimenTable] AS [t1] WITH (NOLOCK) 
                    WHERE ([t1].[CASE] = (C.[ObjectID]))
                    ) AS [t2]
                )) IS NOT NULL))) AND (C.[Pathologist] = 0123456789)
            GROUP BY C.ObjectID
 )

Case to Specimen relationship is one-to-many. Specimen to SpecimenBlock is also one-to-many. Now I am asked to also add a count of specimen blocks. So for each case, I must list specimen count and specimen block count. I am working on different ways to do this but it is taking me long to come up with a way to retrieve both counts in just ONE statement. Maybe you can see it faster (time is the real issue). Any help is appreciated.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[simian336]

Could you provide a data example and the expected result?

Simi

 

[TheBugSlayer]

Here goes. Given
CASE(CaseID, CaseNumber, ...)
SPECIMEN(SpecID, CaseID, ...)
SPECIMENBLOCK(BlockID, SpecID, ...)


CASE

1, CASE1
2, CASE2
3, CASE3

SPECIMEN

1, 1
2, 1
3, 2
4, 3
5, 3

SPECIMENBLOCK

1,1
2,1
3,1
4,2
5,3
6,3
7,4
8,5
9,5
10,5


Expected Result (CASE ID, SPEC COUNT, BLOCK COUNT)

CASE1, 2, 4
CASE2, 1, 2
CASE3, 2, 4

Does that help?


MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[TheBugSlayer]

For better understanding, a simplified version of my original query looks like this"

SELECT C.CaseID, Count(S.ObjectID)
            FROM Case C 
            JOIN Specimen S WITH (NOLOCK) ON S.[CaseID] = C.CaseID
            WHERE SomeCondition
            GROUP BY C.CaseID

Now I need to stick SpecimenBlock table in there and workout the count of blocks for the case...

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[jbenson001]

I am working on different ways to do this but it is taking me long to come up with a way to retrieve both counts in just ONE statement.

Why is it necessary to do everything you want in one SQL statement? Most likely that approach will cause your code to be inefficient. Not only that, it won't be easily readable and maintainable. You might think so now, but come back to it in a couple of months, or have someone else look at it. Will you/they be able to figure it out, will you remember what you were doing?
I would break the code up into logical steps and then combine any result sets into one at the end of your procedure.

 

[TheBugSlayer]

Benson,

You're right, and that's what I meant. Currently I am working on a solution that looks like this:

            SELECT C.CaseID, Count(S.ObjectID) SpecimenCount, SC.BlockCount
            FROM Case C 
            JOIN Specimen S ON S.[CaseID] = C.CaseID
            LEFT JOIN SpecimenBlock B WITH (NOLOCK) ON B.Specimen = S.ObjectID
            JOIN (SELECT C.CaseID, Count(S.ObjectID) BlockCount
                  FROM Case C 
                  JOIN Specimen S ON S.[CaseID] = C.CaseID
                  WHERE SomeCondition
                  GROUP BY C.CaseID) SC ON SC.CaseID = C.CaseID
            WHERE SomeCondition
            GROUP BY C.CaseID

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

Take a look at this:

SET NOCOUNT ON
Declare @Case Table(CaseId Int, CaseNumber VarChar(20))
Declare @SPECIMEN Table(SpecID int, CaseID Int)
Declare @SPECIMENBLOCK Table(BlockID Int, SpecID int)

Insert Into @Case Values(1, 'CASE1')
Insert Into @Case Values(2, 'CASE2')
Insert Into @Case Values(3, 'CASE3')

Insert Into @SPECIMEN VALUES(1, 1)
Insert Into @SPECIMEN VALUES(2, 1)
Insert Into @SPECIMEN VALUES(3, 2)
Insert Into @SPECIMEN VALUES(4, 3)
Insert Into @SPECIMEN VALUES(5, 3)

Insert Into @SPECIMENBLOCK Values(1,1)
Insert Into @SPECIMENBLOCK Values(2,1)
Insert Into @SPECIMENBLOCK Values(3,1)
Insert Into @SPECIMENBLOCK Values(4,2)
Insert Into @SPECIMENBLOCK Values(5,3)
Insert Into @SPECIMENBLOCK Values(6,3)
Insert Into @SPECIMENBLOCK Values(7,4)
Insert Into @SPECIMENBLOCK Values(8,5)
Insert Into @SPECIMENBLOCK Values(9,5)
Insert Into @SPECIMENBLOCK Values(10,5)

Select  [Case].CaseNumber, 
        COUNT(Distinct Specimen.SpecID) As SpecCount,
        COUNT(*) As BlockCount 
From    @Case As [Case]
        Inner Join @SPECIMEN As SPECIMEN
          On [Case].CaseId = SPECIMEN.CaseID
        Inner Join @SPECIMENBLOCK As SPECIMENBLOCK
          On SPECIMEN.SpecID = SPECIMENBLOCK.SpecID
Group By [Case].CaseNumber

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Perfect George! I conducted plenty of tests. Now my original query becomes:

SELECT C.Number, 
                   Count(DISTINCT S.ObjectID) AS SpecimenCount,
                   COUNT(*) AS BlockCount  
            FROM CaseTable C WITH (NOLOCK)
            JOIN SpecimenTable S WITH (NOLOCK) ON S.[CASE] = C.ObjectID
            JOIN SpecimenBlockTable B WITH (NOLOCK) ON B.Specimen = S.ObjectID
            WHERE (((C.[Number] LIKE 'ABC%') AND (LOWER(C.[CurrentNeeds]) LIKE 'abcdef%')) OR 
                   (((C.[Number] LIKE 'DEF%') OR (C.[Number] LIKE 'GHI%') OR (C.[Number] LIKE 'KLM%')) AND 
                      ((C.[FINALSIGNOUTDATE] ='None') OR (C.[FINALSIGNOUTDATE] IS NULL)) AND (((
                SELECT [t2].[Gross]
                FROM (
                    SELECT TOP (1) [t1].[Gross]
                    FROM [dbo].[SpecimenTable] AS [t1] WITH (NOLOCK) 
                    WHERE ([t1].[CASE] = (C.[ObjectID]))                    ) AS [t2]
                )) IS NOT NULL))) AND (C.[Pathologist] = 0123456789)
            GROUP BY C.Number

I would not have figured out the COUNT DISTINCT part easily. It's the key to the accuracy of the result set.

Thanks so much George! I must star your answer.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[TheBugSlayer]

Just one small change I had to make, the JOIN on the SpecimenBlock table is a LEFT JOIN as at the early stages in the workflow the specimen has not been cut into blocks yet; we still want the specimen to be counted...Luckily COUNT DISTINCT excludes NULL values.

Thanks again to those who took the time out of their busy schedules to answer.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[TheBugSlayer]

COUNT(SPECIMENBLOCK.SpecID) AS BlockCount to show 0 when there is no block. Now it's 100% perfect.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)