drew.allen (5/20/2016)

---

Updating a table is expensive; altering a table is expensive. I updated your query without any updates and in ran in about 1/10 the time.

;

WITH E(n) AS(

SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E(n)

)

, cteTally(n) AS(

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 n

FROM E a, E b, E c, E d

)

, broadcast_cte AS (

SELECT TOP(16000)

n + 1 AS Broadcast_ID,

c.Broadcast_Date,

c.Media_Week,

MAX(c.Broadcast_Date) OVER(PARTITION BY c.Media_Week) AS end_of_week

FROM cteTally

CROSS APPLY (VALUES(DATEADD(DAY, n, '19771226'), n/7 +1)) c(Broadcast_Date, Media_Week)

)

SELECT

b.Broadcast_ID,

b.Broadcast_Date,

b.Media_Week,

DATEPART(MONTH, b.end_of_week) AS Media_Month,

DATENAME(MONTH, b.end_of_week) AS Media_Month_Name,

DATEPART(QUARTER, b.end_of_week) AS Media_Quarter,

YEAR(b.end_of_week) AS Media_Year,

CASE

WHEN DATEADD(YEAR, DATEDIFF(YEAR, b.Broadcast_Date, 0)/4*4, b.Broadcast_Date) = '19000504' THEN 1

WHEN b.Broadcast_Date IN (

'5/25/1977' -- Episode IV

,'5/21/1980' -- Episode V

,'5/25/1983' -- Episode VI

,'5/19/1999' -- Episdoe I

,'5/16/2002' -- Episdoe II

,'5/19/2005' -- Episdoe III

,'12/18/2015' -- Episdoe VII

,'12/16/2016' -- Rogue One

) THEN CAST(1 AS BIT)

ELSE CAST(0 AS BIT)

END AS NCM_Special

, DATEADD(YEAR, DATEDIFF(YEAR, b.Broadcast_Date, 0)/4*4, b.Broadcast_Date)

FROM broadcast_cte b

Other changes I made:

I used a zero-based ROW_NUMBER()

The zero-based ROW_NUMBER allowed me to greatly simplify the media week calculation.

I used the MAX() windowed function to replace the self-join

I used the DATENAME() function to replace the LONG CASE expression for the media month name

I used the DATEPART() function to replace the CASE expression for the media quarter

I used a different method of calculating 5/4 every fourth year

Drew