01-20-2014 08:34 AM - edited 03-14-2019 12:58 PM
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
Solved! Go to Solution.
02-03-2014 04:58 AM
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.
01-31-2014 06:43 AM
You'll want to link up the two tables on the following:
Termination_Call_Detail.RecoveryKey = Termination_Call_Variable.TCDRecoveryKey
01-31-2014 07:45 AM
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
-----------------------------------------------------------------------------------------------------------------------------------------------------------
01-31-2014 08:23 AM
RouterCallKey and RouterCallKeyDay are stored in Termination_Call_Detail which you do not have in your query
01-31-2014 09:41 PM
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
02-03-2014 04:58 AM
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.
02-03-2014 06:47 AM
Perhaps your query will work for Nick, but it did not return any results for me. Regardless, what you said makes sense.
02-05-2014 02:31 PM
Hi Gergely
This query of yours is great ! It's exacly what I was looking for.
Thank you for your help.
Best Regards
Nick
02-05-2014 08:11 PM
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.
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide