Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Broadcast calender generator Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 10:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 1:17 PM
Points: 236, Visits: 474
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!
Post #1433888
Posted Thursday, March 21, 2013 11:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:47 PM
Points: 12,890, Visits: 31,848
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1433923
Posted Thursday, March 21, 2013 11:45 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 28, 2014 4:49 AM
Points: 556, Visits: 2,581
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


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1433940
Posted Thursday, March 21, 2013 12:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:47 PM
Points: 12,890, Visits: 31,848
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1433951
Posted Thursday, March 21, 2013 12:13 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:16 PM
Points: 23,081, Visits: 31,611
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


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



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1433959
Posted Thursday, March 21, 2013 12:17 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 1:17 PM
Points: 236, Visits: 474
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!
Post #1433960
Posted Thursday, March 21, 2013 5:00 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 28, 2014 4:49 AM
Points: 556, Visits: 2,581


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.. 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;



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1434080
Posted Thursday, March 21, 2013 7:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:47 PM
Points: 36,787, Visits: 31,245
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1434106
Posted Tuesday, April 9, 2013 4:36 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 28, 2014 4:49 AM
Points: 556, Visits: 2,581
Thank you Jeff.

sql_r:... Sorry about that - 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 .



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1440601
Posted Tuesday, April 9, 2013 5:43 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:45 PM
Points: 1,786, Visits: 5,681
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());

┌────────────┬─────────────┬─────────────────┬───────────────┬────────────┐
Media_Week Media_Month Media_MonthName Media_Quarter Media_Year
├────────────┼─────────────┼─────────────────┼───────────────┼────────────┤
│ 15 │ 4 │ April │ 2 │ 2013 │
└────────────┴─────────────┴─────────────────┴───────────────┴────────────┘


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


MM


  • MMGrid Addin
  • MMNose Addin


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

  • Post #1440612
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse