I have a table CourtCase with following fields:
CourtCaseId (PK int)
CourtLevelId (FK int)
CourtId (FK int)
CaseNumber (varchar)
isDecided (bool)
isDeleted (bool)

I have another table CourtLevel with following fields
CourtlevelId, CourtTitle

I have a stored procedure which takes 2 parameters:
CourtLevelId, CourtId

I have to get the following data based on courtlevelid and courtid
CourtTitle, TotalCase

I need to get following details based on CourtlevelId and CourtId
Courttitle, TotalCases, Total Cases where isdecided=1, total cases where isdecided=1 and isdeleted=0

icanhelp33 (AT) gmail (DOT) com wrote:
select Courttitle
, count(*) as TotalCases
, sum(case isDecided when 1 then 1 else 0 end) as TotalDecided
, sum(case when isDecided =1 and isdeleted=0 then 1 else 0 end) as
TotalDecidedDeleted
from CourtCase as c
join CourtLevel as l on c.CourtLevelId = l.CourtLevelId
where c.CourtLevelId = @CourtLevelId and CourtId = @CourtId
group by Courttitle

This query works just fine. However it returns only one courtleveltitle. More than one courtleveltitle exist for @courtid and @courtlevelid

select 1 as courtlevel,l.courtleveltitle, 'abc' as url, COUNT(*) as total, SUM(case isdecided when 1 then 1 else 0 end)as decided, SUM(case isdecided when 0 then 1 else 0 end)as pending, SUM(case isdecided when 1 then 1 else 0 end)-SUM(case when c.isdecided=1 and right(c.DecisionOrderFile,1)='/'then 1 else 0 end) as uploadfrom CourtCase as c join CourtLevels as l on c..CourtLevelID=l.CourtLevelID where c.CourtLevelID=@CourtLevelid and c.CourtID=@CourtId group by l.courtleveltitle

icanhelp33 (AT) gmail (DOT) com wrote:
Please show sample data in tabular format followed by results desired from
that sample data, also in tabular format.

So what is the primary key of CourtLevels? It's difficult to assist with a
problem like this without table definitions and sample data. But if I should
work from the names, I would assume that CourtLevelID is the PK of
Courtlevels, and in that case, there can only be one court-level title.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx