Allen Pitts
asked on
Nested DISTINCT SELECT
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
from
(<the big query>)
But SSMS complains about the trailing parenthesis.
How can one do this using distinct or
Group BY or such?
Thanks.
Allen in Dallas
+++++start big query+++++++++++
Select
ctl.Name AS ReportName,ctl.Name,
exl.TimeStart,
exl.TimeEnd,
exl.Status,
exl.ByteCount,
exl.RowsCount,
ctl.Path
FROM Subscription.dbo.SSRSExecu tionLog exl
LEFT JOIN ReportServer.dbo.Catalog ctl ON exl.ReportID = ctl.ItemID
LEFT JOIN Subscription.dbo.SSRSReque stTypes srt ON exl.RequestType = srt.RequestType
LEFT JOIN Subscription.dbo.SSRSRepor tTypes rty ON ctl.Type = rty.ReportTypeID
LEFT JOIN Subscription.dbo.SSRSSourc eTypes 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++++++++++++++++++
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
from
(<the big query>)
But SSMS complains about the trailing parenthesis.
How can one do this using distinct or
Group BY or such?
Thanks.
Allen in Dallas
+++++start big query+++++++++++
Select
ctl.Name AS ReportName,ctl.Name,
exl.TimeStart,
exl.TimeEnd,
exl.Status,
exl.ByteCount,
exl.RowsCount,
ctl.Path
FROM Subscription.dbo.SSRSExecu
LEFT JOIN ReportServer.dbo.Catalog ctl ON exl.ReportID = ctl.ItemID
LEFT JOIN Subscription.dbo.SSRSReque
LEFT JOIN Subscription.dbo.SSRSRepor
LEFT JOIN Subscription.dbo.SSRSSourc
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++++++++++++++++++
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.