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
3 6845
I don't have that database installed in my server, could you post some sample data from the tables?
--- CK
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
Thank you, kunalsmehta! It was the group by and max that I was missing.
Thanks again!
Jennifer
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Russell |
last post by:
I'm using MySQL 4.1.1
I've inherited a database which has some (almost) duplicate rows.
The databse is like this.
userID
userPosition
userDepartment
|
by: OC |
last post by:
I have a datatable for which I have bound a DataView. I have three rows in
the table, with rowid = '12310104', '12314402', and '12315208'.
When I do a dvHdr.RowFilter = '12314402', it returns a...
|
by: g_chime |
last post by:
I'm sorry, I had a couple of typos in my original post.
Here is the corrected version:
How can I make MySQL reject duplicate rows when inserting?
The table is quite large: 100+ columns and...
|
by: Jeff Silverman |
last post by:
I have a PHP program that almost works. I'm running it from the command
line and simulating a form using a GET method. That part is working,
but I get spurious records with all of the fields...
|
by: Bob Stearns |
last post by:
I am getting duplicate rows back from a select distinct statement of the
form:
SELECT DISTINCT 'jhough', '000111', t0.bhid
FROM (SELECT lots of good stuff) t0
LEFT OUTER JOIN another_table ...
|
by: Jon Maz |
last post by:
Hi,
I have a MySql problem I hope someone can help me with. I'm trying to run
an update on a linking table, the update is running into a Primary Key
constraint violation, and in my workaround...
|
by: rdemyan via AccessMonster.com |
last post by:
I have two databases, db1 and db2, with the same table, TableA. I want to
select the records from TableA in db1 that have a LAST_UPDATE SomeDate.
Then I want to get the identical records in TableA...
|
by: Phil Latio |
last post by:
I have a number of spreadsheets, each with between 1000-6000 rows (each row
is a property) and they all need to be combined into a single database. Each
spreadsheet contains slightly different...
|
by: ravir81 |
last post by:
Hi,
I am currently working on excel validation using Perl. I am new to Excel validation but not for Perl. I have a question regarding one of the validation. Could anyone please tell me how to get...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |