0

Please login or register to vote for this query.

(click on this box to dismiss)

View normalisation factors for each month

Q&A about the site for people looking to bypass life's everyday problems with simple tricks

-- 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
    #ViewCurve
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
    #ViewCurve

UPDATE
    #ViewCurve
SET
    ViewWeight = @MinScore / ViewWeight

SELECT
      Month
    , ViewWeight
FROM
    #ViewCurve
ORDER BY
    1

fork query

created may 5 11 martin clayton