Can anyone please tell me if it is possible to amend my query so it does a count of all the AgentID's that belong to a specific SMID in the table below and display them on a 1 row basis. So, for example SMID 106426 would be displyed on just one row with a new column holding the value '4' as 4 is the no. of AgentID's that are related to this SMID. SMID 106432 would have the No. 12 as a value and so on.
The query I have used to get thus far is: SELECT SMID, AgentID FROM dbo.vw_TimesheetFact where perctarget < 80 AND ([date] BETWEEN CONVERT(varchar, DATEADD(d, - 21, GETDATE()), 112) AND CONVERT(varchar, GETDATE(), 112)) AND perctarget > 0 order by SMID
SELECT SMID, COUNT(DISTINCT AgentID)
FROM dbo.vw_TimesheetFact
where perctarget < 80 AND ([date] BETWEEN CONVERT(varchar, DATEADD(d, - 21, GETDATE()), 112) AND CONVERT(varchar, GETDATE(), 112))
AND perctarget > 0
GROUP BY SMID
order by SMID
Many thanks Mark, it works perfectly. I was nearly there but was putting my DISTINCT in the wrong place. These things are so obvious with the benefit of hindsight!!
Generate new column showing count.
Posted - 02/15/2006 : 10:09:21
