relatively simple mode, median, mean

,

Here's a sample script to help you get some generic mode, median, and mean statistics for something. In my example, I'm getting the number of days something takes to start and stop some task. The idea is that I'd have a fairly short list of jobs, and record would record the job id, and start/stop dates. You should be able to extrapolate the mechanism to report on other things.

DECLARE @thisjob char(1)
DECLARE @medianTbl TABLE(
	[job] char(1),
	[mode] smallint,
	[median] smallint,
	[mean] smallint,
	[total] int,
	[updated] int DEFAULT 0
)
DECLARE @workTbl TABLE(
	[job] char(1),
	[startDate] smalldatetime,
	[endDate] smalldatetime
)

/*start by loading up a working resultset of all the jobs you want to test*/
INSERT @workTbl
SELECT jID,jStart,jEnd
FROM jobs
--WHERE some criteria

/*create initial set of results*/
INSERT @medianTbl([job],[mean],[total])
SELECT [job],AVG(DATEDIFF(d,[startDate],[endDate])),COUNT(*) FROM @workTbl
GROUP BY [job] WITH ROLLUP

/*loop over each item, and get the statistics for each*/
SELECT TOP 1 @thisjob=[job] FROM @medianTbl WHERE [updated]=0 AND [job] IS NOT NULL
WHILE @@ROWCOUNT>0 /* Run updates for each job */
BEGIN
	UPDATE @medianTbl
	SET [mode]=(
		SELECT TOP 1 DATEDIFF(d,[startDate],[endDate]) AS [timeTaken]
		FROM @workTbl
		WHERE [job]=@thisjob
		GROUP BY DATEDIFF(d,[startDate],[endDate])
		ORDER BY COUNT(DATEDIFF(d,[startDate],[endDate])) DESC),

	[median]=(SELECT TOP 1 timeTaken FROM (
		SELECT TOP 50 PERCENT DATEDIFF(d,[startDate],[endDate]) AS [timeTaken]
		FROM @workTbl
		WHERE [job]=@thisjob
		ORDER BY timeTaken
	) AS presort
	ORDER BY timeTaken DESC),

	[updated]=1
	WHERE [job]=@thisjob

	SELECT TOP 1 @thisjob=[job] FROM @medianTbl WHERE [updated]=0 AND [job] IS NOT NULL
END

/*get over-all statistics for the entire set*/
UPDATE @medianTbl
SET [mode]=(SELECT TOP 1 DATEDIFF(d,[startDate],[endDate]) AS [timeTaken]
	FROM @workTbl
	GROUP BY DATEDIFF(d,[startDate],[endDate])
	ORDER BY COUNT(DATEDIFF(d,[startDate],[endDate])) DESC),

	[median]=(SELECT TOP 1 presort.timeTaken FROM (
		SELECT TOP 50 PERCENT DATEDIFF(d,[startDate],[endDate]) AS [timeTaken]
		FROM @workTbl
		ORDER BY timeTaken
	) AS presort
	ORDER BY timeTaken DESC),

	[updated]=1
WHERE [updated]=0 AND [job] IS NULL

/* Select just what we need */
SELECT [job],[mode],[median],[mean],[total] FROM @medianTbl ORDER BY [job]

Rate

Share

Share

Rate