Looking for an SQL query to retreive callvariables + ECC from a RUN SCRIPT RESULT (Translation to VRU)

[NICNGUYEN]

Hi Team,

I am looking for an SQL query to check the data (ECC + CallVariable) received following a RUN SCRIPT RESULT when requesting an external VRU with a Translation Route to VRU with a "Run External Script".

I believe the data are parsed between the Termination Call Detail + Termination Call Variable .

If you already have such an SQL query I would very much appreciate to have it.

Thank you and Regards

Nick

[jpsweeney77]

You'll want to link up the two tables on the following:

Termination_Call_Detail.RecoveryKey = Termination_Call_Variable.TCDRecoveryKey

[NICNGUYEN]

Hi,

Thank you for your answer.

I made this query bellow. Now the only thing is that this query doesn't show the RouterCallKey/RouterCallKeyDay

Therefore there is probalbly some enhancement to be done on this query

Regards

Nick

-----------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT 

LEFT(RTRIM(Termination_Call_Variable.DateTime),25 )AS DateTime,

LEFT(RTRIM(Termination_Call_Variable.ExpandedCallVariableID),10 )AS ECC_ID,

LEFT(RTRIM(Expanded_Call_Variable.EnterpriseName),25 )AS EnterpriseName,

LEFT(RTRIM(Termination_Call_Variable.ECCValue),25 )AS ECCValue,

Termination_Call_Variable.RecoveryKey,

Termination_Call_Variable.TCDRecoveryKey

FROM  Termination_Call_Variable, Expanded_Call_Variable

WHERE Termination_Call_Variable.ExpandedCallVariableID = Expanded_Call_Variable.ExpandedCallVariableID

AND  DateTime > '2014-01-20 12:00:00'

AND  DateTime < '2014-11-20 16:02:00'

ORDER BY TCDRecoveryKey, RecoveryKey

-----------------------------------------------------------------------------------------------------------------------------------------------------------

[jpsweeney77]

RouterCallKey and RouterCallKeyDay are stored in Termination_Call_Detail which you do not have in your query

[Omar Deen]

Nick,

This might be what you're looking for. If this query works for you, please be conservative with it and shorten the DateTime gaps.

SELECT

LEFT(RTRIM(TCV.DateTime),25) AS DateTime,

LEFT(RTRIM(TCV.ExpandedCallVariableID),10) AS ECC_ID,

LEFT(RTRIM(ECV.EnterpriseName),25) AS EnterpriseName,

LEFT(RTRIM(TCV.ECCValue),25) AS ECCValue,

TCV.TCDRecoveryKey, TCV.RecoveryKey, TCD.RouterCallKey, TCD.RouterCallKeyDay

FROM Termination_Call_Variable TCV

JOIN Termination_Call_Detail TCD ON (TCDRecoveryKey = TCV.TCDRecoveryKey)

JOIN Expanded_Call_Variable ECV ON (ECV.ExpandedCallVariableID = TCV.ExpandedCallVariableID)

WHERE TCV.DateTime BETWEEN '2014-01-24 00:00:00' AND '2014-01-24 23:59:59'

GROUP BY TCV.DateTime, TCV.ExpandedCallVariableID, TCV.TCDRecoveryKey, TCV.RecoveryKey,

TCV.ECCValue, ECV.EnterpriseName, TCD.RouterCallKey, TCD.RouterCallKeyDay

ORDER BY TCDRecoveryKey, TCV.RecoveryKey

[Gergely Szabo · Accepted solution]

Omar,

with all due respect, shortening a one day's interval might not be an option for a historical report ;-)

I would recommend to take a look the following SQL query:

DECLARE @dateFrom DATETIME, @dateTo DATETIME

SET @dateFrom = '2014-01-24 00:00:00'

SET @dateTo   = '2014-01-25 00:00:00'

SELECT

tcv.DateTime,

tcd.RecoveryKey,

tcd.RouterCallKeyDay,

tcd.RouterCallKey,

ecv.EnterpriseName AS [ECVEnterpriseName],

tcv.ArrayIndex,

tcv.ECCValue

FROM Termination_Call_Variable tcv

JOIN

(SELECT RouterCallKeyDay,RouterCallKey,RecoveryKey FROM Termination_Call_Detail WHERE DateTime > @dateFrom AND DateTime < @dateTo) tcd

ON tcv.TCDRecoveryKey = tcd.RecoveryKey

LEFT OUTER JOIN Expanded_Call_Variable ecv ON tcv.ExpandedCallVariableID = ecv.ExpandedCallVariableID

WHERE tcv.DateTime > @dateFrom AND tcv.DateTime < @dateTo

With variables, you can parametrize your code (for instance, you could write SET @dateFrom = ? and let the calling application fill in the datetime value in for you).

Plus joining two large tables with all rows like you did (TCD-TCV) is never a good option.

Another aspect to consider: all ECC's are actually arrays (always), so it's not good to leave out the index value (tcv.ArrayIndex).

G.

[Omar Deen]

Perhaps your query will work for Nick, but it did not return any results for me. Regardless, what you said makes sense.

[NICNGUYEN]

Hi Gergely

This query of yours is great ! It's exacly what I was looking for.

Thank you for your help.

Best Regards

Nick

[Omar Deen]

Gergely,

Is there a way to include non-persistent variables?

EDIT: Kindly disregard. Just realized that non-persistent variables are not written to the database.