[Request] PvPer of the week!

[Penchod-Shaiya]

Hey so I was wondering if anyone is able to create a script for example to read the person who got most kills between a time line (from - to) such as:

1) A script to place in [+] tag at the end of the name and removes it from last week's winner.
2) A script to read who got the most kills within 7 days.
3) (If possible) A script to auto announce the winner.

If anyone got any idea how to start / any idea how to create this, please post a reply.

Thanks in advance!

[[DEV]xXDASHXx]

in order to keep track of the weekly pvp kills and make it seperat from total kills you will need to add a new row name it week kills or something to that efect then you must creat a quiry that runs at weekly intervuls then creat a triger to automaticaly run said quiry kindof like the DB backup one just search for the automatic db backup here on epvp then edit it to execute a quiry kind of like this SELECT TOP 1 from ps_gamedata.dbo.chars where week kills < 1 then add in the UPDATE PS_Gamdata.dbo.chars set CharName = @CharName+[+] or something to that efect i dont have an sql server on my phone so actualy creating the exact script is kind of hard off top of my head when i havent messed in shaiya for over 2 years and forget exactly whats in there as my sql db hasnt opened in 2 years and i havent wrote a quiry in just as long lol give me a few weeks assuming real life alows it and ill creat what you need and post here if noone else dose first

[nubness]

DASH, what if I told you that not having messed with Shaiya for a while isn't a good excuse for not knowing SQL Server syntax ? No offense, it's a lame excuse.

If it's done once a week, there's no need to add a new column. You either do it manually, or using an SQL Server job, or any other creative way you could think of, and here's what you gotta do:

UPDATE PS_GameData.dbo.Chars
SET CharName = CharName + '+'
WHERE CharID = (SELECT TOP 1 CharID FROM PS_GameLog.dbo.ActionLog
WHERE ActionType = 103 AND ActionTime BETWEEN DATEADD(DAY, -7, GETDATE()) AND GETDATE()
GROUP BY CharID
ORDER BY COUNT(CharID) DESC)

I did not test this code, but this is most likely what you should be using.

[Shànks♠]

Who is and who is not Dev you can solve it in PM's not by spamming in that thread.

[Autrux]

^

Please just solve it like that.

[Penchod-Shaiya]

Originally Posted by nubness

DASH, what if I told you that not having messed with Shaiya for a while isn't a good excuse for not knowing SQL Server syntax ? No offense, it's a lame excuse.

If it's done once a week, there's no need to add a new column. You either do it manually, or using an SQL Server job, or any other creative way you could think of, and here's what you gotta do:

UPDATE PS_GameData.dbo.Chars
SET CharName = CharName + '+'
WHERE CharID = (SELECT TOP 1 CharID FROM PS_GameLog.dbo.ActionLog
WHERE ActionType = 103 AND ActionTime BETWEEN DATEADD(DAY, -7, GETDATE()) AND GETDATE()
GROUP BY CharID
ORDER BY COUNT(CharID) DESC)

I did not test this code, but this is most likely what you should be using.

Thanks a lot this is exactly what I was looking for and as you were referring auto (once a week) you can decide that through PhP or make a simple trigger that executes this and saves the result somewhere each 7 days, not really hard but thanks a lot!

[Penchod-Shaiya]

[UPDATE]
The code is working perfectly thanks a lot, even tho I would like to ask for a more advanced code right now it selects the top 1 i tried editing so it picks the top 10 pvp'ers and I managed to get top 10. What I want is if you are able to make it so it reads top 10 pvper's and how many kills it has been on each one of them.

This is the current code I am using.

 Spoiler

SELECT CharName
FROM PS_GameData.dbo.Chars
WHERE CharID = (SELECT TOP 10 CharID FROM PS_GameLog.dbo.ActionLog
WHERE ActionType = 103 AND ActionTime BETWEEN DATEADD(DAY, -7, GETDATE()) AND GETDATE()
GROUP BY CharID
ORDER BY COUNT(CharID) DESC)

[nubness]

Originally Posted by Penchod-Shaiya

[UPDATE]
The code is working perfectly thanks a lot, even tho I would like to ask for a more advanced code right now it selects the top 1 i tried editing so it picks the top 10 pvp'ers and I managed to get top 10. What I want is if you are able to make it so it reads top 10 pvper's and how many kills it has been on each one of them.

This is the current code I am using.

 Spoiler

SELECT CharName
FROM PS_GameData.dbo.Chars
WHERE CharID = (SELECT TOP 10 CharID FROM PS_GameLog.dbo.ActionLog
WHERE ActionType = 103 AND ActionTime BETWEEN DATEADD(DAY, -7, GETDATE()) AND GETDATE()
GROUP BY CharID
ORDER BY COUNT(CharID) DESC)

SELECT TOP 10 CharID, COUNT(CharID) Kills
INTO #TempTable
FROM PS_GameLog.dbo.ActionLog
WHERE ActionType = 103 AND ActionTime BETWEEN DATEADD(DAY, -7, GETDATE()) AND GETDATE()
GROUP BY CharID
ORDER BY COUNT(CharID) DESC

SELECT t.CharID, c.CharName, t.Kills
FROM PS_GameData.dbo.Chars c
INNER JOIN #TempTable t
ON c.CharID = t.CharID

DROP TABLE #TempTable