July 6, 2011 at 11:26 am
I am trying to determine time frame(days) as form enters and exits each queue. Below is sample data and report query. Numerous events occur at the same date/time so I have it ranked per query below. Seems event status 37 or 21 are start dates in queues and event status 9 or 10 are end dates in queues. New to Macess and SSRS 2005- getting no help or support internally so looking for assistance please.
I set up a SSRS 2005 report but unable to get datediff between entry and exit of queues working. I can determine datediff between event date to now(). Need some indicator to determine start/end date in each queue within each document.
DOC_IDFORM EVENT_DT EVENTS_NM QUEUE_NM Event Status
111FORM 110/28/10 4:00 PM Create Job Queue 1 37
111FORM 110/28/10 6:28 PM Selective Draw Queue 1 3
111FORM 110/28/10 6:32 PM Rerouted Queue 1 10
111FORM 110/28/10 6:32 PM Pre Reroute Queue 2 21
111FORM 111/5/10 12:59 AM Selective Draw Queue 2 3
111FORM 111/5/10 1:43 AM Pended Queue 2 8
111FORM 16/28/11 4:42 PM Completed Queue 2 9
My query is as follows:
(select * from
(SELECT DISTINCT
'3-JobEvents' AS event_type,
s.document_id,
wjd.document_uid,
s.form_name,
wje.event_date AS job_event_date,
wjs.job_event_name AS event_name,
s.creation_date AS doc_create_date,
s.completion_date AS doc_compl_date,
wje.wfsys_job_event_uid AS job_event_id,
NULL AS actiongram_date,
S.created_by AS created_by,
a.full_name AS event_creator,
s.sf_status AS form_status,
q.queue_id AS queue_ID,
q.name AS queue_name,
Rank() over (Partition BY wjd.document_uid, q.queue_id order by s.document_id DESC, wje.wfsys_job_event_uid DESC) as rank
FROM macess.dbo.wf_job_events wje
INNER JOIN macess.dbo.wf_job_documents wjd
ON (wje.wf_job_uid = wjd.wf_job_uid)
INNER JOIN macessrpt.dbo.sf_docs s
ON (wjd.document_uid = s.document_uid)
INNER JOIN macess.dbo.wfsys_job_events wjs
ON (wje.wfsys_job_event_uid = wjs.wfsys_job_event_uid)
INNER JOIN macess.dbo.queues q
ON (wje.queue_uid = q.queue_uid)
INNER JOIN macess.dbo.v_actors a
ON (wje.actor_uid = a.actor_uid)
-- WHERE s.creation_date BETWEEN '05/01/2011' AND '06/30/2011'
)job_event
where rank=1)
Thanks,
July 6, 2011 at 12:36 pm
I will repost my problem after reading Jeff's article on posting data/code.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply