Helping Counting

I posted a week or so an issue but I don't think I explained it correct. I'll try to do it better here. I have several queries (two in the example below) that return tables with 2 identical Columns (Advocate and Service) and 1 count Column. In order to group the two sets of data together I've used the coalesce function. I have two reports I'm generating from this same data just with different where clauses and the query below works successfully for the first report that counts the total number of Services administered by the Advocate no matter if the Service was administered multiple times to the same VictimsWitness or not. The query I'm having trouble with is counting only the first time a service was administered to a VictimWitness by an Advocate. Example table below and desired results.





Code Block

VictimWitness Advocate Service

---------------------------------------

Joe Witness Cindy Advocate Email Service

Joe Witness Cindy Advocate Email Service

Joe Witness Sue Advocate Phone Service

Joe Witness Sue Advocate Letter Service

John Witness Sue Advocate Letter Service

The result of the sub-query I currently have returns:





Code Block

Advocate Service NewCount


-------------------------------------

Cindy Advocate Email Service 2

Sue Advocate Phone Service 1

Sue Advocate Letter Service 2

The result I'm looking for with the new sub-query is this:





Code Block

Advocate Service NewCount

-------------------------------------

Cindy Advocate Email Service 1


Sue Advocate Phone Service 1

Sue Advocate Letter Service 2
Here is a shortened sample of my current query. It also shows the coalesce functions which I explained earlier, but for the most part can be ignored.





Code Block

DECLARE @StartDate varchar(50)
DECLARE @EndDAte varchar(50)
SET @StartDate = '11/1/07'
SET @EndDate = '11/30/07'

SELECT DISTINCT
COALESCE(n.Advocate,o.Advocate) AS newAdvocate
,COALESCE(n.Service,o.Service) AS newService
,ISNULL(n.ServiceCount,0) AS NewCount
,ISNULL(o.ServiceCount,0) AS OngoingCount

FROM
(
--Get New Count
SELECT DISTINCT
PA.FirstName + PA.MiddleName + PA.LastName AS Advocate,
SV.Service,
COUNT(SV.Service) AS ServiceCount

FROM VictimWitness.dbo.Service SV
INNER JOIN ServiceDetail SD ON SD.ServiceID = SV.ServiceID
INNER JOIN tblVictimWitness VW ON VW.VictimWitnessID = SD.VictimWitnessID
INNER JOIN tblPerson PA ON PA.PersonID = VW.AdvocateID
INNER JOIN tblCase C ON C.CaseID = VW.CaseID

WHERE SD.Date>=@StartDate
AND SD.Date<=@EndDate
AND C.CaseStatus='O'
AND SD.VictimWitnessCode IS NOT NULL

GROUP BY PA.FirstName,PA.MiddleName,PA.LastName,SV.Service
) AS n
LEFT OUTER JOIN
(
--Get Ongoing Count
SELECT DISTINCT
PA.FirstName + PA.MiddleName + PA.LastName AS Advocate,
SV.Service,
COUNT(SV.Service) AS ServiceCount

FROM VictimWitness.dbo.Service SV
INNER JOIN ServiceDetail SD ON SD.ServiceID = SV.ServiceID
INNER JOIN tblVictimWitness VW ON VW.VictimWitnessID = SD.VictimWitnessID
INNER JOIN tblPerson PA ON PA.PersonID = VW.AdvocateID
INNER JOIN tblCase C ON C.CaseID = VW.CaseID
WHERE
C.CaseStatus='O'
AND SD.VictimWitnessCode IS NOT NULL

GROUP BY PA.FirstName,PA.MiddleName,PA.LastName,SV.Service
) AS o ON
n.Advocate=o.Advocate AND n.Service=o.Service

ORDER BY newAdvocate,newService
I'm really stuck on this and trying to wrap up this project as this is the last report. Thanks for your help!