Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2007
    Posts
    56

    Unanswered: 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!

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    If I say yes then what is the next question?

  3. #3
    Join Date
    Jan 2007
    Posts
    56
    :-)

    Could you please share it so that I don't have to reinvent the wheel?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Something like this? (It uses hard-coded holiday dates, but you could easily link to a table instead.)
    Code:
    CREATE function [dbo].[DateRange](@StartDate date, @EndDate date)
    returns table
    --Test parameters
    --declare	@StartDate date
    --declare	@EndDate date
    --set		@StartDate = GETDATE()
    --set		@EndDate = '2013-01-01'
    return
    with Holidays as
    		(
    		select '2011-01-03' as HolidayDate, 'New Years' as Holiday
    		union select '2011-05-30' as HolidayDate, 'Memorial Day' as Holiday
    		union select '2011-07-04' as HolidayDate, 'Independence Day' as Holiday
    		union select '2011-09-05' as HolidayDate, 'Labor Day' as Holiday
    		union select '2011-11-24' as HolidayDate, 'Thanksgiving' as Holiday
    		union select '2011-11-25' as HolidayDate, 'Thanksgiving' as Holiday
    		union select '2011-12-26' as HolidayDate, 'Christmas Day' as Holiday
    
    		union select '2012-01-02' as HolidayDate, 'New Years' as Holiday
    		union select '2012-05-28' as HolidayDate, 'Memorial Day' as Holiday
    		union select '2012-07-04' as HolidayDate, 'Independence Day' as Holiday
    		union select '2012-09-03' as HolidayDate, 'Labor Day' as Holiday
    		union select '2012-11-22' as HolidayDate, 'Thanksgiving' as Holiday
    		union select '2012-11-23' as HolidayDate, 'Thanksgiving' as Holiday
    		union select '2012-12-25' as HolidayDate, 'Christmas Day' as Holiday
    
    		union select '2013-01-01' as HolidayDate, 'New Years' as Holiday
    		union select '2013-05-27' as HolidayDate, 'Memorial Day' as Holiday
    		union select '2013-07-04' as HolidayDate, 'Independence Day' as Holiday
    		union select '2013-09-02' as HolidayDate, 'Labor Day' as Holiday
    		union select '2013-11-28' as HolidayDate, 'Thanksgiving' as Holiday
    		union select '2013-11-29' as HolidayDate, 'Thanksgiving' as Holiday
    		union select '2013-12-25' as HolidayDate, 'Christmas Day' as Holiday
    		),
    	DateList as
    		(
    		select	Number as DateNumber,
    				DATEADD(day, number, @StartDate) as DateValue
    		from	dbo.SequentialNumbers
    		where	DATEADD(day, number, @StartDate) <= @EndDate
    		)
    select	DateList.DateNumber,
    		DateList.DateValue,
    		datepart(weekday, DateList.DateValue) DayOfWeek,
    		datepart(day, DateList.DateValue) DayOfMonth,
    		datepart(DAYOFYEAR, DateList.DateValue) DayOfYear,
    		case when datepart(weekday, DateList.DateValue) between 2 and 6 then 1 else 0 end as IsWeekDay,
    		case when (datepart(weekday, DateList.DateValue) between 2 and 6 and Holidays.HolidayDate is null) then 1 else 0 end as IsWorkDay,
    		case when Holidays.HolidayDate is null then 0 else 1 end as IsHoliday,
    		coalesce(Holidays.Holiday, '') as HolidayName
    from	DateList
    		left outer join Holidays on DateList.DateValue = Holidays.HolidayDate
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...you'll also need a table of sequential numbers (every database should have one anyway):
    Code:
    CREATE TABLE [dbo].[SequentialNumbers] ([Number] [int] NOT NULL PRIMARY KEY CLUSTERED)
    
    ;
    with NumList (NumVal) as
    		(select 0 as NumVal
    		UNION ALL
    		select NumVal + 1
    		from NumList
    		where NumVal < 9)
    insert into SequentialNumbers (Number)
    select	Ones.NumVal + Tens.NumVal * 10 + Hundreds.NumVal * 100 + Thousands.NumVal * 1000
    from	NumList Ones,
    		NumList Tens,
    		NumList Hundreds,
    		NumList Thousands
    where not exists
    		(select	*
    		from	SequentialNumbers
    		where	Number = Ones.NumVal + Tens.NumVal * 10 + Hundreds.NumVal * 100 + Thousands.NumVal * 1000)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    The wheel wasn't invented, it was stolen from aliens.

  7. #7
    Join Date
    Jan 2007
    Posts
    56
    So it was invented ... just not by humans

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I, for one, welcome our new Wheel-wielding Alien Overlords.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •