CREATE TABLE Calendar (
[WeekId] INT PRIMARY KEY IDENTITY(1,1),
[Decade] SMALLINT,
[Year] SMALLINT,
[MonthOrdinal] TINYINT,
[WeekOrdinal] TINYINT
)
 
INSERT INTO Calendar ([Decade],[Year],[MonthOrdinal],[WeekOrdinal])
SELECT [Year]-[Year]%10,[Year],[MonthOrdinal],[WeekOrdinal]
FROM [Months]
CROSS JOIN (
    SELECT CAST(1 AS TINYINT) AS [WeekOrdinal]
    UNION SELECT CAST(2 AS TINYINT)
    UNION SELECT CAST(3 AS TINYINT)
    UNION SELECT CAST(4 AS TINYINT)
) wo
ORDER BY [Year],[MonthOrdinal],[WeekOrdinal]

CREATE FUNCTION [dbo].[ParseRanks] (@ranks VARCHAR(1000))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT    [ranks].[Ordinal]-1 AS [Ordinal],
            [ranks].[Item] AS [Rank]
    FROM    [dbo].[Split](REPLACE(REPLACE(@ranks,'[',''),']',''),',') ranks
    CROSS APPLY (SELECT CAST(ranks.Item AS DECIMAL(4,2)) AS [Rank]) rankFn
    WHERE    CAST(ranks.Item AS FLOAT) < 25.00
 
);
GO

CREATE PROCEDURE [dbo].[ProjectRanks]
AS
    INSERT INTO [dbo].[SongRanks] ([SongFullname],[WeekId],[Rank])
    SELECT [SongFullname], [FinalWeekId]+N, r0.[Rank]*POWER(r0.[Rank]/r1.[Rank],N)
    FROM (
        SELECT [SongFullname], MAX([WeekId]) AS [FinalWeekId]
        from [dbo].[SongRanks]
        GROUP BY [SongFullname]
    ) a
    OUTER APPLY (SELECT [Rank] FROM [dbo].[SongRanks] WHERE [SongFullname]=a.[SongFullname] AND [WeekId]=a.[FinalWeekId]) r0
    OUTER APPLY (SELECT [Rank] FROM [dbo].[SongRanks] WHERE [SongFullname]=a.[SongFullname] AND [WeekId]=a.[FinalWeekId]-1) r1
    OUTER APPLY (SELECT [Rank] FROM [dbo].[SongRanks] WHERE [SongFullname]=a.[SongFullname] AND [WeekId]=a.[FinalWeekId]-2) r2
    CROSS JOIN Numbers
    WHERE N < LOG(25/r0.[Rank]) / LOG(r0.[Rank]/r1.[Rank])
RETURN 0;

CREATE FUNCTION RankToScore(@rank DECIMAL(4,2))
RETURNS DECIMAL(3,2)
AS
BEGIN
    DECLARE @score DECIMAL(3,2)
    SET @score = CASE WHEN LOG(@rank) > 3 THEN 0 ELSE 3-LOG(@rank) END
    RETURN @score
END
GO

USE [SongCharts]
GO
 
CREATE VIEW [dbo].[Decades]
AS 
SELECT CAST(1950 AS SMALLINT) AS [Number], '1950s' AS [Slug]
UNION SELECT CAST(1960 AS SMALLINT), '1960s' AS [Slug]
UNION SELECT CAST(1970 AS SMALLINT), '1970s' AS [Slug]
UNION SELECT CAST(1980 AS SMALLINT), '1980s' AS [Slug]
UNION SELECT CAST(1990 AS SMALLINT), '1990s' AS [Slug]
UNION SELECT CAST(2000 AS SMALLINT), '2000s' AS [Slug]
UNION SELECT CAST(2010 AS SMALLINT), '2010s' AS [Slug]
GO
 
CREATE VIEW [dbo].[Years]
AS
SELECT d.[Number]+u.[Number] AS [Number], CAST(d.[Number]+u.[Number] AS VARCHAR) AS [Slug]
FROM [Decades] d
CROSS JOIN (
    SELECT CAST(0 AS TINYINT) AS [Number]
    UNION SELECT CAST(1 AS TINYINT) 
    UNION SELECT CAST(2 AS TINYINT) 
    UNION SELECT CAST(3 AS TINYINT) 
    UNION SELECT CAST(4 AS TINYINT) 
    UNION SELECT CAST(5 AS TINYINT) 
    UNION SELECT CAST(6 AS TINYINT) 
    UNION SELECT CAST(7 AS TINYINT) 
    UNION SELECT CAST(8 AS TINYINT) 
    UNION SELECT CAST(9 AS TINYINT) 
) u
GO
 
CREATE VIEW [dbo].[Months]
AS
SELECT 
    y.[Number] AS [Year], 
    m.[Ordinal] AS [MonthOrdinal], 
    m.[Name]+' '+CAST(y.[Number] AS VARCHAR) AS [Name],
    CAST(y.[Number] AS VARCHAR)
        + '-'
        + CASE WHEN m.[Ordinal]<10 THEN '0' ELSE '' END 
        + CAST(m.[Ordinal] AS VARCHAR) AS [Slug] 
FROM [Years] y CROSS JOIN [MonthsOfYear] m
GO