[RELEASE] PVP statistics view query without any triggers/hooks.

USE SRO_VT_SHARDLOG

SELECT 
EventTime, 
CASE EventID
    WHEN 19 THEN 'pk'
    WHEN 20 THEN 'vs'
    WHEN 101 THEN 'trade'
END AS cateogory,
CASE 
    WHEN strDesc LIKE '%Robber%' THEN 'job'
    WHEN strDesc LIKE '%Trader%' THEN 'job'
    WHEN strDesc LIKE '%Hunter%' THEN 'job'
    ELSE 'pvp'
END as mode,
cnl.CharName16 as Killer,
REPLACE(REPLACE(strDesc, LEFT(strDesc, CHARINDEX('(', strDesc)), ''), RIGHT(REPLACE(strDesc, LEFT(strDesc, CHARINDEX('(', strDesc)), ''), CHARINDEX(')', REVERSE(REPLACE(strDesc, LEFT(strDesc, CHARINDEX('(', strDesc)), '')))), '') as Dead,
lec.CharID as KillerID,
cnl2.CharID as DeadID,
CASE 
    WHEN c.NickName16 = '' THEN '-'
    ELSE c.Nickname16
END as KillerJobName,
CASE 
    WHEN c2.NickName16 = '' THEN '-'
    ELSE c2.Nickname16
END as DeadJobName,
c.MaxLevel as KillerLevel,
c2.MaxLevel as DeadLevel
FROM SRO_VT_SHARDLOG.dbo._LogEventChar lec
LEFT JOIN  SRO_VT_SHARD.dbo._CharNameList cnl on cnl.CharID = lec.CharID
LEFT JOIN SRO_VT_SHARD.dbo._Char c on c.CharID = lec.CharID
LEFT JOIN SRO_VT_SHARD.dbo._Char c2 on CONVERT(varchar(50), c2.CharName16) COLLATE Latin1_General_CI_AS = REPLACE(REPLACE(strDesc, LEFT(strDesc, CHARINDEX('(', strDesc)), ''), RIGHT(REPLACE(strDesc, LEFT(strDesc, CHARINDEX('(', strDesc)), ''), CHARINDEX(')', REVERSE(REPLACE(strDesc, LEFT(strDesc, CHARINDEX('(', strDesc)), '')))), '') 
LEFT JOIN SRO_VT_SHARD.dbo._CharNameList cnl2 on CONVERT(varchar(50), cnl2.CharName16) COLLATE Latin1_General_CI_AS = REPLACE(REPLACE(strDesc, LEFT(strDesc, CHARINDEX('(', strDesc)), ''), RIGHT(REPLACE(strDesc, LEFT(strDesc, CHARINDEX('(', strDesc)), ''), CHARINDEX(')', REVERSE(REPLACE(strDesc, LEFT(strDesc, CHARINDEX('(', strDesc)), '')))), '') 


WHERE strDesc IS NOT NULL AND strDesc LIKE '%Neutral, no freebattle team%' AND strDesc NOT LIKE '%Monster%'