|
|
October 2008 Blog Posts
Sometimes you face the situation where you have some items ranked, and someone always tries to bump the ranking by voting a "perfect 100" to a particular item.
And when you rank the items with average function or root mean square, that single "perfect 100" vote still bumps the item at top of ranking.
Well, have you looked a Bayesian Estimate?
DECLARE @Sample TABLE
(
userID INT,
vote INT
)
INSERT @Sample
SELECT 3, 40 UNION ALL
SELECT 3, 60 UNION ALL
SELECT 0, 100 UNION ALL
SELECT 1, 100 UNION ALL
SELECT 1, 100 UNION ALL
SELECT 1, 90 UNION ALL
SELECT 1, 100 UNION ALL
SELECT 1, 90 UNION ALL
SELECT 1, 100 UNION ALL
SELECT...
DECLARE @s VARCHAR(100)
SET @s = 'aardddvaaaarrkkkk'
-- Highest frequency of same character
SELECT TOP 1 WITH TIES
[char],
COUNT(*) AS cnt
FROM (
SELECT SUBSTRING(@s, 1 + Number, 1) [char]
FROM master..spt_values
WHERE Number < DATALENGTH(@s)
AND type = 'P'
) AS q
GROUP BY [char]
ORDER BY COUNT(*) DESC
-- Longest sequence of same character
SELECT TOP 1 WITH TIES
[char],
CASE [seq]
WHEN 0 THEN DATALENGTH(@s) - Number
ELSE [seq]
END AS [seq]
FROM (
SELECT SUBSTRING(@s, 1 + Number, 1) [char],
Number,
PATINDEX('%[^' + SUBSTRING(@s, 1 + Number, 1) + ']%', SUBSTRING(@s, 2 + Number, 8000)) AS [seq]
FROM master..spt_values
WHERE Number < DATALENGTH(@s)
AND type = 'P'
)...
SELECT number,
CASE
WHEN number % 100 IN (11, 12, 13) THEN 'th'
WHEN number % 10 = 1 THEN 'st'
WHEN number % 10 = 2 THEN 'nd'
WHEN number % 10 = 3 THEN 'rd'
ELSE 'th'
END AS Ordinal
FROM master..spt_values
WHERE type = 'p'
ORDER BY number
/*******************************************************************************
Initialize communication variables
*******************************************************************************/
SET NOCOUNT ON
DECLARE @pathProc VARCHAR(255),
@pathFunc VARCHAR(255),
@pathTrig VARCHAR(255),
@pathView VARCHAR(255),
@cmd NVARCHAR(4000),
@pathBase VARCHAR(256)
SELECT @pathBase = '\\Archive\Documents\Projects\Peso\Code\',
@pathProc = @pathBase + 'Stored Procedures\',
@pathFunc = @pathBase + 'Functions\',
@pathTrig = @pathBase + 'Triggers\',
@pathView = @pathBase + 'Views\'
SET @cmd = 'md "' + @pathProc + '"'
EXEC master..xp_cmdshell @cmd, no_output
SET @cmd = 'md "' + @pathFunc + '"'
EXEC master..xp_cmdshell @cmd, no_output
SET @cmd = 'md "' + @pathTrig + '"'
EXEC master..xp_cmdshell @cmd, no_output
SET @cmd = 'md "' + @pathView + '"'
EXEC master..xp_cmdshell @cmd, no_output
/*******************************************************************************
Stage all existing relevant code
*******************************************************************************/
CREATE TABLE TempDB..CodeOut
(
spID INT,
[uID] INT,
colID INT,
codeText NTEXT,
isProc TINYINT,
isFunc TINYINT,
isTrig...
Today I helped a guy out with a special request. His goal was to remove all records that are subsets or parts of another record, a superset record.
See this sample data
DECLARE @Sample TABLE
(
recID INT IDENTITY(1, 1),
col1 VARCHAR(1),
col2 VARCHAR(2),
col3 VARCHAR(3),
userID INT
)
INSERT @Sample
SELECT 'A', 'B', 'C', 1 UNION ALL
SELECT 'A', 'B', ' ', 1 UNION ALL
SELECT 'A', ' ', 'C', 1 UNION ALL
SELECT 'F', ' ', 'C', 1 UNION ALL
SELECT ' ', 'M', ' ', 2 UNION ALL
SELECT 'T', 'M', 'O', 2 UNION ALL
SELECT ' ', 'M', 'O', 2 UNION ALL
SELECT 'X', 'M', 'O', 2...
Today I had to write some code to dynamically get the job name currently running.
DECLARE @SQL NVARCHAR(72),
@jobID UNIQUEIDENTIFIER,
@jobName SYSNAME
SET @SQL = 'SET @guid = CAST(' + SUBSTRING(APP_NAME(), 30, 34) + ' AS UNIQUEIDENTIFIER)'
EXEC sp_executesql @SQL, N'@guid UNIQUEIDENTIFIER OUT', @guid = @jobID OUT
SELECT @jobName = name
FROM msdb..sysjobs
WHERE job_id = @jobID
This is a piece of code I use to create a resultset from and display in Outlook calendar.
CREATE PROCEDURE dbo.uspGetScheduleTimes
(
@startDate DATETIME,
@endDate DATETIME
)
AS
/*
This code is blogged here
http://weblogs.sqlteam.com/peterl/archive/2008/10/10/Keep-track-of-all-your-jobs-schedules.aspx
*/
SET NOCOUNT ON
-- Create a tally table. If you already have one of your own please use that instead.
CREATE TABLE #tallyNumbers
(
num SMALLINT PRIMARY KEY CLUSTERED
)
DECLARE @index SMALLINT
SET @index = 1
WHILE @index <= 8640
BEGIN
INSERT #tallyNumbers
(
num
)
VALUES (
@index
)
SET @index = @index + 1
END
-- Create a staging table for jobschedules
CREATE TABLE #jobSchedules
(
rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
serverName SYSNAME NOT NULL,
...
This tuesday I had the opportunity to meet Kalen Delaney and hear her talk about the new compression algorithms in SQL Server 2008.
For those of you that never have met Kalen, I can tell she is a sweet lady, knowledgable and interesting to listen to.
With SQL Server 2005 SP2, the new VARDECIMAL datatype arrived, with a few stored procedures to calculate eventual savings. This turned out to be a subset for the ROW and PAGE compressions available in SQL Server 2008, Enterprise Edition. You can have ROW compression only if you want, but if you choose PAGE compression you...
DECLARE @a TABLE (pk INT)
DECLARE @b TABLE (fk INT, i INT)
DECLARE @c TABLE (fk INT, j INT)
INSERT @a
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
INSERT @b
SELECT 1, 1 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 2, 4 UNION ALL
SELECT 2, 8 UNION ALL
SELECT 2, 10 UNION ALL
SELECT 3, 1
INSERT @c
SELECT 1, 11 UNION ALL
SELECT 1, 13 UNION ALL
SELECT 2, 14 UNION ALL
SELECT 2, 18 UNION ALL
SELECT 2, 60 UNION ALL
SELECT 3, 11
-- Wrong way
SELECT a.pk,
SUM(b.i) AS SumAct,...
|