A trigger to calculate a due time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

A trigger to calculate a due time

Author

Topic

sm@softtrade.se
Starting Member

5 Posts

Posted - 2006-04-21 : 10:35:31

Hi i am trying to make a trigger that calculates a new due time from a start time. I have a hour target like 24 hours, but the dates have an starttime and stoptime or the days could even be closed. I need it to calculate the new due datetime using the 24 hours as effective working time.

Any ideas ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-21 : 10:38:04

use dateadd(hour, 24, starttime) to get the due time for 24 hours.

If this is not what you want ? Post your table structure with some sample data and expected result

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-21 : 10:39:38

that was fun reading...

if you're computing a new time out of the old time with start and stop, and putting it in a trigger, why not do the computations along when you add the record or update the record?

or instead of trigger read more on computed columns (haven't tried them myself but never needed to since data gets validated before it gets committed)

--------------------
keeping it simple...

sm@softtrade.se
Starting Member

5 Posts

Posted - 2006-04-21 : 10:40:50

Thanks

But I have to take account of the open time and close time each day

I have something like this with a table of weekdaynames and start and stop times..

/* For Priority 3 Workorders */

if left(@i_priority,1) = '3'
begin
set @prioritytime = 480

/*If the new due time is within the openingtime do the first if */

if @i_duedate >= @starttime and dateadd(minute,@prioritytime,@i_duedate) <= @stoptime
begin
Set @i_wo_date1 = dateadd(minute,@prioritytime,@i_duedate)
set @i_lookup2 = 'Within Open hours'
update tasks
set wo_date1 = dateadd(minute, @ActiveTimeBias, dateadd(day,@offset,@i_wo_date1)),
lookup2 = @i_lookup2
where wo_num = @i_wo_num
end
/* If the time is after hours do the following if */

else if @i_duedate < @starttime
begin
if @daynum = 5 set @offset = 2
set @i_wo_date1 = dateadd(minute,(@prioritytime - (datediff(minute,@i_duedate,@starttime))),dateadd(day,1,@starttime))
set @i_lookup2 = 'Before hours'
update tasks
set wo_date1 = dateadd(minute, @ActiveTimeBias, dateadd(day,@offset,@i_wo_date1)),
lookup2 = @i_lookup2
where wo_num = @i_wo_num
end

/* If the time is before hours do the following if */

else if @i_duedate > @stoptime
begin
if @daynum = 5 set @offset = 2
set @i_wo_date1 = dateadd(minute,15,dateadd(day,1,@starttime))
set @i_lookup2 = 'After hours'
update tasks
set wo_date1 = dateadd(minute, @ActiveTimeBias, dateadd(day,@offset,@i_wo_date1)),
lookup2 = @i_lookup2
where wo_num = @i_wo_num
end

/* If there is a fraction of the new due time left before closinghours */
else
begin
if @daynum = 5 set @offset = 2
set @i_wo_date1 = dateadd(minute,(@prioritytime - (datediff(minute,@i_duedate,@stoptime))),dateadd(day,1,@starttime))
set @i_lookup2 = 'Overlapping hours'
update tasks
set wo_date1 = dateadd(minute, @ActiveTimeBias, dateadd(day,@offset,@i_wo_date1)),
lookup2 = @i_lookup2
where wo_num = @i_wo_num
end
end

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-21 : 10:44:36

these are data validations and IMHO should be handled in the apps level unless you're allowing users to directly add/edit records in the table

or divide the conditions...

in your apps
if condition one call sp1
if condition two call sp2...

--------------------
keeping it simple...

sm@softtrade.se
Starting Member

5 Posts

Posted - 2006-04-21 : 10:50:23

Thanks

Unfortunately we cannot change the helpdesk application so we have to do this as the records are inserted into the database, my problem is that it is becoming a very complex calculation taking care of the evntualities .. I have four different priorities, 60 min 120 min, these are OK but 8 hours and 48 hours need to check for weekends and holidays.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-21 : 10:53:07

then you may want to add a look up table and maintain that table
and use case instead

--------------------
keeping it simple...

sm@softtrade.se
Starting Member

5 Posts

Posted - 2006-04-21 : 11:21:35

Here is the table structure the workschedule table contains the days of week and closed dates.

Begin

declare
@ActiveTimeBias int,
@Bias int,
@i_wo_num int,
@i_priority varchar(30),
@i_wo_date1 datetime,
@i_opendate datetime,
@i_duedate datetime,
@i_lookup2 varchar(30)
select
@i_wo_num = wo_num,
@i_priority = priority,
@i_wo_date1 = wo_date1,
@i_opendate = opendate,
@i_lookup2 = lookup2

from inserted
exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation', 'ActiveTimeBias', @ActiveTimeBias out
exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation', 'Bias', @Bias out

set @i_duedate = dateadd(minute, -@ActiveTimeBias, @i_opendate)
declare
@daynum int,
@dayname varchar(10),
@starthour int,
@startmin int,
@stophour int,
@stopmin int,
@closed bit,
@closeddate datetime

select
@daynum = daynum,
@dayname = dayname,
@starthour = starthour,
@startmin = startmin,
@stophour = stophour,
@stopmin = stopmin,
@closed = closed,
@closeddate = closeddate
from workschedule
where dayname = datename(weekday, @i_duedate)

declare

@date varchar(10),
@starttime datetime,
@stoptime datetime,
@prioritytime int,
@vstarthour varchar(2),
@vstartmin varchar(2),
@vstophour varchar(2),
@vstopmin varchar(2),
@offset int,
@optime int,
@Offsetdec decimal

set @date = convert(varchar,@i_duedate,112)
set @vstarthour = @starthour
set @vstartmin = @startmin
set @vstophour = @stophour
set @vstopmin = @stopmin

set @starttime = convert(datetime,@date+ ' '+@vstarthour+':'+@vstartmin+':00')

set @stoptime = convert(datetime,@date+' '+@vstophour+':'+@vstopmin+':00')
set @offset = 0

if @daynum = 6 set @offset = 2
if @daynum = 7 set @offset = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources