SQL FUNCTION Business Date/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

SQL FUNCTION Business Date/Time

Author

Topic

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2007-01-02 : 10:52:42

Is there an SQL function out there, that calculate date like this

Todays Date 02/01/2006 - dd/mm/yyyy
Staff working in time 9.00am
Staff end of work time 17.30pm
A customer log a call at 15.30 and this call should be resolved within 3 hours of recieving the call therefore the call should be resolved before
or at

03/01/2006 - 10.00am

Being that End of work time - Call time = 2hrs remaining 1 hr
then add 1 hr to the start of working tommorrow which is
03/01/2006 - 10.00am

Tips/Code will be appreciated
Thanks

If it is that easy, everybody will be doing it

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-02 : 11:05:50

What about weekends? What aout holidays?

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-02 : 11:06:11

select case when convert(varchar(5),getdate(), 114) > '14:30' then getdate() + '18:30' else getdate() + '03:00' end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2007-01-02 : 11:22:00

Hi nr
Can you explain where is
CallDate
Staff working in time
Staff end of work time

If it is that easy, everybody will be doing it

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-02 : 11:30:49

Staff end of work time is incorporated in to
> '14:30'
i.e. + 3 hrs gives > 17:30

The + '18:30' incorporates the 17:30 and 9:00 + 3 hours.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2007-01-02 : 11:37:11

Hi Thanks for the help
Can you split in pls as to differentiate start,end, calldate as I want to make it a function pls

If it is that easy, everybody will be doing it

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-02 : 11:55:30

For @start and @end as varchar or datetime and calldate as a datetime.

select case when convert(varchar(5),calldate + '03:00', 114) > @end then calldate + convert(datetime,@start) + 1 - @end else calldate end + '03:00'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2007-01-02 : 12:44:55

It evalutes to February why

DECLARE @CallDate datetime
DECLARE @Start varchar(33)
DECLARE @end varchar(33)
DECLARE @PriorityHours varchar(22)

set @start ='9:00'
set @end ='17:30'
set @PriorityHours = '03:00'
set @CallDate = '2007-01-02 15:00'
select case when convert(varchar(5),@calldate + @PriorityHours, 114) > @end then
--@calldate + convert(datetime,@start) + 1 - @end
@calldate + convert(datetime,@start) + 1 - @end
else
@calldate end + @PriorityHours

If it is that easy, everybody will be doing it

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-02 : 14:45:01

Hi --

A couple of things: #1, always, always, always use proper data types. don't store dates and times as varchars. Never. even when storing just a time, use a datetime datatype.

Next: Please see my weblog for a whole bunch of useful date and time manipulation functions here:
http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

If you use those functions, and proper data types, the solution is easy:

DECLARE @CallDate datetime
DECLARE @startTime datetime
DECLARE @endTime datetime
DECLARE @PriorityTime datetime

set @startTime = dbo.Time(9,0,0)   -- 9:00 AM
set @endTime = dbo.Time(17,30,0)   -- 5:30 PM
set @PriorityTime = dbo.Time(3,0,0)  -- 3:00 Hours

set @CallDate = '2007-01-02 17:00'

select case 
    when dbo.TimeOnly(@PriorityTime + @CallDate) > @endTime then
         dbo.DateOnly(@CallDate) + 1 + @startTime + dbo.TimeOnly(@CallDate + @PriorityTime - @endTime) 
    else
	 @PriorityTime + @CallDate
    end

The code for the solution now also reads like the algoritm:

If the time of the call date plus the priority time is greater than the ending date, then start at the next day's starting time plus the difference. Otherwise, return the calldate plus the priority time.

- Jeff

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-02 : 14:56:39

quote:
Originally posted by Peso

What about weekends? What aout holidays?

Peter Larsson
Helsingborg, Sweden

Those will be dealt with in follow-up question 14.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-02 : 14:59:37

Right. I am premature, regarding to who is the OP.

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-02 : 15:06:42

quote:
Originally posted by Peso

Right. I am premature, regarding to who is the OP.

Peter Larsson
Helsingborg, Sweden

Well, now you have time to prepare a detailed discussion and scripts for a business calendar table.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-02 : 15:08:51

Again?
Feels like a deja-vu all over!!

Peter Larsson
Helsingborg, Sweden

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2007-01-03 : 05:15:42

Thanks People it worked, NO deja-vu this time Peso
Here is the code

CREATE FUNCTION dbo.ExpDueDate(@myDate Datetime,@PriorityTime datetime )
returns datetime
as
begin

DECLARE @CallDate datetime
DECLARE @startTime datetime
DECLARE @endTime datetime
--DECLARE @PriorityTime datetime
DECLARE @ReturnedDate datetime

set @startTime ='9:00'
set @endTime ='17:30'
--set @PriorityTime = '03:0'
set @CallDate = @myDate

select @ReturnedDate= case
when dbo.TimeOnly(@PriorityTime + @CallDate) > @endTime then
dbo.DateOnly(@CallDate) + 1 + @startTime + dbo.TimeOnly(@CallDate + @PriorityTime - @endTime)

else
@PriorityTime + @CallDate
end

return dbo.AvoidWeekEnd(@ReturnedDate)
end

go

CREATE FUNCTION dbo.AvoidWeekEnd (@date DATETIME)
RETURNS DATETIME
AS
BEGIN
DECLARE @return DATETIME

IF DATENAME(WEEKDAY, @DATE) = 'Saturday'
BEGIN
SELECT @return = DATEADD(day, 2, @Date)
END
IF DATENAME(WEEKDAY, @DATE) = 'Sunday'
BEGIN
SELECT @return = DATEADD(day, 1, @Date)
END
IF DATENAME(WEEKDAY, @DATE) NOT IN ('Saturday', 'Sunday')
BEGIN
SELECT @return = @DATE
END

return @return

END

go

select dbo.ExpDueDate('05/01/2007 17:30','03:00')

If it is that easy, everybody will be doing it

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2007-01-03 : 05:30:58

Houston, we have a big problem - DEJA-VU Lurking behind

Adding Anything more than 23.59 hour fails
eg select dbo.ExpDueDate('05/01/2007 17:30','25:00')
Help

If it is that easy, everybody will be doing it

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2007-01-03 : 07:46:33

Can somebody 'elp

If it is that easy, everybody will be doing it

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-03 : 09:01:29

EDIT #1: Added in weekend code.
EDIT #2: I misread your "problem".

Are you saying that the priortyTime might be more than 1 day??? then you haven't fully explained your requirements... if the priorityTime is 29 hours, how is the dueDate calculated??? That change in your specification (expected and predicted, of course) doesn't seem to make much sense and has nothing to do with your original question since it completely changes everything ... when will I ever learn ???

Stop using literals for everything, you don't want your code to be dependant on date time formatting on the server! use VALUES for your data whenever possible, as I tried to demonstrate. (my sample data does use date literals since it is only a sample and a little easier to read)

Sounds like you have 4 cases to handle, then. Always think about this stuff logically and write it out on paper before trying to write the code.

1. The time of the Call is BEFORE the startTime -- return startTime plus PriorityTime for call Date
2. The time of the call plus PriorityTime is BEFORE endTime -- return time of the call plus PriorityTime for call date
3. The time of the call plus PriorityTime is AFTER endTime -- return the difference the next day (adjusted for weekends)
4. The time of the Call is AFTER the endTime -- return the startTime plus PriorityTime the next day (adjusted for weekends)

Sounds good? Then simply write it -- and don't use string literals!!!!!!

Also, to make your life easier, use temp tables or table variables with enough sample data to test your conditions:


DECLARE @startTime datetime
DECLARE @endTime datetime
DECLARE @PriorityTime datetime

set @startTime = dbo.Time(9,0,0)   -- 9:00 AM
set @endTime = dbo.Time(17,30,0)   -- 5:30 PM
set @PriorityTime = dbo.Time(3,0,0)  -- 3:00 Hours

-- create sample data for different situations

declare @SampleData table (CallDate datetime, Description varchar(100))

insert into @SampleData
select '2007-01-02 4:00','case 1: Due date should be 3 hours after startTime' union all
select '2007-01-02 10:00', 'case 2: Due date should be 3 hours later' union all
select '2007-01-02 17:00', 'case 3: Due date should be the next day, 2 1/2 hours after startTime' union all
select '2007-01-02 19:00', 'case 4: due date should be the next day, 3 hours after startTime' union all
select '2007-01-05 16:30', 'case 3: due date is 3 days later since this is friday' union all
select '2007-01-06 20:00', 'case 4: due date is 2 days later since this is saturday'

select CallDate, Description, 
   case 
	when dbo.TimeOnly(CallDate) < @startTime then dbo.DateOnly(CallDate) + @StartTime + @PriorityTime
	when dbo.TimeOnly(@PriorityTime + CallDate ) <= @EndTime then @PriorityTime + CallDate
        when dbo.TimeOnly(CallDate) > @endTime then dbo.DateOnly(CallDate) + NextDayAdd + @StartTime + @PriorityTime
        else 
	    dbo.DateOnly(CallDate) + NextDayAdd + @startTime + dbo.TimeOnly(CallDate + @PriorityTime - @endTime) 
   end as DueDate
from 
   (
    select *, 
      case datepart(dw,CallDate) when 6 then 3 when 7 then 2 else 1 end as NextDayAdd
      from @SampleData
    ) x

Of course, you should have a table of holidays and do a lookup on that to further adjust the dueDate if this should be a consideration.

I hope this gives you enough to work with.

- Jeff

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-01-03 : 11:42:24

Peso: Simply find out what they are trying to do. What I mean is, get the project and just do it yourself. You will stop losing your hair, sleep, and temper

The only downside is, your post count will grow MUCH more slowly.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-03 : 12:45:22

On the contraire my friend

Doing his job will make my postcount decrease.

On the other hand, following Madhis example and posting a "Do a Madhi tell you to do", after every "Do it in front-end", will make postcount increase again...

Peter Larsson
Helsingborg, Sweden

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2007-01-04 : 12:45:57

My Calculation does not work help

CREATE FUNCTION dbo.ExpDueCallDate(@myDate Datetime,@PriorityTime int )
returns datetime
as
begin

DECLARE @CallDate datetime
DECLARE @startTime datetime
DECLARE @endTime datetime
DECLARE @ReturnedDate datetime
--DECLARE @PriorityTime INT
declare @jobdate as datetime

set @startTime = '8:00:00'
set @endTime = '18:00:00'
--SET @PriorityTime = '240' -- 4 hours
--SET @jobDate = '08/01/2007 08:01:00'
SET @jobDate = @myDate

declare @jobtime as nvarchar(8)
declare @alerttime as datetime
declare @dayofweek as int
set @jobtime=CONVERT(CHAR(8),@jobdate,8)

--print @jobtime
--print @jobtime
--print datepart(dw,@jobdate)
--got call raised date - time.
set @dayofweek= datepart(dw,@jobdate)
--print @dayofweek
if @dayofweek between 1 AND 5
Begin
IF @jobtime<=@startTime
begin
--set alert = day + starttime +4hrs
set @alerttime=cast(CONVERT(CHAR(8),@jobdate,112)+ DATEADD(minute,@PriorityTime,@startTime ) AS DateTime)
--PRINT 'IF 1'
end
IF @jobtime>=@startTime AND @JobTime <= DATEADD(minute,@PriorityTime*-1,@endTime )
begin
set @alerttime=dateadd(minute,@PriorityTime,@jobdate)
--PRINT 'IF 2'
end

--IF @jobtime>=@startTime AND @jobtime<=@endTime
--PRINT cast(dateadd(minute,@PriorityTime*-1,@Endtime) as datetime)
--PRINT cast((@jobtime) as datetime)

IF cast((@jobtime) as datetime)>=cast(dateadd(minute,@PriorityTime*-1,@Endtime) as datetime) AND cast((@jobtime) as datetime) <=@endTime
Begin
--set alert = day + 18hrs OR + another 48 if rolled over to saturday
DECLARE @EM INT
DECLARE @MM INT
SET @EM =datediff(minute,cast(@endTime AS datetime),cast('00:00:00' AS datetime)+1)
SET @MM =datediff(MINUTE,cast('00:00:00' AS datetime),cast(@startTime AS datetime))
set @alerttime=dateadd(MINUTE,@EM+@MM+@PriorityTime,@jobdate)

--PRINT 'IF 3'
if datepart(dw,@alerttime)>5
BEGIN
set @alerttime=dateadd(hh,18+48,@jobdate)
--PRINT 'IF 4'
END
End

IF @jobtime>=@endTime
begin
IF @dayofweek<>5
Begin
--monday to thursday - set alert = day+1 + startt + 4
--set @alerttime=cast(CONVERT(CHAR(8),@jobdate+1,112)+ ' 12:00:00' AS DateTime)
set @alerttime=cast(CONVERT(CHAR(8),@jobdate+1,112)+ dateadd(minute,@prioritytime,@startTime) AS DateTime)
--PRINT 'IF 5'
End
ELSE
BEGIN
--Friday - rollover to monday - set alert = day+(8-dayofweek) + startt + 4
SET @alerttime=cast(CONVERT(CHAR(8),@jobdate+(8-@dayofweek),112)+ dateadd(minute,@prioritytime,@startTime) AS DateTime)
---PRINT 'IF 6'
END
end
END

if @dayofweek between 6 AND 7
begin
--set alert = day+(8-dayofweek) + startt + 4
set @alerttime=cast(CONVERT(CHAR(8),@jobdate+(8-@dayofweek),112)+ dateadd(minute,@prioritytime,@startTime) AS DateTime)
--PRINT 'IF 7'
end

return @alerttime

end

go
--1440 = 24hrs
select dbo.ExpDueCallDateX('04/01/2007 16:00:00',1440)

If it is that easy, everybody will be doing it

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-04 : 13:35:08

Well, I guess if you are going to completely ignore my advice and the effort I put in to help and do things the hard way, more power to you ... good luck.

- Jeff

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources