• 0 Vote(s) - 0 Average
  • 5
  • 4
  • 3
  • 2
  • 1
Thread Modes

using Rank within business rule
#1
We're trying to build a process that assigns each tech in our service desk an availability score (based off total tickets in queue, actions completed in last 60 min, and total number of high urgency tickets they have worked on).

A very simplified example of the sql is below, or the full query is attached:

select top 1(e.employee_id), (ISNULL(a1.total_open_rank, 0) + (ISNULL(a2.worked_last_hour, 0)*2) + ISNULL(a3.urgency_week, 0))
        from am_employee e
        join am_emplgroup eg
                on e.employee_id=eg.employee_id
                and eg.group_id = 6
  left join (select a.DONE_BY_ID, rank () over (order by count(a.request_id) asc) as total_open_rank
                from AM_ACTION a
                join SD_REQUEST r
                                on a.REQUEST_ID=r.REQUEST_ID
                                and r.status_id != 5
                join AM_EMPLGROUP eg
                                on a.DONE_BY_ID=eg.EMPLOYEE_ID
                                and eg.group_id = 6
where a.END_DATE_UT is null
group by a.DONE_BY_ID) as a1
        on e.EMPLOYEE_ID=a1.DONE_BY_ID
......

this continues on with left joins for a2 and a3 to get rank scores for the math done in the initial select.

It returns the expected results in both MS SQL Server management Studio, and MC Monitoring. When I add it to an Internal Update step in a Business Rule Related Process Workflow, it gives me an error.
"Invalid SQL Statement.
order(1,28) expected token: ( SELECT
Error Code : 1"

Testing the subqueries, shows that the error is associated with the "rank () over (order by count(a.request_id) asc) as total_open_rank".

I've tried row_number, Dense_Rank, and even ntile , all with the same functioning in mc monitoring, but failing in internal update.

Does anyone have any suggestions about how I can get a rank type result in an internal update step?

brosenber, proud to be a member of EV CONNECT FORUM since Oct 2015.

#2
(07-18-2018, 01:49 PM)brosenber Wrote: We're trying to build a process that assigns each tech in our service desk an availability score (based off total tickets in queue, actions completed in last 60 min, and total number of high urgency tickets they have worked on).

A very simplified example of the sql is below, or the full query is attached:

select top 1(e.employee_id), (ISNULL(a1.total_open_rank, 0) + (ISNULL(a2.worked_last_hour, 0)*2) + ISNULL(a3.urgency_week, 0))
        from am_employee e
        join am_emplgroup eg
                on e.employee_id=eg.employee_id
                and eg.group_id = 6
  left join (select a.DONE_BY_ID, rank () over (order by count(a.request_id) asc) as total_open_rank
                from AM_ACTION a
                join SD_REQUEST r
                                on a.REQUEST_ID=r.REQUEST_ID
                                and r.status_id != 5
                join AM_EMPLGROUP eg
                                on a.DONE_BY_ID=eg.EMPLOYEE_ID
                                and eg.group_id = 6
where a.END_DATE_UT is null
group by a.DONE_BY_ID) as a1
        on e.EMPLOYEE_ID=a1.DONE_BY_ID
......

this continues on with left joins for a2 and a3 to get rank scores for the math done in the initial select.

It returns the expected results in both MS SQL Server management Studio, and MC Monitoring. When I add it to an Internal Update step in a Business Rule Related Process Workflow, it gives me an error.
"Invalid SQL Statement.
order(1,28) expected token: ( SELECT
Error Code : 1"

Testing the subqueries, shows that the error is associated with the "rank () over (order by count(a.request_id) asc) as total_open_rank".

I've tried row_number, Dense_Rank, and even ntile , all with the same functioning in mc monitoring, but failing in internal update.

Does anyone have any suggestions about how I can get a rank type result in an internal update step?

I haven't tried using derived queries in internal update steps. Maybe it would be easier to create views for all your derived queries (a, a2, a3) and use the views in simple joins instead of joins with derived queries.
It may take a restart of services to have the view available for your BR.
Amine.

#3
I like that idea, but I don't have much experience with creating views in EV. I'd appreciate any documentation anyone could point me towards.
brosenber, proud to be a member of EV CONNECT FORUM since Oct 2015.

#4
(07-19-2018, 04:38 PM)brosenber Wrote: I like that idea, but I don't have much experience with creating views in EV. I'd appreciate any documentation anyone could point me towards.

I was referring to views in MSSQL.
Amine.

#5
Ah thanks for the clarification. I will give it a shot.
brosenber, proud to be a member of EV CONNECT FORUM since Oct 2015.

#6
so I did as suggested, and created sql view tables out of the three main subqueries (a1 a2 and a3), my final (working) code is below. There are a few custom columns, but those should be fairly obvious.


select top 1 e.employee_Id


from am_employee e

join AM_EMPLGROUP eg
    on e.EMPLOYEE_ID=eg.EMPLOYEE_ID
    and group_id = 6
    and eg.EMPLOYEE_ID not in (select employee_id from am_emplgroup where group_id in (7,11,75))
    and e.employee_id not in (4906, 21187)

left join e_open_tickets t
 on e.employee_Id=t.employee_Id
left join e_action_count a2
 on e.employee_id=a2.employee_Id
left join e_urgent_count u
on e.employee_Id=u.employee_Id   


where (e.END_OF_CONTRACT >= getdate()
or e.END_OF_CONTRACT is null)
and e.employee_id not in (select a.done_by_id
        from am_action a
        where a.action_type_id = 39   
        and getutcdate() between a.start_date_ut and a.end_date_ut )

and (select datepart(HOUR, getdate()) as time) between left(e.e_start_time, 2) and left(e.e_end_time, 2)
order by (ISNULL(t.total_open_rank, 0) + (ISNULL(a2.worked_last_hour, 0)*2) + ISNULL(u.urgency_week, 0)) asc
brosenber, proud to be a member of EV CONNECT FORUM since Oct 2015.

#7
Thumbs Up 
Glad to see this worked for you
Amine.

#8
https://theclashofclans.net/clash-of-magic/
opbwg, proud to be a member of EV CONNECT FORUM since Jul 2018.






Users browsing this thread: 1 Guest(s)