emeadaxsupport: AX Retail: Follow the Progress in converting Preaction to Action

[Blog bot]

Источник: 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