asked on

auto Date and sql not working together!

I have to create a query that will produce a calendar for schedules.
The query works with the java code passing the dates via auto date - however
it is not working correctly using the autodate options coded into the query here.
I need to keep the autodate in the sql because one of the reports passes a string variable.
ANY suggestions -
I am including the query.

USE [acisql2]
GO
/****** Object:  StoredProcedure [dbo].[reportSchedulesCalendar]    Script Date: 05/11/2011 15:09:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[reportSchedulesCalendar]
	@startDate SMALLDATETIME = null,
	@endDate SMALLDATETIME = null,
	@utilityKey VARCHAR(38),
	@autodate int = null
AS
declare @begin SMALLDATETIME
declare @end SMALLDATETIME
set @begin = cast(@startDate as SMALLDATETIME) 
set @end =cast(@endDate  as SMALLDATETIME)
/* tester
	exec reportSchedulesCalendar '2011-05-08 00:00:00.0', '2011-05-14 23:59:59.000', 'B89153AC-1323-49F4-BCA4-F7BFC4E04592'
	exec reportSchedulesCalendar null, null, 'B89153AC-1323-49F4-BCA4-F7BFC4E04592','8'

*/
IF @autodate is not null
Begin
	IF @autodate = '0' BEGIN
		SET @begin = DATEADD(m,DATEDIFF(m,1,GETDATE()),0) -- this month
		SET @end = DATEADD(m,DATEDIFF(m,1,GETDATE()),+30)
	/* tester
	exec reportSchedulesCalendar null, null, 'B89153AC-1323-49F4-BCA4-F7BFC4E04592','0'
	*/
	END		
	IF @autodate = '1' BEGIN
	    SET @begin = GETDATE()-- today
		SET @end = DATEADD(dd, DATEDIFF(dd,0,getdate()),0)
	/* tester
	exec reportSchedulesCalendar null, null, 'B89153AC-1323-49F4-BCA4-F7BFC4E04592','1'
	*/
	END		
	IF @autodate = '2'  BEGIN
		SET @begin = DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0)-- yesterday
		SET @end = DATEADD(dd, DATEDIFF(dd,0,getdate()),-1)
	/* tester
	exec reportSchedulesCalendar null, null, 'B89153AC-1323-49F4-BCA4-F7BFC4E04592','2'
	*/
	END		
	IF @autodate = '3'  BEGIN
		SET @begin = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),-1)-- this week
		SET @end = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),+5)
	/* tester
	exec reportSchedulesCalendar null, null, 'B89153AC-1323-49F4-BCA4-F7BFC4E04592','3'
	*/
	END		
	IF @autodate = '4' BEGIN
		SET @begin = DATEADD(wk,DATEDIFF(wk,0,GETDATE())-1,0) -- last week
		SET @end = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),-1)
	/* tester
	exec reportSchedulesCalendar null, null, 'B89153AC-1323-49F4-BCA4-F7BFC4E04592','4'
	*/
	END		
	IF @autodate = '5' BEGIN
		SET @begin = DATEADD(m,DATEDIFF(m,0,GETDATE()),0) -- this month
		SET @end = DATEADD(m,DATEDIFF(m,0,GETDATE()),+30)
	/* tester
	exec reportSchedulesCalendar null, null, 'B89153AC-1323-49F4-BCA4-F7BFC4E04592','5'
	*/
	END		
	IF @autodate = '6' BEGIN
		SET @begin = DATEADD(m,DATEDIFF(m,0,GETDATE())-1,0) -- last month
		SET @end = DATEADD(m,DATEDIFF(m,0,GETDATE())+0,0)
	/* tester
	exec reportSchedulesCalendar null, null, 'B89153AC-1323-49F4-BCA4-F7BFC4E04592','6'
	*/
	END		
	IF @autodate = '7' BEGIN
		SET @begin = DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)--this year
		SET @end = DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
	/* tester
	exec reportSchedulesCalendar null, null, 'B89153AC-1323-49F4-BCA4-F7BFC4E04592','7'
	*/
	END		
	IF @autodate = '8' BEGIN
		SET @begin = DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0) -- last year
		SET @end = DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
	/* tester
	exec reportSchedulesCalendar null, null, 'B89153AC-1323-49F4-BCA4-F7BFC4E04592','8'
	*/
	END		
	IF @autodate = '9' BEGIN
		SET @begin =  @begin
		SET @end = @end
	/* tester
	exec reportSchedulesCalendar null, null, 'B89153AC-1323-49F4-BCA4-F7BFC4E04592','9'
	*/	
	END
END
	IF @begin IS NULL SET @begin = DATEADD(m,DATEDIFF(m,0,GETDATE()),0) -- this month
	IF @end IS NULL SET @end = DATEADD(m,DATEDIFF(m,0,GETDATE()),+30)
SET NOCOUNT ON



create table #calendarBlock (
	cbKey uniqueidentifier primary key default newid(),
	cbSchedule uniqueidentifier,
	cbStart datetime,
	cbEnd datetime,
	cbType varchar(5) default '0',
	cbDescription varchar(500) default ''
)

declare @temp datetime
set @temp = @begin

--add each day
while not exists(select * from #calendarBlock where YEAR(@temp) = YEAR(cbStart) and MONTH(@temp) = MONTH(cbStart) AND DAY(@temp) = DAY(cbStart))
  begin
	declare @date varchar(50)
	set @date = cast(MONTH(@temp) as varchar(2)) + '/' + cast(DAY(@temp) as varchar(2)) + '/' + cast(YEAR(@temp) as varchar(4))
	insert into #calendarBlock(cbStart, cbEnd) values (CAST(@date as datetime), DATEADD(SECOND, -1, DATEADD(DAY, 1, CAST(@date as datetime))))	
	if datediff(day, @temp, @end) <> 0 
	begin
		print 'temp: '+ + cast(@temp as varchar(50))
		print 'enddate: '+ cast(@end as varchar(50))
		print datediff(day, @temp, @end)
		set @temp = dateadd(dd, 1, @temp)
		print 'temp: '+ + cast(@temp as varchar(50))
		print 'enddate: '+ cast(@end as varchar(50))
	 end
	ELSE if (@temp < @end)
	BEGIN
	print 'temp: '+ + cast(@temp as varchar(50))
		print 'enddate: '+ cast(@end as varchar(50))
		print datediff(day, @temp, @end)
		set @temp = dateadd(ms, 1, @temp)
		print 'temp: '+ + cast(@temp as varchar(50))
		print 'enddate: '+ cast(@end as varchar(50))
	END
	
  end
  PRINT @temp
  PRINT @end
  PRINT @begin
  PRINT @autodate
--add each normal shift
insert into #calendarBlock(cbSchedule, cbStart, cbEnd, cbType)
select sKey, dateadd(MINUTE, nsLowMinute, cbStart), dateadd(MINUTE, nsHighMinute, cbStart), '1 ns' 
	from #calendarBlock 
		inner join normalShift on DATEPART(weekday, cbStart) = nsWeekDay
		inner join schedule on sKey = nsSchedule 
		where sUtility = @utilityKey
--add each holiday
insert into #calendarBlock(cbSchedule, cbStart, cbEnd, cbType, cbDescription)
select sKey, cbStart, cbEnd, '4 h', 'Holiday: ' + hDescription  
	from #calendarBlock 
		inner join holiday on hMonth = MONTH(cbStart) and hDay = DAY(cbStart) and hYear = YEAR(cbStart)
		inner join schedule on sKey = hSchedule		
	where cbSchedule is null and sUtility = @utilityKey
update #calendarBlock set cbtype = '2 cns', cbDescription = '*Cancelled: See Holiday' where cbkey in (select ns.cbkey from #calendarBlock ns 
	inner join #calendarBlock h on 
			ns.cbtype = '1 ns' 
			and ns.cbkey <> h.cbKey 
			and ns.cbschedule = h.cbschedule 
			and ns.cbStart between h.cbStart and h.cbEnd 
			and ns.cbEnd between h.cbStart and h.cbEnd)

--add each shift exception
insert into #calendarBlock(cbSchedule, cbType, cbDescription, cbStart, cbEnd)
select sKey, '5 se', 'Shift Exception', 
	case when (seStart >= cbStart and seStart < cbEnd) then seStart
		else cbStart end,
	case when (seEnd > cbStart and seEnd < cbEnd) then seEnd
		else cbEnd end
	from #calendarBlock inner join shiftException on 
		(cbStart >= seStart and cbStart < seEnd) 
		or (cbEnd > seStart and cbStart < seEnd) 
		or (seStart >= cbStart and seStart < cbEnd) 
		or (seEnd > cbStart and seEnd < cbEnd) 
	inner join schedule on sKey = seSchedule
	where cbSchedule is null and sUtility = @utilityKey
		

update #calendarBlock set cbtype = '2 cns', cbDescription = '*Cancelled: See Shift Exception' where cbkey in (select ns.cbkey from #calendarBlock ns 
	inner join #calendarBlock h on 
			ns.cbtype = '1 ns' 
			and ns.cbkey <> h.cbKey 
			and ns.cbschedule = h.cbschedule 
			and ns.cbStart between h.cbStart and h.cbEnd 
			and ns.cbEnd between h.cbStart and h.cbEnd)

update #calendarBlock set cbtype = '3 pns', cbDescription = '*Partial: See Shift Exception' where cbkey in (select ns.cbkey from #calendarBlock ns 
	inner join #calendarBlock ex on 
			ns.cbtype = '1 ns' 
			and ex.cbtype = '5 se' 
			and ns.cbkey <> ex.cbKey 
			and ns.cbschedule = ex.cbschedule 
			and (ns.cbStart between ex.cbStart and ex.cbEnd 
			or ns.cbEnd between ex.cbStart and ex.cbEnd
			or ex.cbStart between ns.cbStart and ns.cbEnd))
			
select sDescription, cbStart, cbEnd, cbType, cbDescription, cbDayOfWeek = DATEPART(weekday, cbStart), CONVERT(nvarchar(30), cbStart, 107) as sDay 
	from #calendarBlock left join schedule on sKey = cbSchedule order by year(cbStart), month(cbStart), day(cbStart), sDescription, cbType, cbEnd

Open in new window

ASKER CERTIFIED SOLUTION

roshnipatel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lcohan

The issue is when you build the @begin and @end dates and just run the queries below to see what I mean and this would be similar for each pair in your SP:

select GETDATE()-- today
select DATEADD(dd, DATEDIFF(dd,0,getdate()),0)

I assume you need something like:

declare @beginingofday datetime
declare @endofday datetime
select @beginingofday = (select dateadd(dd, datediff(dd, 0, getdate()), 0))
select @endofday = (select dateadd(dd, datediff(dd, 0, dateadd(dd, 1, getdate())), -1))
select @endofday = dateadd(hh, 23, @endofday)
select @endofday = dateadd(mi, 59, @endofday)
select @endofday = dateadd(ss, 59, @endofday)
print @beginingofday
print @endofday

You could put the code below in two SQL functions to return you
declare @beginingofday and @endofday but be carefull with smalldatetime VS datetime in this case. For instance if you change it in my code above you get different incorrect result due to the data type precission.

Tagom

ASKER

I was not getting any errors, I had just forgotten to subtract the days so it was pulling one past the desired end timeframe.