/************************************************************
 Name : Clintz_SP_Calendar
 Purpose: hmmmmmm
 Created: 12212005

************************************************************/
create procedure Clintz_SP_Calendar
 @Month int = ''
 , @Year int = ''

as

if @Month = ''
 set @Month = month(getdate())
if @Year = ''
 set @Year = year(getdate())

declare @tblCalendar table (WeekNum int, Sunday int, Monday int, Tuesday int, Wednesday int, Thursday int, Friday int, Saturday int)
declare @PassedDate datetime, @DayName varchar(50)
declare @LastDayOfMonth int, @WeekNum int, @LastMonthWeekNum int, @cntr int

insert into @tblCalendar (WeekNum)
select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6

set @PassedDate = convert(varchar, @Month) +'/1/' + convert(varchar, @Year)
set @LastDayOfMonth = day(dateadd(d, -1, dateadd(m, 1, @PassedDate)))
set @LastMonthWeekNum = datename(wk, dateadd(d, -1, @PassedDate))

set @cntr = 0

while @cntr < @LastDayOfMonth
 begin
  set @DayName = datename(dw, dateadd(d, @cntr, @PassedDate))
  set @WeekNum = datename(wk, dateadd(d, @cntr, @PassedDate)) - @LastMonthWeekNum + 1
   if @WeekNum < 0
    set @WeekNum = datename(wk, dateadd(d, @cntr, @PassedDate))
   
  if @DayName = 'Sunday'
   begin
    update @tblCalendar
    set Sunday = @cntr + 1
    where WeekNum = @WeekNum
   end
  if @DayName = 'Monday'
   begin
    update @tblCalendar
    set Monday = @cntr + 1
    where WeekNum = @WeekNum
   end
  if @DayName = 'Tuesday'
   begin
    update @tblCalendar
    set Tuesday = @cntr + 1
    where WeekNum = @WeekNum
   end
  if @DayName = 'Wednesday'
   begin
    update @tblCalendar
    set Wednesday = @cntr + 1
    where WeekNum = @WeekNum
   end
  if @DayName = 'Thursday'
   begin
    update @tblCalendar
    set Thursday = @cntr + 1
    where WeekNum = @WeekNum
   end
  if @DayName = 'Friday'
   begin
    update @tblCalendar
    set Friday = @cntr + 1
    where WeekNum = @WeekNum
   end
  if @DayName = 'Saturday'
   begin
    update @tblCalendar
    set Saturday = @cntr + 1
    where WeekNum = @WeekNum
   end

  set @cntr = @cntr + 1
 end

 select datename(m, @PassedDate) as [Month], datename(yyyy, @PassedDate) as [Year]

 select isnull(convert(varchar, Sunday), '') as Sunday
  , isnull(convert(varchar, Monday), '') as Monday
  , isnull(convert(varchar, Tuesday), '') as Tuesday
  , isnull(convert(varchar, Wednesday), '') as Wednesday
  , isnull(convert(varchar, Thursday), '') as Thursday
  , isnull(convert(varchar, Friday), '') as Friday
  , isnull(convert(varchar, Saturday), '') as Saturday
 from @tblCalendar
 where (isnull(Sunday, 0) + isnull(Monday, 0) + isnull(Tuesday, 0) +
  isnull(Wednesday, 0) + isnull(Thursday, 0) + isnull(Friday, 0) +
  isnull(Saturday, 0)) > 0

/*

sp_calendar 10, 1999

*/