11-17-2011 05:49 PM
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.
11-17-2011 05:49 PM
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
11-17-2011 05:49 PM
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
11-17-2011 05:49 PM
more info pls
11-17-2011 05:49 PM
The reporting packages should be able to help you out with this
11-17-2011 05:49 PM
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
11-17-2011 05:49 PM
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
11-17-2011 05:49 PM
Doug,
txActivity, txProcess and txRquisition tables should help you get all the above information.
txActivity.PerformerID - Actual Approver
txActivity.StartedOn - Date Started
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide