Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 

RequestCenter Select Queries

RequestCenter Select Queries

Anyone in this group have any experience with select queries against the RequestCenter DB? I know that this is not generally supported, but we ocassionally have use cases where we need to retrieve transaction data in a finer grained format in real time.

We have numerous SQL routines in our library (simple selects all -- of course), but there is a specific case that I haven't been able to solve yet. If there are any tech resources out there with good knowledge of the schema I'd be very interested to exchange ideas.

Everyone's tags (1)
7 REPLIES
Community Member

RequestCenter Select Queries

Hi Doug,

We occassionaly do run SQLs againgst RequestCenter DB to generate some very specific reports demanded by end-users. Let me know what data you are looking for to pull up from DB.

-Mihir

RequestCenter Select Queries

I was looking for this, by serviceId.

Submitted Date

Approved Date

Completed Date

Time to Approve

Time to Deliver Once Approved

Total Time from Submit to Completed

For services with due date during a 30 month window that were completed, but not cancelled

Community Member

RequestCenter Select Queries

more info pls

Community Member

RequestCenter Select Queries

The reporting packages should be able to help you out with this

Community Member

RequestCenter Select Queries

This should get you started:

 

 

select r.RequisitionId,
  s.Name            as "Service Name",
  r.createdOn       as "Created Date",
  case when r.Statusid = 0 then 'Preparation'
 when r.Statusid = 1 then 'Ongoing'
 when r.Statusid = 2 then 'Closed'
 when r.Statusid = 3 then 'Cancelled'
 when r.Statusid = 4 then 'Rejected'
 when r.Statusid = 5 then 'Delivery Cancelled'
 else 'Unknown'
  end As Status,
  rb.FirstName || ' ' || rb.Last

RequestCenter Select Queries

Thanks so much for your help on this! This is where I got to, but it looks like your queries will do the trick. Thanks again.

select
TxRequisition.RequisitionID,
DefService.Name,
TxRequisition.StartedDate as SubmittedDate,
TxRequisitionEntry.StartedDate as ApprovedDate,
TxRequisitionEntry.DueDate,
TxRequisition.ClosedDate,
DATEDIFF( day, TxRequisitionEntry.StartedDate, TxRequisition.ClosedDate ) as DeliveryTimeDays,
TxRequisition.ActualDuration/10 as TotalTime, /* b

Community Member

RequestCenter Select Queries

Doug,

txActivity, txProcess and txRquisition tables should help you get all the above information.

 txActivity.PerformerID - Actual Approver

txActivity.StartedOn - Date Started

207
Views
0
Helpful
7
Replies
CreatePlease to create content