Select from multiple tables with multiple where clauses

Question

I am trying to write a stored procedure that will give a count of all the cases in a table that are not deleted, grouped by a CaseStatusID in another table, but only the cases that have a CaseStatusID that also isn't deleted. I also want it to return any CaseStatusID that does not have case related to it (i.e. a count of 0)

So far I have tried

    Create Table #tCaseStatus   (CaseStatusID int,CaseStatusDesc varchar(200) )
declare @NofCases int
declare @CaseStatusID int
declare @CaseStatusDesc varchar(200)

BEGIN
    INSERT #tCaseStatus 
    Select CaseStatusID, CaseStatusDesc From dbo.CaseStatus Where IsDeleted = 0
    Select @NofCases = Count(*) From #tCaseStatus
    While (@NofCases > 0)
    Begin
        Select Top (1) @CaseStatusID = CaseStatusID, @CaseStatusDesc = CaseStatusDesc from #tCaseStatus 
        SELECT    CaseStatusDesc, Count(CaseID) AS CountCases
        FROM      Cases inner join #tCaseStatus on Cases.CaseStatusID = #tCaseStatus.CaseStatusID
        WHERE     (IsDeleted = 0) AND Cases.CaseStatusID = @CaseStatusID
        Group by  #tCaseStatus.CaseStatusDesc
        Set @NofCases = @NofCases - 1
        Delete Top(1) from #tCaseStatus
    End
END

AND

This returns the correct cases but excludes any of the CaseStatusDesc that have a count of 0

SELECT     CaseStatus.CaseStatusDesc, COUNT(Cases.CaseID) AS CaseCount
FROM         Cases FULL OUTER JOIN
                  CaseStatus ON Cases.CaseStatusID = CaseStatus.CaseStatusID
WHERE     (CaseStatus.IsDeleted = 0) AND
                  (Cases.IsDeleted = 0)
GROUP BY CaseStatus.CaseStatusDesc, CaseStatus.CaseStatusID
ORDER BY CaseStatus.CaseStatusID

AND

this returns all the CaseStatusDesc's even the ones that are deleted

SELECT CaseStatus.CaseStatusDesc, COUNT(CASE
                                        WHEN CaseStatus.IsDeleted = 0 THEN 'ok'
                                        WHEN Cases.IsDeleted = 0 THEN 'ok'
                                        Else null                                       
                                    END) AS [Case] 
FROM   Cases  FULL OUTER JOIN CaseStatus ON Cases.CaseStatusID = CaseStatus.CaseStatusID
GROUP BY CaseStatus.CaseStatusDesc, CaseStatus.CaseStatusID
Order By CaseStatus.CaseStatusID asc

But I cant seem to get the desired results

podiluska · Accepted Answer · 2012-08-13 10:26:10Z

1

Is this what you're after?

select 
    casestatus.casestatusid,
    casestatusdesc,
    COUNT(caseid)
from casestatus
    left join cases 
        on casestatus.casestatusid = cases.casestatusid
        and cases.isdeleted=0
where
    casestatus.isdeleted=0
group by    
    casestatus.casestatusid,
    casestatusdesc

answered Aug 13, 2012 at 10:26

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select from multiple tables with multiple where clauses

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related