Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
p.shaw3@ukonline.co.uk
Posting Yak Master
103 Posts |
Posted - 2006-02-15 : 10:09:21
|
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, AgentIDFROM dbo.vw_TimesheetFactwhere perctarget < 80 AND ([date] BETWEEN CONVERT(varchar, DATEADD(d, - 21, GETDATE()), 112) AND CONVERT(varchar, GETDATE(), 112))AND perctarget > 0order by SMID |
|
p.shaw3@ukonline.co.uk
Posting Yak Master
103 Posts |
Posted - 2006-02-15 : 10:16:47
|
Don't worry about the duplicate ID's in the AgentID column, I forgot to put the distinct in before I took the screenshot. |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2006-02-15 : 10:17:03
|
[code]SELECT SMID, COUNT(DISTINCT AgentID)FROM dbo.vw_TimesheetFactwhere perctarget < 80 AND ([date] BETWEEN CONVERT(varchar, DATEADD(d, - 21, GETDATE()), 112) AND CONVERT(varchar, GETDATE(), 112))AND perctarget > 0GROUP BY SMIDorder by SMID[/code]Mark |
|
|
p.shaw3@ukonline.co.uk
Posting Yak Master
103 Posts |
Posted - 2006-02-15 : 10:31:43
|
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!!Cheers, Pat. |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2006-02-16 : 07:24:09
|
No problem PatMark |
|
|
|
|
|
|
|