TFS版バグ予測ツール

http://qiita.com/items/1596
の投稿で知ったバグ予測ツールの計算式を元にTFS管理しているファイルに対して、計算を行うクエリを作成。TFSを管理するSQLServerのデータベース上で実行する。

BugTfs.sql

DECLARE @T_FILE table (
	ID int identity(1, 1),
	ItemId int,
	[PATH] nvarchar(4000),
	[FILENAME] nvarchar(4000),
	COUNT_CHECKIN int,
	DATE_CREATE] datetime,
	DATE_LAST_UPDATE datetime,
	ALIVE_MINUTES money
)

DECLARE @T_CHECKIN table (
	ID int identity(1, 1),
	FileId int,
	ItemId int,
	DATE_CHECKIN datetime,
	DATE_CREATE_AS_SAMEFILE datetime,
	TIMING float(53)
)

INSERT INTO @T_FILE
SELECT
	tbl_VersionedItem.ItemId,
	REPLACE(REPLACE(tbl_VersionedItem.ParentPath, '>', '_'), '\', '/') AS [PATH],
	REPLACE(REPLACE(tbl_VersionedItem.ChildItem, '>', '_'), '\', '') AS [FILENAME],
	T_FILE.[COUNT] AS COUNT_CHECKIN,
	T_FILE.MIN_DATE_UPDATE,
	T_FILE.MAX_DATE_UPDATE,
	DATEDIFF(D, T_FILE.MIN_DATE_UPDATE, T_FILE.MAX_DATE_UPDATE)
FROM
	tbl_VersionedItem
	INNER JOIN (
		SELECT
			tbl_File.ItemId,
			COUNT(*) AS [COUNT],
			MIN(tbl_File.CreationDate) AS MIN_DATE_UPDATE,
			MAX(tbl_File.CreationDate) AS MAX_DATE_UPDATE
		FROM
			tbl_File
		GROUP BY
			tbl_File.ItemId
	) AS T_FILE ON tbl_VersionedItem.ItemId = T_FILE.ItemId

INSERT INTO @T_CHECKIN
SELECT
	tbl_File.FileId,
	tbl_File.ItemId,
	tbl_File.CreationDate,
	A_FILE.DATE_CREATE] AS DATE_CREATE_AS_SAMEFILE,
	null
FROM
	tbl_File
	LEFT OUTER JOIN @T_FILE AS A_FILE ON tbl_File.ItemId = A_FILE.ItemId

UPDATE @T_CHECKIN
SET DATE_CREATE_AS_SAMEFILE = ISNULL(MIN_CHECKIN.MIN_DATE, NULL)
FROM
	@T_CHECKIN AS A_CHECKIN
	LEFT OUTER JOIN (
		SELECT
			tbl_File.ItemId,
			MIN(tbl_File.CreationDate) AS MIN_DATE
		FROM
			tbl_File
		GROUP BY
			tbl_File.ItemId
	) AS MIN_CHECKIN ON A_CHECKIN.ItemId = MIN_CHECKIN.ItemId

UPDATE @T_CHECKIN
SET TIMING = CONVERT(float(53), DATEDIFF(D, DATE_CREATE_AS_SAMEFILE, DATE_CHECKIN)) / CONVERT(float(53), DATEDIFF(D, DATE_CREATE_AS_SAMEFILE, GETDATE()))
WHERE
	DATEDIFF(D, DATE_CREATE_AS_SAMEFILE, GETDATE()) <> 0



SELECT
	A_FILE.[PATH],
	A_FILE.[FILENAME],
	T_SCORE.SCORE
FROM
	@T_FILE AS A_FILE
	INNER JOIN (
		SELECT
			A_CHECKIN.ItemId AS ItemId,
			SUM(1/(1+EXP(-12*TIMING+12))) AS SCORE
		FROM
			@T_CHECKIN AS A_CHECKIN
		GROUP BY
			A_CHECKIN.ItemId
	) AS T_SCORE ON A_FILE.ItemId = T_SCORE.ItemId
ORDER BY
	T_SCORE.SCORE desc