Here is the first part and the goal of this part is to improve the yearweek table which then allows us to do a direct join to it rather than using string functions in the join definition.

😎

USE TESTDB;

GO

IF EXISTS (SELECT OBJECT_ID(N'dbo.yearweek'))

BEGIN

DROP TABLE dbo.yearweek;

END

/*

First suggestion, change the structure of the dbo.yearweek table, adding

a clustered index, a computed column and finally an unique covering

index for the query.

*/

CREATE TABLE dbo.yearweek(

yrwk VARCHAR(6) NOT NULL CONSTRAINT PK_DBO_YEARWEEK_YRWK PRIMARY KEY CLUSTERED

,yr INT NOT NULL

,wk INT NOT NULL

,rn INT NOT NULL

,weekx AS (CONVERT( VARCHAR(9),((('W '+CONVERT([char](4),[yr]))+' ')

+stuff('00',(3)-len(CONVERT([char](2),[wk]))

,len(CONVERT([char](2),[wk]))

,CONVERT([char](2),[wk]))),1)) PERSISTED

) ON [PRIMARY];

GO

/* Unique covering index */

CREATE UNIQUE INDEX UNQIDX_DBO_YEARWEEK_WEEKX ON dbo.yearweek (weekx ASC)

INCLUDE (yr,wk,rn);

GO

/* Populate the table */

DECLARE @startyr as INT

DECLARE @no_yrs as INT

SET @startyr = 2010

SET @no_yrs = 20

;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

,YEAR_NUMS(N) AS (SELECT TOP(@no_yrs) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS N FROM T T1,T T2, T T3 ,T T4, T T5, T T6, T T7)

,WEEK_NUMS(N) AS (SELECT TOP(52) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS N FROM T T1,T T2, T T3 ,T T4, T T5, T T6, T T7)

INSERT INTO dbo.yearweek (yrwk,yr,wk,rn)

SELECT

CAST(a.yearno + b.dayno as VARCHAR(6)) AS yrwk

,CAST(a.yearno AS INT) AS yr

,CAST(b.dayno AS INT) AS wk

,CONVERT(INT,ROW_NUMBER() OVER (ORDER BY a.yearno, b.dayno),0) AS rn

FROM

(SELECT CAST(@startyr + N AS VARCHAR) AS yearno FROM YEAR_NUMS)AS a

CROSS JOIN

(SELECT (CASE WHEN N < 10 THEN '0' + CAST(N AS VARCHAR) ELSE CAST(N AS VARCHAR) END ) AS dayno FROM WEEK_NUMS)AS b

/* Check the content */

SELECT

*

FROM dbo.yearweek;

BTW: My apologies for the late and piecemeal like answer:-)