• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

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

0
Tagom
Asked:
Tagom
1 Solution
 
roshnipatelCommented:
I don't see what the problem is... I ran your code block
/****** 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)


print 'Autodate: ' + CAST(@autodate as varchar(10))
print 'Begin: ' + CAST(@begin  as varchar(50))
print 'End: ' + CAST(@end  as varchar(50))

Open in new window


and got these results:
Autodate: 0
Begin: May  1 2011 12:00AM
End: May 31 2011 12:00AM
Autodate: 1
Begin: May 11 2011  6:46PM
End: May 11 2011 12:00AM
Autodate: 2
Begin: May 10 2011 12:00AM
End: May 10 2011 12:00AM
Autodate: 3
Begin: May  8 2011 12:00AM
End: May 14 2011 12:00AM
Autodate: 4
Begin: May  2 2011 12:00AM
End: May  8 2011 12:00AM
Autodate: 5
Begin: May  1 2011 12:00AM
End: May 31 2011 12:00AM
Autodate: 6
Begin: Apr  1 2011 12:00AM
End: May  1 2011 12:00AM
Autodate: 7
Begin: Jan  1 2011 12:00AM
End: Jan  1 2012 12:00AM
Autodate: 8
Begin: Jan  1 2010 12:00AM
End: Jan  1 2011 12:00AM
Autodate: 9
Begin: May  1 2011 12:00AM
End: May 31 2011 12:00AM

Open in new window


The only thing that I saw was that you may need to subract 1 day from options 6, 7, and 8.

What error or issue are you getting?
0
 
lcohanDatabase AnalystCommented:
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.
0
 
TagomAuthor Commented:
I was not getting any errors, I had just forgotten to subtract the days so it was pulling one past the desired end timeframe.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now