Here are the SQL Queries you can copy and paste from the recent NoJitter article posted here:
https://www.nojitter.com/post/240173403/sophisticated-skype-rate-my-call
SELECT
s.ConferenceDateTime
,Caller.URI as Caller
,CallerCqf.FeedbackText
,CallerCqf.Rating
,CallerCqfTokenDef.TokenDescription
,CallerCqfToken.TokenValue
FROM [Session] s WITH (NOLOCK)
INNER JOIN [MediaLine] AS m WITH (NOLOCK) ON
m.ConferenceDateTime = s.ConferenceDateTime
AND m.SessionSeq = s.SessionSeq
INNER JOIN [AudioStream] AS a WITH (NOLOCK) ON -- only look at Audio related feedback
a.MediaLineLabel = m.MediaLineLabel
and a.ConferenceDateTime = m.ConferenceDateTime
and a.SessionSeq = m.SessionSeq
and a.SenderIsCallerPAI = 1
INNER JOIN [CallQualityFeedback] AS CallerCqf WITH (NOLOCK) ON
CallerCqf.ConferenceDateTime = s.ConferenceDateTime
and
CallerCqf.SessionSeq = s.SessionSeq
INNER JOIN [CallQualityFeedbackToken] AS CallerCqfToken WITH (NOLOCK) ON
CallerCqfToken.ConferenceDateTime = s.ConferenceDateTime
and
CallerCqfToken.SessionSeq = s.SessionSeq
and
CallerCqfToken.FromURI = CallerCqf.FromURI
INNER JOIN [CallQualityFeedbackTokenDef] AS CallerCqfTokenDef WITH (NOLOCK) ON
CallerCqfTokenDef.TokenId = CallerCqfToken.TokenId
and
(CallerCqfToken.TokenId < 20 or (CallerCqfToken.TokenId > 100 and CallerCqfToken.TokenId < 200)) -- only look at Audio related feedback
INNER JOIN [User] AS Caller WITH (NOLOCK) ON
Caller.UserKey = CallerCqf.FromURI
And a second, overly-complex, example for retrieving video call feedback:
SELECT
s.ConferenceDateTime
,Caller.URI as Caller
,CallerCqf.FeedbackText
,CallerCqf.Rating
,CallerCqfTokenDef.TokenDescription
,CallerCqfToken.TokenValue
FROM [Session] s WITH (NOLOCK)
INNER JOIN [MediaLine] AS m WITH (NOLOCK) ON
m.ConferenceDateTime = s.ConferenceDateTime
AND m.SessionSeq = s.SessionSeq
INNER JOIN [VideoStream] AS v WITH (NOLOCK) ON -- only look at Video related feedback
v.MediaLineLabel = m.MediaLineLabel
and v.ConferenceDateTime = m.ConferenceDateTime
and v.SessionSeq = m.SessionSeq
and v.SenderIsCallerPAI = 1
INNER JOIN [CallQualityFeedback] AS CallerCqf WITH (NOLOCK) ON
CallerCqf.ConferenceDateTime = s.ConferenceDateTime
and
CallerCqf.SessionSeq = s.SessionSeq
INNER JOIN [CallQualityFeedbackToken] AS CallerCqfToken WITH (NOLOCK) ON
CallerCqfToken.ConferenceDateTime = s.ConferenceDateTime
and
CallerCqfToken.SessionSeq = s.SessionSeq
and
CallerCqfToken.FromURI = CallerCqf.FromURI
INNER JOIN [CallQualityFeedbackTokenDef] AS CallerCqfTokenDef WITH (NOLOCK) ON
CallerCqfTokenDef.TokenId = CallerCqfToken.TokenId
and
((CallerCqfToken.TokenId > 20 and CallerCqfToken.TokenId < 100) or (CallerCqfToken.TokenId > 200 and CallerCqfToken.TokenId < 300)) -- only look at Video related feedback
INNER JOIN [User] AS Caller WITH (NOLOCK) ON
Caller.UserKey = CallerCqf.FromURI
Building on our simple query, we can count the number number of each particular rating over a specified period of time as follows:
SELECT
Rating, count(*)
FROM [CallQualityFeedback] f WITH (NOLOCK)
WHERE f.ConferenceDateTime >= '1-Mar-2018' and f.ConferenceDateTime <= '31-Mar-2018'
group by Rating
order by Rating
The CallQualityFeedbackToken table has one row for each checkbox presented on the Rate My Call popup, with a value of 1 in the TokenValue column if a checkbox was selected. As such, we can tally the reasons for each rating as follows:
SELECT f.Rating, td.TokenDescription,count(*)
FROM [CallQualityFeedback] f WITH (NOLOCK)
JOIN [CallQualityFeedbackToken] AS tf WITH (NOLOCK) ON
tf.ConferenceDateTime = f.ConferenceDateTime
and
tf.SessionSeq = f.SessionSeq
JOIN [CallQualityFeedbackTokenDef] AS td WITH (NOLOCK) ON
td.TokenId = tf.TokenId
WHERE f.ConferenceDateTime >= '1-Feb-2018' and f.ConferenceDateTime <= '28-Feb-2018' and tf.TokenValue=1
group by Rating, td.TokenDescription
order by Rating, td.TokenDescription
Comments