Источник:
http://blogs.msdn.com/b/axsupport/ar...to-action.aspx
==============
Description:
Simple sql script to catch the number of actions generated (A-JOBS)
--RETAILCONNPREACTIONTABLE Tracking with percentage calculation
SELECT
count(*) AS RecordCount
,S1.REFTABLEID
,S2.NAME
,t5.RecordCount1 AS PROCESSED
,t6.RecordCount2 AS UNPROCESSED
,((t5.RecordCount1 * 100) / (t5.RecordCount1 + t6.RecordCount2)) AS RecordPercentage
,t7.recordcount3 AS NumActionsCreated
,(t7.RecordCount3 / t5.RecordCount1) AS PreActionToActionPerc
FROM
RETAILCONNPREACTIONTABLE S1
CROSS
JOIN SQLDICTIONARY
S2
CROSS
APPLY (
SELECT
count(*) AS RecordCount1
FROM
RETAILCONNPREACTIONTABLE T1
CROSS
JOIN SQLDICTIONARY
T2
WHERE
T1.REFTABLEID = t2.TABLEID
AND t2.FIELDID = 0
AND t1.PROCESSED = 1
AND S1.REFTABLEID = t1.REFTABLEID
GROUP
BY t1.REFTABLEID
,t2.NAME
) AS T5
CROSS
APPLY (
SELECT
count(*) AS RecordCount2
FROM
RETAILCONNPREACTIONTABLE T3
CROSS
JOIN SQLDICTIONARY
T4
WHERE
T3.REFTABLEID = t4.TABLEID
AND t4.FIELDID = 0
AND t3.PROCESSED = 0
AND S1.REFTABLEID = t3.REFTABLEID
GROUP
BY t3.REFTABLEID
,t4.NAME
) AS T6
CROSS
APPLY (
SELECT
count(*) AS RecordCount3
FROM
RETAILCONNACTIONTABLE T5
CROSS
JOIN SQLDICTIONARY
T6
WHERE
T5.REFTABLEID = t6.TABLEID
AND t6.FIELDID = 0
AND S1.REFTABLEID = t5.REFTABLEID
GROUP
BY t5.REFTABLEID
,t6.NAME
) AS T7
WHERE
S1.REFTABLEID = S2.TABLEID
AND S2.FIELDID = 0
GROUP
BY s1.REFTABLEID
,s2.NAME
,t5.RecordCount1
,t6.recordcount2
,t7.RecordCount3
ORDER
BY s2.NAME ASC
Author: Kim Truelsen
Date: 12/9-2013
Источник:
http://blogs.msdn.com/b/axsupport/ar...to-action.aspx