[SQL] Unique Ranking - includes all Uniques

[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:

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.

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:

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 

e.g.



+ 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

[MentaL]

[asi]

good job

thank you...

[NoNStop]

awesome!

[asi]

committed only once, our database , after could not

[Caipi]

committed only once, our database , after could not

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

[LastThief]

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

[Caipi]

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 :)

[sladlejrhfpq]

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

[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.

[Caipi]

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

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:

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.

[trytokillme]

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

[Caipi]

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

[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'.

[Caipi]

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

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 :)

[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)".

[Caipi]

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

[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).

[Caipi]

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

[trytokillme]

I use SQL 08 R2 english version

[Caipi]

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

[trytokillme]

Send you a private message!

[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

[Caipi]

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

[SupremeSRO]

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

With all Uniques which are listen in the FatalLogs.

Procedure:

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.

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:

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 

e.g.



+ 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 $

[alaabas]

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