Hello Expert

Have a T-SQL big query that works , copied below.
The big query shows the reports that have not been run for a year.
It is returning  200k plus rows with some of the report names
repeated over and over.

So would like to remove the duplicated names. So I wrote

Select Distinct ctl.Name
(<the big query>)

But SSMS complains about the trailing parenthesis.

How can one do this using distinct or
Group BY or such?


Allen in Dallas

+++++start big query+++++++++++
ctl.Name AS ReportName,ctl.Name,
FROM      Subscription.dbo.SSRSExecutionLog exl
LEFT JOIN ReportServer.dbo.Catalog ctl ON exl.ReportID = ctl.ItemID
LEFT JOIN Subscription.dbo.SSRSRequestTypes srt ON exl.RequestType = srt.RequestType
LEFT JOIN Subscription.dbo.SSRSReportTypes  rty ON ctl.Type = rty.ReportTypeID
LEFT JOIN Subscription.dbo.SSRSSourceTypes sst ON exl.Source = sst.SourceTypeID
WHERE IsNull(srt.RequestDesc, exl.RequestType) != 'System'
AND   exl.Format = 'HTML4.0'
AND   IsNull(rty.ReportType, ctl.Type) = 'Report'
AND   exl.TimeStart > '2014-10-19'

++++++end big query++++++++++++++++++
Allen PittsBusiness analystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
>The big query shows the reports that have not been run for a year.
>WHERE ... AND   exl.TimeStart > '2014-10-19'

Without digging too deeply into your T-SQL, as I can't connect to it so I can't test, maybe something like this?

<air code>
SELECT ctl.Name, Max(exl.TimeStart) as most_recent_run_dt
FROM Subscription.dbo.SSRSExecutionLog exl 
   LEFT JOIN ReportServer.dbo.Catalog ctl ON exl.ReportID = ctl.ItemID
GROUP BY ctl.Name 
HAVING MAX(exl.TimeStart)  <= '2014-10-19'

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.