declare @counter int, @currentdate datetime 
declare @cases nvarchar(10), @MyMessage nvarchar(1000), @CRLF char(2)
declare @month int, @year int, @date datetime, @rows int

	set @month = datepart(month,getdate() - 1) 
	set @year = datepart(year,getdate() - 1)
	set @currentdate = '2/1/2005'
	set @date = convert(varchar, @year) + right('0' + convert(varchar, @month), 2) + '01'

	set @rows = (select count(casenumber) from tblCapRec 
			where updated NOT BETWEEN 
			dateadd( d, -1 , dateadd( m , 1 , @date ) ) - (datediff(d, @date, dateadd(m, 1, @date)) -1)-- first day of month
			AND dateadd( d, -1 , dateadd( m , 1 , @date )))
	set @counter = 0
	set @cases = (select top 1 casenumber from tblCapRec 
			where updated NOT BETWEEN 
			dateadd( d, -1 , dateadd( m , 1 , @date ) ) - (datediff(d, @date, dateadd(m, 1, @date)) -1)-- first day of month
			AND dateadd( d, -1 , dateadd( m , 1 , @date ))--last day of month 
			order by casenumber)

	set @MyMessage = @cases
	set @CRLF = char(10) + char(13)
		 
	WHILE @counter < @rows  - 2
	BEGIN

	  set @cases = (select top 1 casenumber from tblCapRec 
			where (CaseNumber > @cases) AND (updated NOT BETWEEN 
			dateadd( d, -1 , dateadd( m , 1 , @date ) ) - (datediff(d, @date, dateadd(m, 1, @date)) -1)-- first day of month
			AND dateadd( d, -1 , dateadd( m , 1 , @date )))--last day of month 
			order by casenumber)
	  set @MyMessage =  @MyMessage + @CRLF + @cases
	  set @counter = @counter + 1

	 END

	if (select count(casenumber) from tblCapRec 
			where updated NOT BETWEEN @currentdate - 1 AND dateadd( d, -1 , dateadd( m , 1 , @date )) ) = 0
		set @MyMessage = 'All Cases Were Worked On This Month.'
	else
		set @MyMessage = 'Here Are The Cases That Were NOT Worked On Last Month:'
			+ @CRLF  + @CRLF + @MyMessage 
		
	
		exec master..xp_sendmail 
		@recipients = 'brenda@capitaltracer.com', 
		@subject = 'Cases NOT Worked On Last Month',
		@message = @MyMessage