عايز unique ranking

[Bouken]

الكلام ده منقول و مجربتوش بس يفضل أنك تعمل BACKUP قبل ما تعمل حاجه

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

*********************

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

*********************

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