--**************************************
--     
-- Name: SQL CALENDAR
-- Description:UPDATED:Produces Full Cal
--     endar for each Month in a Year. Just pas

--     s year as an integer. Let me know if it 
--     proves useful. Call it Like: 
EXECUTE Calendar 2002
-- By: John Overton
--
-- Inputs:@year(int)
--
-- Returns:Calendar for each month
--
--This code is copyrighted and has-- limited warranties.Please see http://
--     www.Planet-Source-Code.com/vb/scripts/Sh
--     owCode.asp?txtCodeId=546&lngWId;=5--for details.--**************************************
--     

--**************************************
--     
-- 
-- Name: SQL CALENDAR
-- Description:Produces Full Calendar fo
--     
-- r each Month in a Year. Just pass yea
--     r a
-- s an integer. Let me know if it prove
--     s u
-- seful.
-- By: John Overton
--
-- Inputs:@year(int)
--
-- Returns:Calendar for each month
--
--This code is copyrighted and has-- lim
--     ited warranties.Please see http://
-- www.Planet-Source-Code.com/xq/ASP/txt
--     Cod
-- eId.546/lngWId.5/qx/vb/scripts/ShowCo
--     de.
-- htm--for details.--******************
--     ********************
-- 
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
CREATE PROCEDURE CALENDAR (@YEAR INT) AS
DECLARE @INPUTDATE DATETIME
DECLARE @DATE DATETIME
DECLARE @LASTDATE DATETIME
DECLARE @MONTHDAYCOUNT INT
DECLARE @COUNT INT
DECLARE @DAY VARCHAR(10)
DECLARE @STARTWEEK INT
DECLARE @CURWEEK INT
DECLARE @STARTMONTH INT
SET @INPUTDATE='01/01/' + CAST(@YEAR AS CHAR(4))
PRINT @INPUTDATE
SET @STARTMONTH=1
WHILE @STARTMONTH<=12
    BEGIN
    SET @COUNT=1
    SET @DATE = DATEADD(d, -(DATEPART(dd, @INPUTDATE) - 1), @INPUTDATE)
    SET @LASTDATE=DATEADD(DD,-1,DATEADD(MM,1,@DATE))
    SET @MONTHDAYCOUNT=datediff(d, @date, dateadd(m, 1, @date))
    SET @STARTWEEK=DATENAME(WEEK,@DATE)
    DECLARE @CURRWEEK INT
    DECLARE @CUR INT
    CREATE TABLE #TEMP(
    	WEEK VARCHAR(10),
    	SUNDAY VARCHAR(10),
    	MONDAY VARCHAR(10),
    	TUESDAY VARCHAR(10),
    	WEDNESDAY VARCHAR(10),
    	THURSDAY VARCHAR(10),
    	FRIDAY VARCHAR(10),
    	SATURDAY VARCHAR(10))
    DECLARE @wkcount int
    DECLARE @weeksinmonth int
    DECLARE @EXEC NVARCHAR(2000)
    SET @WKCOUNT=1
    SET @weeksinmonth=datediff(week, @date, @lastdate) + 1
    WHILE @wkcount<= @weeksinmonth
        begin
        INSERT INTO #TEMP VALUES(@wkcount,'SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY')
        SET @WKCOUNT=@WKCOUNT + 1
    end

    WHILE @COUNT<=@MONTHDAYCOUNT
        BEGIN
        	SET @DAY=DATENAME(WEEKDAY,@DATE)
        	IF @STARTWEEK=DATENAME(WEEK,@DATE)
        		SET @CURRWEEK=1
        	ELSE
        	BEGIN
        		SET @CUR=DATENAME(WEEK,@DATE)
        		SET @CURRWEEK=(@CUR-@STARTWEEK)+1
        	END
        	SET @EXEC='UPDATE #TEMP SET ' + @DAY + ' =' + CAST(@COUNT AS CHAR(2)) + ' WHERE WEEK=' + CAST(@CURRWEEK AS CHAR(2))+ 'AND WEEK IS NOT NULL'
        	EXEC SP_EXECUTESQL @EXEC
        	SET @DATE=DATEADD(DD,1,@DATE)
        	SET @COUNT=@COUNT + 1
    END

    UPDATE #TEMP SET SUNDAY=' ' WHERE SUNDAY='SUNDAY'
    UPDATE #TEMP SET MONDAY=' ' WHERE MONDAY='MONDAY'
    UPDATE #TEMP SET TUESDAY=' ' WHERE TUESDAY='TUESDAY'
    UPDATE #TEMP SET WEDNESDAY=' ' WHERE WEDNESDAY='WEDNESDAY'
    UPDATE #TEMP SET THURSDAY=' ' WHERE THURSDAY='THURSDAY'
    UPDATE #TEMP SET FRIDAY=' ' WHERE FRIDAY='FRIDAY'
    UPDATE #TEMP SET SATURDAY=' ' WHERE SATURDAY='SATURDAY'
    SELECT SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY FROM #TEMP
    DROP TABLE #TEMP
    SET @INPUTDATE=DATEADD(MM,1,@INPUTDATE)
    SET @STARTMONTH=@STARTMONTH+1
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO