March 21, 2013 at 10:10 am
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!
March 21, 2013 at 11:10 am
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
March 21, 2013 at 11:45 am
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
(SELECTn, DATEADD(DAY,n,@startDate) as [date]
FROM tally
WHERE n<=DATEDIFF(DAY,@startdate,@endDate)),
FilteredCalendar AS
(SELECTn 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:
-- Itzik Ben-Gan 2001
March 21, 2013 at 12:06 pm
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
March 21, 2013 at 12:13 pm
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
(SELECTn, DATEADD(DAY,n,@startDate) as [date]
FROM tally
WHERE n<=DATEDIFF(DAY,@startdate,@endDate)),
FilteredCalendar AS
(SELECTn 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. 😉
March 21, 2013 at 12:17 pm
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!
March 21, 2013 at 5:00 pm
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_DateMedia_Week
2010-01-041
2010-01-112
2010-01-183
2010-01-254
2010-02-015
2010-02-086
2010-02-157
......
2010-12-0649
2010-12-1350
2010-12-2051
2010-12-2752
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)
SELECTn AS [n wearing nothing],
DATEADD(DAY,n,GETDATE()) AS [n dressed in (today's date) + (n)days]
FROM tallyTable;
-- Itzik Ben-Gan 2001
March 21, 2013 at 7:09 pm
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
Change is inevitable... Change for the better is not.
April 9, 2013 at 4:36 pm
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:.
-- Itzik Ben-Gan 2001
April 9, 2013 at 5:43 pm
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);April 10, 2013 at 10:46 pm
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.
_____________
Code for TallyGenerator
April 13, 2021 at 2:47 pm
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 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply