Task Calendar example

This example just want to show different ways to solve specific problems, means that this script contains unnecessary processes.

Best regards

--Declare a new table where I will insert every task

DECLARE @CalendarX TABLE  (
[ID] INT IDENTITY(1,1)  ,
[TaskDate] datetime ,
[TaskName] varchar(255)
)

--Other table where insert every day of month
DECLARE @DaysofMonth TABLE  (
[ID] INT   ,
[TaskDate] datetime
)

SET NOCOUNT ON
--Insert examples task rows
Insert into @CalendarX ([TaskDate], [TaskName]) values ( '2010-10-20', 'Task 1')
Insert into @CalendarX ([TaskDate], [TaskName]) values ( '2010-10-19', 'Task 2')
Insert into @CalendarX ([TaskDate], [TaskName]) values ( '2010-10-15', 'Task 3')
Insert into @CalendarX ([TaskDate], [TaskName]) values ( '2010-10-8', 'Task 4')
Insert into @CalendarX ([TaskDate], [TaskName]) values ( '2010-10-21', 'Task 5')
SET NOCOUNT OFF

DECLARE @daytosee   DATETIME
DECLARE @FirstDayofMonth DATETIME
DECLARE @LastDayofMonth  DATETIME
DECLARE @CountofDays  SMALLINT
DECLARE @CurrentDay   SMALLINT
DECLARE @XMLDATA   XML

--Set each var
SET @CurrentDay   = 1
SET @daytosee   = DATEADD(m,1,GETDATE())
SET @CountofDays  = 0 -- Set to zero
SET @FirstDayofMonth         = CONVERT(DATETIME,CONVERT(VARCHAR(4),DATEPART(yyyy, @daytosee)) 

    + '/' +  CONVERT(VARCHAR(2),DATEPART(m,@daytosee)) 

    + '/01' )
SET @LastDayofMonth  = DATEADD(d,-1, dateadd(m, 1, @FirstDayofMonth))
SET @CountofDays  = DATEDIFF(d, @FirstDayofMonth, @LastDayofMonth ) + 1

--Show values


--Run a simple while
WHILE @CurrentDay < @CountofDays BEGIN  

INSERT INTO @DaysofMonth 

 VALUES (@CurrentDay, DATEADD(d, @CurrentDay -1, @FirstDayofMonth))  

 SET @CurrentDay = @CurrentDay + 1 

END  

--Join the tables  

BEGIN  

 WITH FinalCalendar AS (SELECT

   ISNULL(cx.ID, 0) AS ID,        

   dm.TaskDate ,        

   ISNULL(cx.TaskName, '[Empty]') AS TaskName       

  FROM 

   @DaysofMonth AS dm

  LEFT join        

   @CalendarX  AS cx

  ON (dm.TaskDate = cx.TaskDate))  

 SELECT @XMLDATA  = CAST((SELECT * FROM FinalCalendar FOR XML AUTO) AS XML)  

END  

--Show the result  

SELECT   

 @DayToSee     AS DayToSee     ,   

 @FirstDayofMonth  AS FirstDayofMonth  ,   

 @LastDayofMonth   AS LastDayofMonth  ,   

 @CountofDays    AS CountofDays   ,  

 @XMLDATA             AS Calendar  

/* Compatible SQL 2008 SQL 2008 compatible */