×

Warning message

  • Cisco Support Forums is in Read Only mode while the site is being migrated.
  • Cisco Support Forums is in Read Only mode while the site is being migrated.

RequestCenter Select Queries

Unanswered Question
Nov 17th, 2011
User Badges:

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.

  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 0 (0 ratings)
Loading.
Mihir Mihir Thu, 11/17/2011 - 17:49
User Badges:

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

Doug Pinkston D... Thu, 11/17/2011 - 17:49
User Badges:

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

Emir E Thu, 11/17/2011 - 17:49
User Badges:

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

Emir E Thu, 11/17/2011 - 17:49
User Badges:

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

Doug Pinkston D... Thu, 11/17/2011 - 17:49
User Badges:

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

Mihir Mihir Thu, 11/17/2011 - 17:49
User Badges:

Doug,

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

 txActivity.PerformerID - Actual Approver

txActivity.StartedOn - Date Started

Actions

This Discussion

Related Content