By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,412 Members | 1,630 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,412 IT Pros & Developers. It's quick & easy.

Need help with join - it's returning duplicate rows

P: 3
I have re-written this several times (changing the joins to sub selects and various other tricks), and I am sure it's something very, very simple, but I am just missing it. Any help is most appreciated.

I am trying to query the ReportServer database that is installed on Reporting Services. I want to grab all reports that have subscriptions, and grab when the subscription last ran as well as it's status (ex: "Mail sent to userA, userB, etc.")

Here is my query:
select distinct
C.Name,
S.LastStatus,
Sched.LastRunTime

from Schedule Sched
join ReportSchedule RS on Sched.ScheduleID = RS.ScheduleID
join Catalog C on RS.ReportID = C.ItemID
join Subscriptions S on C.ItemID = S.Report_OID

Order by C.Name


The result I am getting is as follows (sorry about the wrapping):

Weekly Status Mail sent to smitha 2009-04-13 08:00:07.943
Daily Status Mail sent to Budgets 2009-04-13 08:00:07.520
Daily Status Mail sent to Budgets 2009-04-13 08:00:08.100
Daily Status Mail sent to Budgets 2009-04-13 08:00:08.147
Daily Status Mail sent to Admins 2009-04-13 08:00:07.520
Daily Status Mail sent to Admins 2009-04-13 08:00:07.520
Daily Status Mail sent to Admins 2009-04-13 08:00:07.520
Daily Status The e-mail address of one or more recipients is not valid. 2009-04-13 08:00:07.520
Daily Status The e-mail address of one or more recipients is not valid. 2009-04-13 08:00:08.100
Daily Status The e-mail address of one or more recipients is not valid. 2009-04-13 08:00:08.147

And here's what I would like to get:
Weekly Status Mail sent to smitha 2009-04-13 08:00:07.943
Daily Status Mail sent to Budgets 2009-04-13 08:00:07.520
Daily Status Mail sent to Admins; 2009-04-13 08:00:07.520
Daily Status The e-mail address of one or more recipients is not valid. 2009-04-13 08:00:08.147
Apr 14 '09 #1
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
I don't have that database installed in my server, could you post some sample data from the tables?


--- CK
Apr 15 '09 #2

P: 6
select
C.Name,
S.LastStatus,
max(Sched.LastRunTime)

from Schedule Sched
join ReportSchedule RS on Sched.ScheduleID = RS.ScheduleID
join Catalog C on RS.ReportID = C.ItemID
join Subscriptions S on C.ItemID = S.Report_OID
group by C.Name,
S.LastStatus
Order by C.Name
Apr 16 '09 #3

P: 3
Thank you, kunalsmehta! It was the group by and max that I was missing.

Thanks again!
Jennifer
Apr 17 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.