cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
2143
Views
5
Helpful
8
Replies

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

NICNGUYEN
Beginner
Beginner

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

1 Accepted Solution

Accepted Solutions

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.

View solution in original post

8 Replies 8

jpsweeney77
Enthusiast
Enthusiast

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

Termination_Call_Detail.RecoveryKey = Termination_Call_Variable.TCDRecoveryKey

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

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

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

Omar Deen
Spotlight
Spotlight

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

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.

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

Hi Gergely

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

Thank you for your help.

Best Regards

Nick

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.

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: