SQLServerCentral.com / SQL Server 2008 / SQL Server Newbies / Query Trouble / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 20:03:20 GMT20RE: Query Troublehttp://www.sqlservercentral.com/Forums/Topic1516845-1292-1.aspxWell shoot I hate it when I post too soon! I found it was returning double because there where two agent types per publication, so a where clause on the type field fixed it.Now, how can I delete this post.Fri, 22 Nov 2013 09:26:21 GMTlmacdonaldQuery Troublehttp://www.sqlservercentral.com/Forums/Topic1516845-1292-1.aspxHi Everyone,I'm a JR. DBA (MSSQL)As so I'm still getting the hang out TSQL. I am trying to write a query that will sum up the important stats of replication, so I can later put them in a report.select s.Name as Publisher,a.Publisher_DB as Published_DB, COUNT(a.article) as Article_Count,p.Publicationfrom MSarticles a join MSpublications p on a.publication_id = p.publication_id join sys.servers s on p.publisher_id = s.server_idgroup by s.name,a.publisher_db, p.publicationThe query above gets the publisher, published_db,article_count, and publication name. My boss wants me to add somethings that will include the status of the agents. So I tried adding a case expression on the MSreplication_monitordata.status column. However now when I run the query I get twice as many results, repeated rows of information and I don't get why. Can someone please help? Below is the code with added case.SELECT s.Name as Publisher,a.Publisher_DB as Published_DB, COUNT(a.article) as Article_Count,p.Publication, case rm.status when '1' then 'Started' when '2' then 'Succeeded' when '3' then 'In Progress' when '4' then 'Idle' when '5' then 'Retrying' when '6' then 'Failed' Endfrom MSarticles a join MSpublications p on a.publication_id = p.publication_id join sys.servers s on p.publisher_id = s.server_id joinMSreplication_monitordata as rm on p.publication_id = rm.publication_idgroup by s.name,a.publisher_db, p.publication,rm.statusOn a non related note, anyone know a good source for policy management templates and common uses? Happy Friday.Fri, 22 Nov 2013 09:11:51 GMTlmacdonald