/* WHAT *******************************************************************************************
*	This is an excercise using date functions and scripting to create a simple calendar
*	This sproc takes a month and year in numerical form and returns a calendar in the results
*	USAGE : EXEC efCalendar 1,2002 = January 2002
**************************************************************************************************/

/*************************************************************************************************/
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE efCalendar @iMo nvarchar(2), @iYr nvarchar(4)

AS

SET NOCOUNT ON
/*************************************************************************************************/

/* DECLARE VARS **********************************************************************************/
DECLARE @iCol int, @sql varchar(30),
	@iFirstDay int, @iOffset int,
	@iNumDays int, @iDay nvarchar(30),
	@dThisMo datetime, @dNextMo datetime,
	@sMoName varchar(15)
/*************************************************************************************************/


/* SET VARS **************************************************************************************/
SELECT @iCol = 1
SELECT @sql = ''
/*************************************************************************************************/

/* DO MONTH CALCULATIONS *************************************************************************/
--get date object for current month
SELECT @dThisMo = CAST(@iMo + '/1/' + @iYr AS smalldatetime)
--get month name
SELECT @sMoName = DATENAME ( mm , @dThisMo )
--get next month
SELECT @dNextMo = DATEADD(mm, 1, @dThisMo)
--get number of days in current month
SELECT @iNumDays = DATEPART (dd, DATEADD(dd, -1, @dNextMo))
--get weekday of current month day 1
SELECT @iFirstDay = DATEPART (dw, @dThisMo)
/*************************************************************************************************/

/* CREATE TEMP TABLE *****************************************************************************/
CREATE TABLE #calendar (
	sun INT DEFAULT 0 NULL,
	mon INT DEFAULT 0 NULL,
	tue INT DEFAULT 0 NULL,
	wed INT DEFAULT 0 NULL,
	thu INT DEFAULT 0 NULL,
	fri INT DEFAULT 0 NULL,
	sat INT DEFAULT 0 NULL
)
/*************************************************************************************************/

/* DO CALENDAR LOOP ******************************************************************************/
WHILE @iCol <= 42	--42 squares in cal table
BEGIN
	-- calculate offset for month
	--1:0
	IF(@iFirstDay) = 1
	BEGIN
		SELECT @iOffset = 0
	END
	ELSE
	--2:-1
	IF(@iFirstDay) = 2
	BEGIN
		SELECT @iOffset = -1
	END
	ELSE
	--3:-2
	IF(@iFirstDay) = 3
	BEGIN
		SELECT @iOffset = -2
	END
	ELSE
	--4:-3
	IF(@iFirstDay) = 4
	BEGIN
		SELECT @iOffset = -3
	END
	ELSE
	--5:-4
	IF(@iFirstDay) = 5
	BEGIN
		SELECT @iOffset = -4
	END
	ELSE
	--6:-5
	IF(@iFirstDay) = 6
	BEGIN
		SELECT @iOffset = -5
	END
	ELSE
	--7:-6
	IF(@iFirstDay) = 7
	BEGIN
		SELECT @iOffset = -6
	END

	--adjust column using offset
	SELECT @iDay = @iCol + @iOffset

	--check for days less than start day
	IF (@iDay < 0)
	BEGIN
		SELECT @iDay = 0
	END

	--check for numbers greater than days in month
	IF (@iDay > @iNumDays)
	BEGIN
		SELECT @iDay = 0
	END

	--build number list to insert
	SELECT @sql = CAST(@sql + @iDay + ',' AS varchar(30))

	--check for saturday(day 7)
	IF (@iCol % 7) = 0
	BEGIN
		--drop the trailing comma
		SELECT @sql = LEFT(@sql, (LEN(@sql) - 1) )
		--insert record into table
		EXEC('INSERT INTO #calendar VALUES(' + @sql + ')')
		--clear statement for next loop
		SELECT @sql = ''
	END

SELECT @iCol = @iCol + 1
END
/*************************************************************************************************/

/*SHOW RESULTS ***********************************************************************************/
SELECT @sMoName AS 'month', @iYr AS 'year'
SELECT * FROM #calendar
/*************************************************************************************************/

/* DROP TEMP TABLE *******************************************************************************/
DROP TABLE #calendar

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
/*************************************************************************************************/

--EXEC efCalendar 3,2003
--DROP PROCEDURE efCalendar