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

[Otakanikaru]

Whats this?
- Will output you data from shard logs like this:

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%'

I hardly suggest storing this query as a VIEW so that you can fetch this data & filter however you like

Love

[! Split]

good job mate , keep it up

[nemo08]

welcome back !!!!!!!!!! waiting for something special like VSRO 1.274 serverfiles

[cristianxi]

this can be very useful, thanks!

[elmagico321]

good idea mate