0

Please login or register to vote for this query.

(click on this box to dismiss)

View normalisation factors for each month

Q&A for professional and enthusiast programmers

-- View Amortization
-- View normalisation factors for each month

SELECT 
      CONVERT( DATETIME, CONVERT( VARCHAR(6), p.CreationDate, 112 ) + '01' ) AS Month
    , CONVERT( FLOAT, AVG( p.ViewCount ) ) AS ViewWeight
    , IDENTITY( INT ) AS Id
INTO #x
FROM
    Posts p
WHERE
    p.PostTypeId = 1
AND p.CreationDate BETWEEN '1 Aug 2008' AND ' 1 Mar 2011'
GROUP BY
    CONVERT( VARCHAR(6), p.CreationDate, 112 ) + '01'
ORDER BY
    1 DESC

DECLARE @MinScore FLOAT

SELECT
    @MinScore = MIN( ViewWeight )
FROM
    #x

UPDATE #x
SET  ViewWeight = @MinScore / ViewWeight

SELECT
      Month
    , ViewWeight
FROM #x
ORDER BY 1

fork query

created may 5 11 martin clayton