Broadcast calender generator

  • Hi guys,

    Did anyone ever have to create a function/stored procedure that would take a date as an input and generate the values for the broadcast calender like:

    - Media_Week_Start_Date

    - Media_Week

    - Media_Month

    - Media_MonthName

    - Media_Quarter

    - Media_Year

    Thanks in advance!

  • I've played with lots of calendar generations, and googled a bit to see what a "Broadcast Calendar" is; all i found was a bunch of (what i think) are fake/.virus sites that want you to download probable malicious code based on instant fake/web page generation based on a keyword search.

    If you can provide a link to whatever the rules are for a "Broadcast Calendar", it would be easy for us to help built it up.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • The query below should do the trick. I don't know what day counts as "Media Start of Week" but I went with monday (isowk). Also, I don't know what you want if someone passed 1/10/2010 as the start date... Would the first record (assuming order by date) to be 1/4/2010 or 1/11/2010... my logic will return 1/4/2010.

    DECLARE @startDate AS date='1/10/2010',

    @endDate AS date='6/1/2011'

    SELECT @startDate=DATEADD(WEEK,-1,@startDate) -- lazy logic to handle the first week_start...

    ;WITH

    tally(n) AS

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

    FROM master.dbo.spt_values),

    allDates AS

    ( SELECT n, DATEADD(DAY,n,@startDate) as [date]

    FROM tally

    WHERE n<=DATEDIFF(DAY,@startdate,@endDate)),

    FilteredCalendar AS

    ( SELECT n as spoon,

    [date] AS Media_Week_Start_Date,

    DATEPART(MONTH,([date])) AS Media_Month,

    DATEPART(isowk,([date])) AS Media_Week,

    DATENAME(M,([date])) AS Media_Month_Name,

    DATEPART(QUARTER,[date]) AS Media_Quarter,

    DATEPART(YEAR,[date]) AS Media_Year

    FROM allDates),

    top1 AS

    ( SELECT TOP 2000000 RANK() OVER (PARTITION BY Media_Week ORDER BY Media_Week_Start_Date) AS top1,*

    FROM FilteredCalendar

    ORDER BY Media_Week_Start_Date)

    SELECT --top1, n, --uncomment to tinker with the logic

    Media_Week_Start_Date, Media_Month, Media_Week, Media_Month_Name, Media_Quarter, Media_Year

    FROM top1

    WHERE top1=1 AND spoon>0

    ORDER BY spoon

    PS See Lowell's signature if you are wondering why I named the sort key "spoon".

    PS.PS... Take a look at that query plan... nice and linear :cool::cool::cool::cool::cool:

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Ok Alan, help me out here: what makes a Broadcast Calendar? are we talking about TV programming, and just a regular calendar, but the first day the first Monday of the year?

    I couldn't google up a definition.

    By the way, thanks for the spoon recognition and tribute!

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Alan.B (3/21/2013)


    The query below should do the trick. I don't know what day counts as "Media Start of Week" but I went with monday (isowk). Also, I don't know what you want if someone passed 1/10/2010 as the start date... Would the first record (assuming order by date) to be 1/4/2010 or 1/11/2010... my logic will return 1/4/2010.

    DECLARE @startDate AS date='1/10/2010',

    @endDate AS date='6/1/2011'

    SELECT @startDate=DATEADD(WEEK,-1,@startDate) -- lazy logic to handle the first week_start...

    ;WITH

    tally(n) AS

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

    FROM master.dbo.spt_values),

    allDates AS

    ( SELECT n, DATEADD(DAY,n,@startDate) as [date]

    FROM tally

    WHERE n<=DATEDIFF(DAY,@startdate,@endDate)),

    FilteredCalendar AS

    ( SELECT n as spoon,

    [date] AS Media_Week_Start_Date,

    DATEPART(MONTH,([date])) AS Media_Month,

    DATEPART(isowk,([date])) AS Media_Week,

    DATENAME(M,([date])) AS Media_Month_Name,

    DATEPART(QUARTER,[date]) AS Media_Quarter,

    DATEPART(YEAR,[date]) AS Media_Year

    FROM allDates),

    top1 AS

    ( SELECT TOP 2000000 RANK() OVER (PARTITION BY Media_Week ORDER BY Media_Week_Start_Date) AS top1,*

    FROM FilteredCalendar

    ORDER BY Media_Week_Start_Date)

    SELECT --top1, n, --uncomment to tinker with the logic

    Media_Week_Start_Date, Media_Month, Media_Week, Media_Month_Name, Media_Quarter, Media_Year

    FROM top1

    WHERE top1=1 AND spoon>0

    ORDER BY spoon

    PS See Lowell's signature if you are wondering why I named the sort key "spoon".

    PS.PS... Take a look at that query plan... nice and linear :cool::cool::cool::cool::cool:

    Now, if we could just get you to use the ; as a statement terminator instead of a statement begininator all would be good. 😉

  • Lowell - here is the wiki link to the description of the broadcast calender:

    http://en.wikipedia.org/wiki/Broadcast_calendar

    Alan - thank you very much for the provided code. I have to admit that it will take me some time to digest/understand it.

    I just ran it and see that only 52 records are returned even if the time range spans over a year. Could you please clarify why?

    Thank you!

  • Lynn,

    Good eye. Thanks. 😉

    Lowell,

    Both your quotes are great. I actually wonder how many people who come to SSC google "There is no spoon". I know I did. It took me awhile to understand why Ben-Gan and others make a big deal about the fact that we are dealing with un-ordered sets of data.

    I don't have a clue what a broadcast calendar is - I was just taking a wild stab at what I thoght sql_er was looking for and this was a good chance to show off the power of the tally table.

    sql_er,

    This is why you have to post some DLL (note the first link in Lynn's signature); all we can do is guess otherwise.

    I saw Media_Week_Start_Date and guessed that you were looking only for the first day of the week for each week of the year (and that is why you are getting 52 records). You will get all the days if you remove the "top1=1" from my code. Note the truncated example result set below - this is basically what I thought you were looking for:

    Media_Week_Start_Date Media_Week

    2010-01-04 1

    2010-01-11 2

    2010-01-18 3

    2010-01-25 4

    2010-02-01 5

    2010-02-08 6

    2010-02-15 7

    ......

    2010-12-06 49

    2010-12-13 50

    2010-12-20 51

    2010-12-27 52

    Re: " I have to admit that it will take me some time to digest/understand it."

    The technique I used involves a tally table. Some people call it a "numbers table" or "auxilary table of numbers"; I call it a tally table because I learned about it here: The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url].. Stuff like "ROW_NUMBER() OVER (ORDER BY (SELECT NULL))" used to baffle the heck out of me. Below is some code that will help you better understand what is going on in my this example code below. Note my comments.

    DECLARE @TodayIs date=GETDATE(), --a date

    @n int=3; --a number

    -- A quick DATEADD demo

    SELECT @todayIS AS '@today',

    @n AS '@n',

    DATEADD(DAY,@n,@TodayIs) AS '@today + @n';

    --What set based programming looks like

    WITH

    UnorderedSet(n) AS

    ( SELECT 1 UNION ALL

    SELECT 1 UNION ALL

    SELECT 1),

    tallyTable(n) AS

    ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) --The fastest way to count to {whatever} in SQL

    FROM UnorderedSet)

    SELECT n AS [n wearing nothing],

    DATEADD(DAY,n,GETDATE()) AS [n dressed in (today's date) + (n)days]

    FROM tallyTable;

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Alan.B (3/21/2013)


    The query below should do the trick. I don't know what day counts as "Media Start of Week" but I went with monday (isowk).

    Oh, be careful now. I'm not on my 2008 machine so I can't check just now but...

    1. According to the WikiPedia article, the first week of the month is the week that contains the 1st of the month.

    2. According to the ISO rules, the first week of the month is the week that contains the 1st Thursday of the month (or the 4th of the month depending on which way you figure it).

    That means that using the ISOWK function is going to give you the wrong first week of the month on an average of 3 out of 7 times (any time the 4th day of the month is less than Thursday).

    Like I said, I can't verify that right now because I'm not on my 2008 box but check it out and see if I'm right.

    On the flip side, that's one hell of a great try without knowing what the definition of a "Broadcast Week" is. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank you Jeff.

    sql_r:... Sorry about that :blush: - I should have googled "Broadcast Calendar" or "Broadcast week" before giving this a shot. It's been a busy couple weeks (this is actually my first post in awhile) but I did try, without any luck, to produce one :crazy:.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Here's some code to generate a Broadcast Calendar table:

    CREATE TABLE BroadcastCalendar (

    [dated] [datetime] NOT NULL PRIMARY KEY

    ,[Media_Week] [bigint] NOT NULL

    ,[Media_Month] [int] NOT NULL

    ,[Media_MonthName] [nvarchar](30) NOT NULL

    ,[Media_Quarter] [int] NOT NULL

    ,[Media_Year] [int] NOT NULL

    );

    with

    n10(N) as (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1)

    ,n100(N) as (SELECT 1 FROM n10 as a,n10 as b)

    ,Tally(N) as (SELECT ROW_NUMBER() OVER(ORDER BY @@SPID) FROM n100 as a,n100 as b)

    ,cal as

    (

    select

    a.dated,

    datepart(dy,a.dated) as DayOfYear,

    datepart(day,a.dated) as DayOfMonth,

    datename(dw,a.dated) as [DayOfWeek],

    datepart(year,a.dated) as CalendarYear,

    1+(ROW_NUMBER() OVER(ORDER BY T1.N)-1)/7 as WeekId

    from Tally T1

    cross apply (select dateadd(day,T1.N,'20091227')) a(dated) -- 28th Dec 2009 is a Monday

    )

    ,weeks as

    (

    select

    WeekId,

    Max(CalendarYear) as CalYear,

    min(dated) as WeekStart,

    CASE WHEN MIN(DayOfMonth) = 1 THEN DATEPART(MONTH,MAX(dated)) ELSE DATEPART(MONTH,MIN(dated)) END AS CalMonth,

    CASE WHEN MIN(DayOfMonth) = 1 THEN DATENAME(MONTH,MAX(dated)) ELSE DATENAME(MONTH,MIN(dated)) END AS CalMonthName

    from cal

    group by WeekId

    )

    INSERT BroadcastCalendar([dated], [Media_Week], [Media_Month], [Media_MonthName], [Media_Quarter], [Media_Year])

    select

    newDates.dated,

    1+(ROW_NUMBER() OVER(PARTITION BY CalYear ORDER BY WeekId)-1)/7 as Media_Week,

    CalMonth as Media_Month,

    CalMonthName as Media_MonthName,

    1+(CalMonth-1)/3 as Media_Quarter,

    CalYear as Media_Year

    from weeks

    cross apply (

    select DATEADD(day,N-1,weeks.WeekStart)

    FROM Tally

    WHERE N>=1 AND N<=7

    ) newDates(dated)

    order by WeekId;

    That will take you up to 2037 roughly...

    Then this iTVF will be your function: (Assuming you still want one for some reason- why not just use the table?)

    CREATE FUNCTION getBroadcastCalendar(@dated DATE)

    RETURNS TABLE

    WITH SCHEMABINDING

    AS

    RETURN(SELECT [Media_Week], [Media_Month], [Media_MonthName], [Media_Quarter], [Media_Year] FROM dbo.BroadcastCalendar WHERE dated=@dated)

    Called like this:

    SELECT [Media_Week], [Media_Month], [Media_MonthName], [Media_Quarter], [Media_Year]

    FROM dbo.getBroadcastCalendar(getdate());

    +-------------------------------------------------------------------------+

    ¦[highlight="#808080"] Media_Week [/highlight]¦[highlight="#808080"] Media_Month [/highlight]¦[highlight="#808080"] Media_MonthName [/highlight]¦[highlight="#808080"] Media_Quarter [/highlight]¦[highlight="#808080"] Media_Year [/highlight]¦

    +------------+-------------+-----------------+---------------+------------¦

    ¦ 15 ¦ 4 ¦ April ¦ 2 ¦ 2013 ¦

    +-------------------------------------------------------------------------+

    EDIT: added qualifier about not needing the function (it was late)

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • mister.magoo (4/9/2013)


    Here's some code to generate a Broadcast Calendar table:

    That's the way!

    You runt it once and you use the outcome (data in the table) indefinitely.

    And if the angry boss comes to you swearing about that stupid developer who cannot even figure out such an obvious thing that this is a week No1, not No2 - you just quietly tweak the numbers in the table according to request and make the bugger happy.

    😀

    You may drop and forget the generation code - it's easier to pass several MB of data to another server.

  • The broadcast calendar is special because it is M-Su weeks organized into months with 13 week quarters meaning every 3 months there is a 5-4-4 or 4-5-4 month pattern with those week counts. The year start date can actually be in the prior calendar year as it is defined as the Monday date for the week containing 1/1. If memory serves, the quarter pattern (4-5-4) can change year over year based on the start date and where 5 week months align. This is what makes it difficult to code. Calendar month does not equal broadcast month necessarily. Attached is a valid example.

    I don't think any of the above code actually does this. Just want to caution readers to review thoroughly before adopting.

    Best...MediaVet

  • Viewing 12 posts - 1 through 12 (of 12 total)

    You must be logged in to reply to this topic. Login to reply