tags:

views:

3

answers:

0

SELECT Cases.pKey, Cases.Name, Cases.FileNum, MIN(cases.[Lot/Unit]) as FirstOfLotUnit,

MIN(Cases.AcctNum)AS FirstOfAcctNum,

MIN(Cases.CaseNum) AS FirstOfCaseNum,

MIN(Cases.Address1) AS FirstOfAddress1,

MIN(Cases.Balance) AS FirstOfBalance,

MIN(CaseActList.Date) AS FirstOfDate

FROM Cases LEFT JOIN CaseActList ON Cases.pKey = CaseActList.CaseKey

WHERE Cases.ClientKey=@ClientKey and CaseActList.ClientKey=@ClientKey and Cases.Active=1

And Cases.HoldFlg=0 AND CaseActList.CollHeadKey>0 AND CaseActList.Posted=0

   And (convert(varchar,caseactlist.date,101) <= @tDate2) and CaseActList.pTeamKey = 0 or CaseActList.pTeamKey IN (SELECT pTeamKey from PTeamMembers 

WHERE ClientKey = @ClientKey and UserKey = @UserKey)

GROUP BY Cases.pKey, Cases.Name, Cases.FileNum

ORDER BY MIN(CaseActList.Date)