Date Parsing Update Question

• jarid.lawson

  SSCrazy

  Points: 2953

  More actions

  May 19, 2016 at 3:49 pm

  #310779

  My assignment is to break down a series of dates into week, month, quarter, and year chunks. The trick is the week must always start on Monday. As an example, day 1 of week 1 of 2016 is 12/28/2015. It may be easier to show you what I mean. This will create and populate the table I'm working on.

  Select Top 1832 Identity(Int,1,1) As Broadcast_ID

  Into dbo.Broadcasts

  From master.dbo.syscolumns SC1

  ,master.dbo.syscolumns SC2

  --------------------

  Alter Table dbo.Broadcasts

  Add BroadcastDate DateTime

  ,MediaWeek Int

  ,MediaMonth Int

  ,MediaMonthName Varchar(10)

  ,MediaQuarter Int

  ,MediaYear Int

  Go

  ----------------------------------------------------------------------------------------------------

  -- Add hard date values

  Update dbo.Broadcasts

  Set BroadcastDate = DateAdd(day,Broadcast_ID,'12/27/2015')

  ------------------------------

  -- Use hard date values to update the rest of the table

  Update dbo.Broadcasts

  Set MediaWeek =

  Case

  When Broadcast_ID % 7 = 0

  Then Broadcast_ID / 7

  When Broadcast_ID % 7 != 0

  Then (Broadcast_ID / 7) + 1

  End

  ,MediaMonth = DatePart(month,BroadcastDate)

  ,MediaMonthName = DateName(month,BroadcastDate)

  ,MediaQuarter =

  Case

  When DatePart(month,BroadcastDate) Between 1 And 3

  Then 1

  When DatePart(month,BroadcastDate) Between 4 And 6

  Then 2

  When DatePart(month,BroadcastDate) Between 7 And 9

  Then 3

  Else 4

  End

  ,MediaYear = DatePart(year,BroadcastDate);

  With CTE

  (MediaWeek

  ,MediaYear

  ,RowNumber)

  As

  (Select MediaWeek

  ,MediaYear

  ,Row_Number() Over(Partition By MediaWeek Order By Broadcast_ID) As RowNumber

  From dbo.Broadcasts)

  Update BC

  Set BC.MediaYear = CTE.MediaYear

  From CTE Inner Join dbo.Broadcasts BC

  On CTE.MediaWeek = BC.MediaWeek

  And CTE.RowNumber = 7

  Select *

  From dbo.Broadcasts

  If you note the year before the last update on row 1 would read 2015, but should correctly read 2016.

  What I'm hitting a wall with is finding a simple equation to note the end of a given week is in the next month / quarter / year, and correctly populating the results. Is that goal clear?

  Any ideas? I'll post updates as I keep developing.

  [font="Arial"]“Any fool can know. The point is to understand.”
  - Albert Einstein

  "DOH!"
  - Homer Simpson[/font]

• jarid.lawson

  SSCrazy

  Points: 2953

  More actions

  May 19, 2016 at 5:12 pm

  #1880401

  Ok, I figured it out. It just took me a second to retrace the logic path the way it should be.

  Here is the completed project. This includes the second phase of the project.

  ----------------------------------------------------------------------------------------------------

  -- Jarid Lawson

  -- 5/19/2016

  ----------------------------------------------------------------------------------------------------

  -- Create project database

  Use master;

  Go

  ----------

  Create Database JaridLawson;

  Go

  ----------------------------------------------------------------------------------------------------

  -- Create table

  Use JaridLawson;

  Go

  --------------------

  If Object_ID('JaridLawson.dbo.Broadcasts') Is Not Null

  Begin

  Drop Table dbo.Broadcasts;

  End

  Go

  ----------

  Select Top 16000 Identity(Int,1,1) As Broadcast_ID

  Into dbo.Broadcasts

  From master.dbo.syscolumns SC1

  ,master.dbo.syscolumns SC2;

  --------------------

  Alter Table dbo.Broadcasts

  Add BroadcastDate DateTime

  ,MediaWeek Int

  ,MediaMonth Int

  ,MediaMonthName Varchar(10)

  ,MediaQuarter Int

  ,MediaYear Int

  ,NCM_Special Bit;

  Go

  ----------------------------------------------------------------------------------------------------

  -- T-SQL code to populate this new table with dates for Jan 1 of the current year to the last

  -- broadcast date for 2020.

  ------------------------------

  -- Add hard date values

  Update dbo.Broadcasts

  Set BroadcastDate = DateAdd(day,Broadcast_ID,'12/25/1977');

  ------------------------------

  -- Use hard date values to update the MediaWeek

  Update dbo.Broadcasts

  Set MediaWeek =

  Case

  When Broadcast_ID % 7 = 0

  Then Broadcast_ID / 7

  When Broadcast_ID % 7 != 0

  Then (Broadcast_ID / 7) + 1

  End

  ,MediaMonth = DatePart(month,BroadcastDate)

  ,MediaYear = DatePart(year,BroadcastDate);

  --------------------

  With CTE

  (MediaWeek

  ,MediaMonth

  ,MediaYear

  ,RowNumber)

  As

  (Select MediaWeek

  ,MediaMonth

  ,MediaYear

  ,Row_Number() Over(Partition By MediaWeek Order By Broadcast_ID) As RowNumber

  From dbo.Broadcasts)

  Update BC

  Set BC.MediaMonth = CTE.MediaMonth

  ,BC.MediaYear = CTE.MediaYear

  From CTE Inner Join dbo.Broadcasts BC

  On CTE.MediaWeek = BC.MediaWeek

  And CTE.RowNumber = 7;

  --------------------

  Update dbo.Broadcasts

  Set MediaMonthName =

  Case

  When MediaMonth = 1

  Then 'January'

  When MediaMonth = 2

  Then 'February'

  When MediaMonth = 3

  Then 'March'

  When MediaMonth = 4

  Then 'April'

  When MediaMonth = 5

  Then 'May'

  When MediaMonth = 6

  Then 'June'

  When MediaMonth = 7

  Then 'July'

  When MediaMonth = 8

  Then 'August'

  When MediaMonth = 9

  Then 'September'

  When MediaMonth = 10

  Then 'October'

  When MediaMonth = 11

  Then 'November'

  Else 'Dec'

  End

  ,MediaQuarter =

  Case

  When MediaMonth Between 1 And 3

  Then 1

  When MediaMonth Between 4 And 6

  Then 2

  When MediaMonth Between 7 And 9

  Then 3

  Else 4

  End;

  ----------------------------------------------------------------------------------------------------

  -- Find the special days

  Update dbo.Broadcasts

  Set NCM_Special = 0;

  ----------

  Update dbo.Broadcasts

  Set NCM_Special = 1

  Where

  ((MediaYear % 4) = 0

  And

  (DatePart(month,BroadcastDate) = 5

  And DatePart(day,BroadcastDate) = 4))

  Or BroadcastDate 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

  ----------------------------------------------------------------------------------------------------

  -- Create search tools

  ----------------------------------------------------------------------------------------------------

  -- Define variables

  Declare @StartDate DateTime

  ,@EndDate DateTime;

  ----------

  Select @StartDate = '1/1/1977'

  ,@EndDate = '12/31/2199';

  --------------------

  Select *

  From dbo.Broadcasts

  Where NCM_Special = 1

  And BroadcastDate Between @StartDate And @EndDate;

  ----------------------------------------

  Select Top 1 *

  From dbo.Broadcasts

  Where NCM_Special = 1

  And BroadcastDate > GetDate();

  [font="Arial"]“Any fool can know. The point is to understand.”
  - Albert Einstein

  "DOH!"
  - Homer Simpson[/font]

• drew.allen

  SSC Guru

  Points: 76948

  More actions

  May 20, 2016 at 2:07 pm

  #1880601

  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

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA