[SQL] Unique Ranking - includes all Uniques

The pure SQL way of http://forum.ragezone.com/f722/relea...app-v1-826324/.

With all Uniques which are listen in the FatalLogs.

Procedure:

PHP Code:

---

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[_UniqueRanking_UPDATE] by Caipi // Script Date: 03/06/2012 12:44:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[_UniqueRanking_UPDATE]

        @Path varchar(max), @Days int

as

SET NOCOUNT ON

Declare 
        @End varchar(max), @Date varchar(max),
        @Start varchar(max), @1 varchar(max), @2 varchar(max), @3 varchar(max), @SEnd varchar(max);

SET        @Start = (SELECT CONVERT(varchar(8), (GETDATE()-(1+(@Days))), 112))
SET        @1 = SUBSTRING(@Start,1,4)
SET        @2 = SUBSTRING(@Start,5,2)
SET        @3 = SUBSTRING(@Start,7,2)
SET        @SEnd = @1 + '-' + @2 + '-' + @3

SET        @Date    =    @SEnd
SET        @End    =    'BULK INSERT dbo.#temp_fatallog FROM'+' '+'"'+@Path+'\'+@Date+'_FatalLog.txt"'

IF exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '#temp_finalkilllog')
BEGIN DROP TABLE #temp_finalkilllog END
IF exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '#temp_fatallog')
BEGIN DROP TABLE #temp_fatallog END
IF exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '#temp_killlog')
BEGIN DROP TABLE #temp_killlog END

IF not exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '_FatalLog_Files')
BEGIN
CREATE TABLE _FatalLog_Files 
(
[Already added] varchar(max) NULL
)
END

/*##*/
IF not exists (SELECT [Already added] FROM _FatalLog_Files WHERE [Already added] = @Date)
BEGIN
/*#*/

CREATE TABLE #temp_fatallog (
[Date1] varchar(max),
[Date2] varchar(max),
[Text1] varchar(max)
)

EXEC (@End) /*BULK INSERT*/

DELETE FROM #temp_fatallog WHERE (Text1 not like '%Unique%' OR Text1 not like '%killed%')

SELECT Date1+' '+Date2 as 'Datum', Text1 as 'Textstring' INTO #temp_killlog FROM #temp_fatallog WITH (NOLOCK) ORDER BY Date2 asc

DROP TABLE #temp_fatallog

IF not exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '_temp_UniqueRanking')
BEGIN

SELECT 
SUBSTRING(Textstring,PATINDEX('%MOB%',Textstring),PATINDEX('%by%',Textstring)-(PATINDEX('%MOB%',Textstring)+2)) as 'Unique',
SUBSTRING(Textstring,PATINDEX('%by%',Textstring)+4,LEN(Textstring)-(PATINDEX('%by%',Textstring)+4)) as Killer
INTO _temp_UniqueRanking
FROM #temp_killlog WITH (NOLOCK)

END
ELSE BEGIN

INSERT INTO _temp_UniqueRanking
SELECT 
SUBSTRING(Textstring,PATINDEX('%MOB%',Textstring),PATINDEX('%by%',Textstring)-(PATINDEX('%MOB%',Textstring)+2)) as 'Unique',
SUBSTRING(Textstring,PATINDEX('%by%',Textstring)+4,LEN(Textstring)-(PATINDEX('%by%',Textstring)+4)) as Killer
FROM #temp_killlog WITH (NOLOCK)

END

DROP TABLE #temp_killlog


IF not exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '_UniqueRanking')
BEGIN

SELECT temp.[Killer],
temp.[Unique], 
COUNT(temp.[Unique]) as Kills
INTO _UniqueRanking
FROM _temp_UniqueRanking as temp
GROUP BY (temp.[Killer]), temp.[Unique]
ORDER BY temp.[Killer] asc

INSERT INTO _FatalLog_Files VALUES(@Date)

END
ELSE BEGIN


TRUNCATE TABLE _UniqueRanking

INSERT INTO _UniqueRanking
SELECT temp.[Killer],
temp.[Unique], 
COUNT(temp.[Unique]) as Kills
FROM _temp_UniqueRanking as temp
GROUP BY (temp.[Killer]), temp.[Unique]
ORDER BY temp.[Killer] asc


INSERT INTO _FatalLog_Files VALUES(@Date)


END

/*#*/
END
/*##*/

SET NOCOUNT OFF 


---

How to execute it?

Use..e.g.

PHP Code:

---

Declare @Days int, @Path varchar(max);

SET @Days = 35
SET @Path = 'C:\Blitzkrieq_Bin_Data\FatalLog'

WHILE (@Days >= 0)
BEGIN
exec _UniqueRanking_UPDATE @Path,@Days
SET @Days = @Days - 1
END 


---

@Days = Will add all FatalLog Records from 35+1 Days ago. Min = 0 = Yesterday.
@Path = the Path where your FatalLog.txt files are located in.


Will create an exception list in the Table _FatalLog_Files with the Dates of the already added files and not existing ones - to prevent double-adding.


Example for a Ranking:

PHP Code:

---

SELECT 
    Killer, 
    CASE [Unique] 
    WHEN 'MOB_KK_ISYUTARU' THEN 'Isyutaru'
    WHEN 'MOB_CH_TIGERWOMAN' THEN 'Tiger Girl'
    ELSE [Unique] END as 'Unique', 
    Kills

FROM     _UniqueRanking
WHERE     [Unique] like 'MOB_KK_ISYUTARU' OR [Unique] like 'MOB_CH_TIGERWOMAN'

ORDER BY Kills desc 


---

http://s7.directupload.net/images/120308/v8t6yisg.jpg

e.g.

http://s1.directupload.net/images/120308/3sztj8ob.jpg

+ it'll add ALL Uniques which uses Notices ingame - so also if you create your own Uniques, the Unique will be listed at the table.


#Edit: Added Pictures

good job

thank you...

awesome!

committed only once, our database , after could not

Quote:

---

Originally Posted by asi

committed only once, our database , after could not

---

Worked properly on a test server - add me at Skype and tell me what you did

Saved for using but anyway to make dat script automated I mean parsing the fatal logs every seconds without duplicating the ranking points ?

I could theoretically add a latest date column and let it check if there's sth new in the logs - but I doubt that it's executable every second :)

lol you are really an SQL pro, good job :)

I would make application for that but I want that function to execute every second and check if something is new.I would release complete ranking system with points ;p.

Quote:

---

Originally Posted by LastThief

I would make application for that but I want that function to execute every second and check if something is new.I would release complete ranking system with points ;p.

---

#Realised the request

PHP Code:

---

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[_UniqueRanking_RealTime] by Caipi // Script Date: 03/06/2012 12:44:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[_UniqueRanking_RealTime]

        @Path varchar(max)

as

SET NOCOUNT ON

Declare 
        @End varchar(max), @Date varchar(max),
        @Start varchar(max), @1 varchar(max), @2 varchar(max), @3 varchar(max), @SEnd varchar(max);

SET        @Start = (SELECT CONVERT(varchar(8), (GETDATE()), 112))
SET        @1 = SUBSTRING(@Start,1,4)
SET        @2 = SUBSTRING(@Start,5,2)
SET        @3 = SUBSTRING(@Start,7,2)
SET        @SEnd = @1 + '-' + @2 + '-' + @3

SET        @Date    =    @SEnd
SET        @End    =    'BULK INSERT dbo.#temp_fatallog FROM'+' '+'"'+@Path+'\'+@Date+'_FatalLog.txt"'

IF exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '#temp_finalkilllog')
BEGIN DROP TABLE #temp_finalkilllog END
IF exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '#temp_fatallog')
BEGIN DROP TABLE #temp_fatallog END
IF exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '#temp_killlog')
BEGIN DROP TABLE #temp_killlog END

IF not exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '_FatalLog_Files')
BEGIN
CREATE TABLE _FatalLog_Files 
(
[Already added] varchar(max) NULL,
[LatestDate] datetime NULL
)
END

/*##*/
--IF not exists (SELECT [Already added] FROM _FatalLog_Files WHERE [Already added] = @Date)
--BEGIN
/*#*/

--## Starting with managing the records at the FatalLog.txt files through temp-Table
CREATE TABLE #temp_fatallog (
[Date1] varchar(max),
[Date2] varchar(max),
[Text1] varchar(max)
)

EXEC (@End) /*BULK INSERT*/


--## 1st Cleaning
DELETE FROM #temp_fatallog WHERE (Text1 not like '%Unique%' OR Text1 not like '%killed%')

SELECT CAST((Date1+' '+Date2) as datetime) as 'DateATM', Text1 as 'Textstring' INTO #temp_killlog FROM #temp_fatallog WITH (NOLOCK) ORDER BY Date2 asc

DROP TABLE #temp_fatallog

--## 2nd Cleaning
DELETE FROM _FatalLog_Files WHERE [Already added] IS NULL AND [LatestDate] IS NULL
DELETE FROM _FatalLog_Files WHERE [Already added] IS NULL AND 
SOUNDEX(CAST(CAST([LatestDate] as datetime) as varchar(12))) = SOUNDEX (cast(CAST(GETDATE()-1 as datetime) as varchar(12)));
DELETE FROM #temp_killlog WHERE DateATM <= (SELECT MAX(LatestDate) FROM _FatalLog_Files)


--## Add Latest Date to the _FatalLog_Files TABLE
IF (((SELECT MAX(LatestDate) FROM _FatalLog_Files) < (SELECT MAX(DateATM) FROM #temp_killlog)) OR (SELECT MAX(LatestDate) FROM _FatalLog_Files) IS NULL)
    BEGIN
        INSERT INTO _FatalLog_Files ([LatestDate]) SELECT MAX(DateATM) FROM #temp_killlog
    END
    
IF ((SELECT TOP 1 len(Textstring) FROM #temp_killlog) > 0)
BEGIN


IF not exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '_temp_UniqueRanking')
BEGIN

SELECT 
SUBSTRING(Textstring,PATINDEX('%MOB%',Textstring),PATINDEX('%by%',Textstring)-(PATINDEX('%MOB%',Textstring)+2)) as 'Unique',
SUBSTRING(Textstring,PATINDEX('%by%',Textstring)+4,LEN(Textstring)-(PATINDEX('%by%',Textstring)+4)) as Killer
INTO _temp_UniqueRanking
FROM #temp_killlog WITH (NOLOCK)

END
ELSE BEGIN

INSERT INTO _temp_UniqueRanking
SELECT 
SUBSTRING(Textstring,PATINDEX('%MOB%',Textstring),PATINDEX('%by%',Textstring)-(PATINDEX('%MOB%',Textstring)+2)) as 'Unique',
SUBSTRING(Textstring,PATINDEX('%by%',Textstring)+4,LEN(Textstring)-(PATINDEX('%by%',Textstring)+4)) as Killer
FROM #temp_killlog WITH (NOLOCK)

END

DROP TABLE #temp_killlog


IF not exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '_UniqueRanking')
BEGIN

SELECT temp.[Killer],
temp.[Unique], 
COUNT(temp.[Unique]) as Kills
INTO _UniqueRanking
FROM _temp_UniqueRanking as temp
GROUP BY (temp.[Killer]), temp.[Unique]
ORDER BY temp.[Killer] asc

IF not exists (SELECT [Already added] FROM _FatalLog_Files WHERE [Already added] = @Date)
    BEGIN
        INSERT INTO _FatalLog_Files ([Already added]) VALUES(@Date)
    END


END
ELSE BEGIN


TRUNCATE TABLE _UniqueRanking

INSERT INTO _UniqueRanking
SELECT temp.[Killer],
temp.[Unique], 
COUNT(temp.[Unique]) as Kills
FROM _temp_UniqueRanking as temp
GROUP BY (temp.[Killer]), temp.[Unique]
ORDER BY temp.[Killer] asc


IF not exists (SELECT [Already added] FROM _FatalLog_Files WHERE [Already added] = @Date)
    BEGIN
        INSERT INTO _FatalLog_Files ([Already added]) VALUES(@Date)
    END

END
/*
/*#*/
END
/*##*/
*/

END
ELSE BEGIN 
DROP TABLE #temp_killlog
END

SET NOCOUNT OFF 


---

Execution:

PHP Code:

---

exec _UniqueRanking_RealTime 'C:\Blitzkrieq_Bin_Data\FatalLog' 


---

-> cares only about the todays FatalLogs, so you shud keep it executing every seconds, will start adding the tomorrows stuff as soon as the next day starts.

Or simply use the app LastThief will release when it's done.

is there any way that the procedure insert the unique name into the _UniqueRanking table not the unique code?

Quote:

---

Originally Posted by trytokillme

is there any way that the procedure insert the unique name into the _UniqueRanking table not the unique code?

---

Everything is possible :) I can bulk insert the textdata_object.txt file from the Media.pk2 and relate them with the Mobcodes - but since it's a select statement it'll prolly take some more time to execute then - gonna try it out later on.

OR you'll do it through cases, means case1 if Mobcode = MOB_CH_TIGERWOMAN then throw "Tiger Girl" out instead of the code.

Altough the new table-solution is a way more efficient - choose what you like and ima do it

Ok thank you, some1 said how i can do it via webpage!
But thank you anyway!

Edit: For real time i get this error!

Msg 207, Level 16, State 1, Procedure _UniqueRanking_RealTime, Line 61
Invalid column name 'LatestDate'.
Msg 207, Level 16, State 1, Procedure _UniqueRanking_RealTime, Line 63
Invalid column name 'LatestDate'.
Msg 207, Level 16, State 1, Procedure _UniqueRanking_RealTime, Line 68
Invalid column name 'LatestDate'.

Quote:

---

Originally Posted by trytokillme

Ok thank you, some1 said how i can do it via webpage!
But thank you anyway!

Edit: For real time i get this error!

Msg 207, Level 16, State 1, Procedure _UniqueRanking_RealTime, Line 61
Invalid column name 'LatestDate'.
Msg 207, Level 16, State 1, Procedure _UniqueRanking_RealTime, Line 63
Invalid column name 'LatestDate'.
Msg 207, Level 16, State 1, Procedure _UniqueRanking_RealTime, Line 68
Invalid column name 'LatestDate'.

---

PHP Code:

---

DROP TABLE _FatalLog_Files 


---

Cause the Fatallog_Files table already exists but w/o the LatestDate column which weren't there at the procedure from the first post :)

What i have to do now? :D

Edit: Renamed the table now it works!

Edit2: If i want execute it now, i was only like DAFUQ?!

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 3 (Text1).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Quote:

---

Originally Posted by trytokillme

What i have to do now? :D

Edit: Renamed the table now it works!

Edit2: If i want execute it now, i was only like DAFUQ?!

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 3 (Text1).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

---

DROP all the Unique Ranking related Tables once and try it again - if it still doesn't work add me at Skype (keep.smilin.caipi).

Truly weird cause there shudn't be invalid letters at those columns

I dont have skype, i dropped the table now but dont work. Still this errors:

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 3 (Text1).

Quote:

---

Originally Posted by trytokillme

I dont have skype, i dropped the table now but dont work. Still this errors:

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 3 (Text1).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 3 (Text1).

---

Which SQL Version do you use? For myself it works pretty well under SQL '08 R2.
I assume instead of giving the warning 'Warning: Null value is eliminated by an aggregate or other SET operation.'
an error occurs on your SQL version.

This error occurs usually at wrongly ordered dates, but well it's just a text format w/o actually any special letter. Well anyway, post your sql version

I use SQL 08 R2 english version

Quote:

---

Originally Posted by trytokillme

I use SQL 08 R2 english version

---

That's quite weird, cause that thang is working pretty well with my sql version ('ve got the same one)
Ready for a teamviewer session? add me at Skype then

Send you a private message!

Why it keeps recording the todays kill?

I killed 5 tigergirl instead of recording 5 for todays log it is adding 5 every update...
my update sequence is per minute and I'm using this one _UniqueRanking_RealTime

Quote:

---

Originally Posted by PX2000

Why it keeps recording the todays kill?

I killed 5 tigergirl instead of recording 5 for todays log it is adding 5 every update...
my update sequence is per minute and I'm using this one _UniqueRanking_RealTime

---

I tried it out and it didn't add the Uniques every time it runs again - did you drop the other tables before? which were responsible for the other Unique stuff at the beginning of the thread

Quote:

---

Originally Posted by Caipi

The pure SQL way of http://forum.ragezone.com/f722/relea...app-v1-826324/.

With all Uniques which are listen in the FatalLogs.

Procedure:

PHP Code:

---

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[_UniqueRanking_UPDATE] by Caipi // Script Date: 03/06/2012 12:44:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[_UniqueRanking_UPDATE]

        @Path varchar(max), @Days int

as

SET NOCOUNT ON

Declare 
        @End varchar(max), @Date varchar(max),
        @Start varchar(max), @1 varchar(max), @2 varchar(max), @3 varchar(max), @SEnd varchar(max);

SET        @Start = (SELECT CONVERT(varchar(8), (GETDATE()-(1+(@Days))), 112))
SET        @1 = SUBSTRING(@Start,1,4)
SET        @2 = SUBSTRING(@Start,5,2)
SET        @3 = SUBSTRING(@Start,7,2)
SET        @SEnd = @1 + '-' + @2 + '-' + @3

SET        @Date    =    @SEnd
SET        @End    =    'BULK INSERT dbo.#temp_fatallog FROM'+' '+'"'+@Path+'\'+@Date+'_FatalLog.txt"'

IF exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '#temp_finalkilllog')
BEGIN DROP TABLE #temp_finalkilllog END
IF exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '#temp_fatallog')
BEGIN DROP TABLE #temp_fatallog END
IF exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '#temp_killlog')
BEGIN DROP TABLE #temp_killlog END

IF not exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '_FatalLog_Files')
BEGIN
CREATE TABLE _FatalLog_Files 
(
[Already added] varchar(max) NULL
)
END

/*##*/
IF not exists (SELECT [Already added] FROM _FatalLog_Files WHERE [Already added] = @Date)
BEGIN
/*#*/

CREATE TABLE #temp_fatallog (
[Date1] varchar(max),
[Date2] varchar(max),
[Text1] varchar(max)
)

EXEC (@End) /*BULK INSERT*/

DELETE FROM #temp_fatallog WHERE (Text1 not like '%Unique%' OR Text1 not like '%killed%')

SELECT Date1+' '+Date2 as 'Datum', Text1 as 'Textstring' INTO #temp_killlog FROM #temp_fatallog WITH (NOLOCK) ORDER BY Date2 asc

DROP TABLE #temp_fatallog

IF not exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '_temp_UniqueRanking')
BEGIN

SELECT 
SUBSTRING(Textstring,PATINDEX('%MOB%',Textstring),PATINDEX('%by%',Textstring)-(PATINDEX('%MOB%',Textstring)+2)) as 'Unique',
SUBSTRING(Textstring,PATINDEX('%by%',Textstring)+4,LEN(Textstring)-(PATINDEX('%by%',Textstring)+4)) as Killer
INTO _temp_UniqueRanking
FROM #temp_killlog WITH (NOLOCK)

END
ELSE BEGIN

INSERT INTO _temp_UniqueRanking
SELECT 
SUBSTRING(Textstring,PATINDEX('%MOB%',Textstring),PATINDEX('%by%',Textstring)-(PATINDEX('%MOB%',Textstring)+2)) as 'Unique',
SUBSTRING(Textstring,PATINDEX('%by%',Textstring)+4,LEN(Textstring)-(PATINDEX('%by%',Textstring)+4)) as Killer
FROM #temp_killlog WITH (NOLOCK)

END

DROP TABLE #temp_killlog


IF not exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '_UniqueRanking')
BEGIN

SELECT temp.[Killer],
temp.[Unique], 
COUNT(temp.[Unique]) as Kills
INTO _UniqueRanking
FROM _temp_UniqueRanking as temp
GROUP BY (temp.[Killer]), temp.[Unique]
ORDER BY temp.[Killer] asc

INSERT INTO _FatalLog_Files VALUES(@Date)

END
ELSE BEGIN


TRUNCATE TABLE _UniqueRanking

INSERT INTO _UniqueRanking
SELECT temp.[Killer],
temp.[Unique], 
COUNT(temp.[Unique]) as Kills
FROM _temp_UniqueRanking as temp
GROUP BY (temp.[Killer]), temp.[Unique]
ORDER BY temp.[Killer] asc


INSERT INTO _FatalLog_Files VALUES(@Date)


END

/*#*/
END
/*##*/

SET NOCOUNT OFF 


---

How to execute it?

Use..e.g.

PHP Code:

---

Declare @Days int, @Path varchar(max);

SET @Days = 35
SET @Path = 'C:\Blitzkrieq_Bin_Data\FatalLog'

WHILE (@Days >= 0)
BEGIN
exec _UniqueRanking_UPDATE @Path,@Days
SET @Days = @Days - 1
END 


---

@Days = Will add all FatalLog Records from 35+1 Days ago. Min = 0 = Yesterday.
@Path = the Path where your FatalLog.txt files are located in.


Will create an exception list in the Table _FatalLog_Files with the Dates of the already added files and not existing ones - to prevent double-adding.


Example for a Ranking:

PHP Code:

---

SELECT 
    Killer, 
    CASE [Unique] 
    WHEN 'MOB_KK_ISYUTARU' THEN 'Isyutaru'
    WHEN 'MOB_CH_TIGERWOMAN' THEN 'Tiger Girl'
    ELSE [Unique] END as 'Unique', 
    Kills

FROM     _UniqueRanking
WHERE     [Unique] like 'MOB_KK_ISYUTARU' OR [Unique] like 'MOB_CH_TIGERWOMAN'

ORDER BY Kills desc 


---

http://s7.directupload.net/images/120308/v8t6yisg.jpg

e.g.

http://s1.directupload.net/images/120308/3sztj8ob.jpg

+ it'll add ALL Uniques which uses Notices ingame - so also if you create your own Uniques, the Unique will be listed at the table.


#Edit: Added Pictures

---

Hello i want add unique ranking to my website , if you can helpme add my skype : SupremeSRO
Payment : paypal $

i want to put it on my site how can any body say no me please ! i will give like

all list? Uniques?

Man autoupdate tables? CodE?

executer.zip

I'm tried put this in a php for 1 day and dont get sucess.
can someone help? Very greateful

'BULK INSERT dbo.#temp_fatallog FROM'+' '+'"'+@Path+'\'+@Date+'_FatalLog.txt"'

Should be :
'BULK INSERT dbo.#temp_fatallog FROM'+' '+'"'+@Path+'\'+@Date+'_EvaLog.txt"'

Nah ? For my part all unique log are in EvaLog files

i need to know that way that SQl will know the char data to way to but it to make site read all users kills