System information

i.srvlosspercent < 13 and i.clientlosspercent < 13 and i.totallosspercent < 13 and
(i.srvlost < i.srvsent/6)
)
then 8
when realduration > 65 and i.srvsent > 2000 and i.srvrec > 2000 and
(
(i.srvsent > i.srvrec/4) and
(i.srvrec > i.srvsent/4) and
i.srvlosspercent < 5 and i.clientlosspercent < 5 and i.totallosspercent < 5 and
(i.srvlost < i.srvsent/6)
)
then 10
when realduration > 180 and i.srvlosspercent < 15 and i.clientlosspercent < 15 and i.totallosspercent < 19
then 10
else null end
) as 'QUALITY (0 = worst,10=best)'
FROM tb_cdrs b WITH(NOLOCK)
left join tb_cdrinfo i with(nolock) on (b.id = i.cdrid )
left join tb_users u1 with(nolock) on (b.callerid = u1.id )
left join tb_users u2 with(nolock) on (b.calledid = u2.id )
left join tb_reasoncodes r with(nolock) on (b.discreason = r.code)
WHERE
b.datum >= GETDATE()-1
and b.realduration > 0
GROUP BY b.marker ORDER BY b.marker
CDR List with details geolocation and quality statistics:
SELECT top 100
b.id, b.datum, b.connecttime as 'Connect', b.realduration as 'duration', u1.username as 'caller', i.geoinfo
as 'caller location', u2.username as 'called', b.callednumber as 'callednumber',
case when (select top 1 tb_users.type from tb_users with(nolock) where b.calledid = tb_users.id) = 0 then 'LOCAL'
else 'OUT' end as 'DIR',
(select top 1 tb_directions.name from tb_directions with(nolock) where b.dirid = tb_directions.id) as 'called
location',
r.text as 'discreason' , (i.srvlosspercent + i.clientlosspercent + i.totallosspercent)/3 as 'packetloss %',
case
when realduration > 5 and realduration < 35 and (i.srvsent > 400 or i.srvrec > 400) and