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

Given a date, halve it... - bit of a challenge maybe

Hi

Given a date of say 15/5/2006 13:00  and then given a time span of say 1 day, how do i then halve that date?

**There is a few catches**

1) Must take into consideration weekends
2) Must take into consideration working hours

As an example, today is the 5th June, if i have a time span of 1 day.

Add one day to 5th June 13:30 = 6th June 13:30
Halve one day (which is based on a 9-5 day, so 4 hours) = 6th June 09:30

Simple :o)

So, if was 6th June at 10:00 and you halved a day and subtracted it, taking into consideration working days it would work out to be the day before because it would be before 9am. Same applies to weekends.

How do i do this with SQL?????

Many Thanks
0
Type25
Asked:
Type25
  • 19
  • 8
  • 6
  • +1
3 Solutions
 
LowfatspreadCommented:
do you mean...

Select startdate
      ,case Datepart(dw,X)
            When 6 then Dateadd(d,2,x)      
            else x
            end as x
 
from (
Select Case when Datepart(hh,x) >= 17
            Then DateAdd(s,
                dateDiff(s,convert(char(8),X,112)+' 17:00:00.000',X)
                ,Dateadd(d,1,Convert(char(8),x,112)+' 09:00:00.000'))
                   else x
                  end as x
     ,startdate
 from (
select dateadd(hh,4,StartDate) as X,startdate
from (select '20060605 10:30:00' as startdate
      union select '20060605 14:30:00'
      union select '20060602 14:30:00'
      ) as qq
) as Y
) as z
0
 
MikeTooleCommented:
You'll need a function to return the new date.

In pseudo code the calculation steps are these:

PeriodInHours = TimeSpan * Conversion Factor
HoursToAdd = PeriodInHours / 2
Days = integer(HoursToAdd/8)
NewDate = StartDate + Days
Hours = remainder(HoursToAdd/8)
NewDate = NewDate + Hours
-- Now take care of the weekends
EndDate = NewDate
Do while EndDate > StartDate
    if WeekNo(EndDate) - WeekNo(StartDate) > 0
        NewDate = NewDate + 2
    end if
    EndDate = EndDate - 7
Loop

I may get a chance later today to set up in TransactSQL if you need it, what are the possibilities for the input time span?

Hope this helps
0
 
Type25Author Commented:
Ughhh....

i'm trying to now use some params with your method, however, running into problems... ie:


DECLARE @StartDate       datetime
DECLARE @TimeSpan      int
SET @StartDate = '5 June 2006 12:00'
SET @TimeSpan = 240 -- minutes (4 hours)

Select @startdate
      ,case Datepart(dw,X)
            When 6 then Dateadd(d,2,x)      
            else x
            end as x
 
from (
Select Case when Datepart(hh,x) >= 17
            Then DateAdd(s,
                dateDiff(s,convert(char(8),X,112)+' 17:00:00.000',X)
                ,Dateadd(d,1,Convert(char(8),x,112)+' 09:00:00.000'))
                   else x
                  end as x
     ,@startdate
 from (
select dateadd(n,@TimeSpan,@StartDate) as X) as Y
) as z

Help! :)

Hi Mike, cheers, i may give that a go myself, the interval will be in minutes, ranges from 15 mins to 5 days (7200 mins)

Thanks
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
NazermohideeenCommented:
Declare @date dateTime
Declare @tSpan int
set @tSpan = 240
set @date = '06/05/2006 10:00:00'
set @date = dateadd(mi, @tSpan, @date)

--Fix Time
Declare @t int
set @t = datePart(hh, @date)
if (@t < 9)
Begin
    set @date = dateadd(d, -1, @date)
    set @date = dateadd(hh, 8, @date)
End
Else if (@t > 17)
Begin
   set @date = dateadd(d, 1, @date)
   set @date = dateadd(hh, -8, @date)
End

--Fix Date  
set datefirst 6
set @t = DatePart(dw, @date)
print @t
set datefirst 7
if (@t <= 2 and @tspan < 0)  set @date = dateadd(d, @t - 3, @date)
if (@t <= 2 and @tspan > 0)  set @date = dateadd(d, @t, @date)
Print @date


Assuming that when subtracting timespan, u end up in a weekend, u choose friday (the last working day), but when adding timespan, u end up in a weekend, u choose the first week day.

Hope it is what u need.
But if u want to find other ways, keep in mind that you will have to process the time first and then the date, to avoid weekends.


NM
0
 
LowfatspreadCommented:
DECLARE @StartDate      datetime
DECLARE @TimeSpan     int
SET @StartDate = '5 June 2006 12:00'
SET @TimeSpan = 240 -- minutes (4 hours)

Select @startdate
      ,case Datepart(dw,X)
            When 6 then Dateadd(d,2,x)      
            else x
            end as x
 
from (
Select Case when Datepart(hh,x) >= 17
            Then DateAdd(s,
                dateDiff(s,convert(char(8),X,112)+' 17:00:00.000',X)
                ,Dateadd(d,1,Convert(char(8),x,112)+' 09:00:00.000'))
                   else x
                  end as x
--     ,@startdate     --<   this originally was just how i passed my test dates up to the top select...  
 from (
select dateadd(n,@TimeSpan,@StartDate) as X) as Y
) as z
0
 
LowfatspreadCommented:
ps my code assumes that the start time will only be Monday to friday...
and is also based on the assumption that the timespan won't go over midnight ...

0
 
Type25Author Commented:
Nazermohideeen

Bit confused by that logic.......  i've put in a few different time spans and it seems to land on weekends

lowfatspread

The start time could potentially occour on a weekend and the timespan could range from 15 mins to 7200 mins....
Any chance of showing me how that might change things? :)

Appreciate the help guys.


0
 
Type25Author Commented:
What might be easier is if i demonstrate the idea behind what i'm trying to accomplish.

There are predefined dates for service standards which range from 15 mins to 5 days.

The user is asked to enter dates into the system, these days might be  

Contacted customer
Visited customer
Report submitted

Once the user has entered a date for Contacted customer, there is a service standard for when they must enter a visited customer date, this for example is 4 days after.

Once the users the date, we then store the 'NextAlertDate' for the users information. This alert date is half of the service standard and must ONLY be visible to the user between mon-fri 9-5.

So for example, a user puts in a contacted customer date and the service standard is 4 days, the next alert time will be in 2 days time (but must be between 9-5 and mon-fri), so if the user filled out the date on the fri, the next alert time would be set to Tues (skipping the weekend)

0
 
NazermohideeenCommented:
Hi,
   
what is the date format you are giving. My code assumes American Date Format (mm/dd/yyyy)

If this works, will explain the logic

NM
0
 
Type25Author Commented:
Date format here is UK: dd/MM/yyyy

0
 
Type25Author Commented:
NM, for example:

Declare @date dateTime
Declare @tSpan int
set @tSpan = 2400 -- 5 days
Set @tSpan = @tSpan / 2 -- 2.5 days
set @date = '05 June 2006 10:00:00'
set @date = dateadd(mi, @tSpan, @date)

--Fix Time
Declare @t int
set @t = datePart(hh, @date)
if (@t <= 9)
Begin
    set @date = dateadd(d, -1, @date)
    set @date = dateadd(hh, 8, @date)
End
Else if (@t >= 17)
Begin
   set @date = dateadd(d, 1, @date)
   set @date = dateadd(hh, -8, @date)
End

--Fix Date  
set datefirst 6
set @t = DatePart(dw, @date)

set datefirst 7
if (@t <= 2 and @tspan < 0)  set @date = dateadd(d, @t - 3, @date)
if (@t <= 2 and @tspan > 0)  set @date = dateadd(d, @t, @date)
Print @date


is incorrect, adding 7200 (5 days / 2) = 2.5 days to 5 June, still comes out as the 5 June ??

0
 
NazermohideeenCommented:
If then just add in the first line

set dateformat dmy


It should work

NM
0
 
Type25Author Commented:
Still same problem, have a look at my example above....

Thanks
0
 
NazermohideeenCommented:
ok I understood, I assume the tspan is 24 hours a day, But u need it as 8 hours a day

Hold on a minute, I will check again
NM
0
 
Type25Author Commented:
Great, thanks!
0
 
NazermohideeenCommented:
Declare @date dateTime
Declare @tSpan int
set @tSpan = 2400 -- 5 days
Set @tSpan = @tSpan / 2 -- 2.5 days

set @date = '05 June 2006 10:00:00'

Declare @temp_tSpan int
set @temp_tSpan  = @tSpan + Round(@tSpan / 480, 0) * 960

set @date = dateadd(mi, @temp_tSpan, @date)


--Fix Time
Declare @t int
set @t = datePart(hh, @date)
if (@t <= 9)
Begin
    set @date = dateadd(d, -1, @date)
    set @date = dateadd(hh, 8, @date)
End
Else if (@t >= 17)
Begin
   set @date = dateadd(d, 1, @date)
   set @date = dateadd(hh, -8, @date)
End

--Fix Date  
set datefirst 6
set @t = DatePart(dw, @date)

set datefirst 7
if (@t <= 2 and @tspan < 0)  set @date = dateadd(d, @t - 3, @date)
if (@t <= 2 and @tspan > 0)  set @date = dateadd(d, @t, @date)
Print @date



This MUST WORK

NM
0
 
Type25Author Commented:
I'll have a play and come back to you ;o)

0
 
Type25Author Commented:
Close.... really close

However, if you put in a start date of 8th June and then add 2.5 days you end up on Sunday

Declare @date dateTime
Declare @tSpan int
set @tSpan = 2400 -- 5 days
Set @tSpan = @tSpan / 2 -- 2.5 days

set @date = '08 June 2006 10:00:00'

Declare @temp_tSpan int
set @temp_tSpan  = @tSpan + Round(@tSpan / 480, 0) * 960

set @date = dateadd(mi, @temp_tSpan, @date)


--Fix Time
Declare @t int
set @t = datePart(hh, @date)
if (@t <= 9)
Begin
    set @date = dateadd(d, -1, @date)
    set @date = dateadd(hh, 8, @date)
End
Else if (@t >= 17)
Begin
   set @date = dateadd(d, 1, @date)
   set @date = dateadd(hh, -8, @date)
End

--Fix Date  
set datefirst 6
set @t = DatePart(dw, @date)

set datefirst 7
if (@t <= 2 and @tspan < 0)  set @date = dateadd(d, @t - 3, @date)
if (@t <= 2 and @tspan > 0)  set @date = dateadd(d, @t, @date)
Print @date
0
 
Type25Author Commented:
Adding
if datename(dw,@date) = 'Saturday' set @date = dateadd(d,2,@date)
if datename(dw,@date) = 'Sunday' set @date = dateadd(d,1,@date)

seems to do the trick though...
0
 
LowfatspreadCommented:
does this work...

DECLARE @StartDate      datetime
DECLARE @TimeSpan     int
SET @StartDate = '5 June 2006 12:00'
SET @TimeSpan = 1240 -- minutes (4 hours)

Select @startdate
      ,case Datepart(dw,X)
            When 6 then Dateadd(d,2,x)      
            else x
            end as x
 
from (
Select Case when Datepart(hh,x) >= 17
            Then DateAdd(s,
                dateDiff(s,convert(char(8),X,112)+' 17:00:00.000',X)
                ,Dateadd(d,1,Convert(char(8),x,112)+' 09:00:00.000'))
            When Datepart(hh,x) < 9
            Then DateAdd(s,datediff(s,Dateadd(d,-1,convert(char(8),X,112)+' 17:00:00.000'),X),
                        Dateadd(d,+1,convert(char(8),x,112)+' 09:00:00.000'))
            else x
            end as x
--     ,@startdate
 from (
select dateadd(d,2 * (@timespan / 480) ,dateadd(d,@timespan / 480,dateadd(n,@TimeSpan % 480,@StartDate))) as X) as Y
) as z


0
 
LowfatspreadCommented:
ie
a work day is 480 hours....
between 9 and 5 ...


can you give some examples of what you'd expect to happend if the starttime was say

6pm on thurs
6pm on Friday

8am on Thursday...


for differing durations ...

hth
0
 
Type25Author Commented:
Nope, because if you stick in the 8th June and then 1200 minutes, it works it out to be weds and it should be monday (2.5 days + 8th June excluding weened)
0
 
Type25Author Commented:


>> can you give some examples of what you'd expect to happend if the starttime was say

sure....

Main thing to keep in mind is the service standards are halved in actual days but it should be worked out in working hours.

I'll work out some times....
 
 
0
 
Type25Author Commented:
6pm on thurs  - Duration of 2.5 days (3600 mins (actual mins)) - Tues @ 1PM
6pm on Friday - Duration of 2.5 days (3600 mins) - Weds @ 1PM

8am on Thursday... - Duration of 2 days (2800 mins) - Friday 5PM - Rolled back to the last working day

Does that make sense?
0
 
Type25Author Commented:
The rolled back bit is the most complicated i think.... if it ends up working out to be between > 5  and < 9 then it will equal 5pm the day before...
0
 
LowfatspreadCommented:
SET @StartDate = '20060608 12:00' --'5 June 2006 12:00'
SET @TimeSpan = 1200 --1240 -- minutes (4 hours)

Select @startdate
      ,case Datepart(dw,X)
            When 6 then Dateadd(d,2,x)  
            when 7 then dateadd(d,1,x)    
            else x
            end as x
 
from (
Select Case when Datepart(hh,x) >= 17
            Then DateAdd(s,
                dateDiff(s,convert(char(8),X,112)+' 17:00:00.000',X)
                ,Dateadd(d,1,Convert(char(8),x,112)+' 09:00:00.000'))
            When Datepart(hh,x) < 9
            Then DateAdd(s,datediff(s,Dateadd(d,-1,convert(char(8),X,112)+' 17:00:00.000'),X),
                        Dateadd(d,+1,convert(char(8),x,112)+' 09:00:00.000'))
            else x
            end as x
--     ,@startdate
 from (
select dateadd(d,2 * (@timespan / 480 / 7) ,dateadd(d,@timespan / 480,dateadd(n,@TimeSpan % 480,@StartDate))) as X) as Y
) as z

0
 
Type25Author Commented:
I may not have made it too clear...

2880 = 2 days

If your start date is 8th June and you try and add 2880 minutes, it ends up being the following weds! (would normally land on the saturday at 12:00 but should be rolled back to 5pm on the friday.

Still appreciating the help.. !!
0
 
LowfatspreadCommented:
sorry forgot the divide by 7 on the week adjustment../.
0
 
LowfatspreadCommented:
>The rolled back bit is the most complicated i think.... if it ends up working out to be between > 5  and < 9 then it will equal 5pm the day before...

yes because its not clear ...

if start at 4pm day1 for duration 5 hours then ends day2 1pm...

if start at day1 a2am for duration 3hours you want it to end on day0 at 5pm?
0
 
Type25Author Commented:

if start at 4pm day1 for duration 5 hours then ends day2 1pm...  - correct, as long as the end calculation ends between 9-5 it's fine.

if start at day1 a2am for duration 3hours you want it to end on day0 at 5pm? - interesting one... but yes it would be rolled back to day0 @ 5pm.

I should have probably mentioned that at the moment the durations are never less than one working day 1440 minutes.
Anything less than that is worked out real time and working hours becomes redundent.

So in theory that second calculation will never be done.


0
 
MikeTooleCommented:
This almost gets you there, let me know if you want more work on it:


create function TargetDate(@StartDate smalldatetime, @Interval integer)
returns smalldatetime
as
begin
Declare @NewDate smalldatetime
set @NewDate = @StartDate
set @interval = @interval / 2
Set @NewDate = DateAdd(d, @interval/(24*60), @NewDate)
Set @NewDate = DateAdd(mi, @interval%2880, @NewDate)

if datepart(dw, @NewDate - datepart(dw,@StartDate)) > 4
      Set @NewDate = dateadd(d, 2, @NewDate)
return @NewDate
end
0
 
NazermohideeenCommented:
There is a bit confusion here

You say that 3600 mins = 2.5 days = 60 hours =  2 days 12 hours.
But you need 2 days 4 hours when u specify 3600


I could also see when u tried to test my example, you specify 2400 = 5 days, which is 8 hours a day. and now if you say 2400 / 2 = 2.5 days which is  days and 4 hours makes sense


First be clear with the requirement.

ofcourse yes, there was a bug with my code fragment, in last line

The following conditions are assumed,
    One day = 8 hours, so 5 days = 2400 mins
    The start time will fall in a working hours and in a working day. (if not, you will have to round the day to the closest working hour)

--------------------------------------
Declare @date dateTime
Declare @tSpan int
set @tSpan = 2400 -- 5 days
Set @tSpan = @tSpan / 2 -- 2.5 days

set @date = '08 June 2006 10:00:00'

Declare @temp_tSpan int
set @temp_tSpan  = @tSpan + Round(@tSpan / 480, 0) * 960

set @date = dateadd(mi, @temp_tSpan, @date)


--Fix Time
Declare @t int
set @t = datePart(hh, @date)
if (@t <= 9)
Begin
    set @date = dateadd(d, -1, @date)
    set @date = dateadd(hh, 8, @date)
End
Else if (@t >= 17)
Begin
   set @date = dateadd(d, 1, @date)
   set @date = dateadd(hh, -8, @date)
End

--Fix Date  
set datefirst 6
set @t = DatePart(dw, @date)

set datefirst 7
if (@t <= 2 and @tspan < 0)  set @date = dateadd(d, @t - 3, @date)
if (@t <= 2 and @tspan > 0)  set @date = dateadd(d, 3 - @t, @date)
Print @date

NM
0
 
Type25Author Commented:
Ok, sorry about the confusion, perhaps i should start again ;)

I need to add a specified number of minutes  (1 day = 1440) to a given date.

If when the minutes are added the new date is not between 9-5 then the date should be rolled back to the last working day at 5pm

So for example:

Start Date: 8th June 2006 12:00

Interval: 2 days (2880 minutes)
End result: 9th June 2006 5PM  (would have landed on a saturday so rolled back to last working day)

Interval: 1 day (1440 minutes)
End result: 9th June 2006 12:00

If the start date is 6th June 6PM and the interval is 1440 minutes then the end result would be 7th June 5PM

Is that any clearer?
 
0
 
Type25Author Commented:
OK, i've had a dabble myself, and i think it's as simple as this:

Declare @tSpan int
set @tSpan = 2880
Set @tSpan = @tSpan / 2

set @date = '05 June 2006 10:00:00'

set @date = dateadd(mi,@tSpan,@date)

if datePart(hh, @date) >= 17 AND datepart(hh,@date) <= 23 SET @date = convert(char(8),@date,112)+' 17:00:00.000'
if datePart(hh, @date) >= 1 AND datepart(hh,@date) < 9 SET @date = dateadd(d,-1,convert(char(8),@date,112)+' 17:00:00.000')

if datename(dw,@date) = 'Saturday' set @date = dateadd(d,-1,convert(char(8),@date,112)+' 17:00:00.000')
if datename(dw,@date) = 'Sunday' set @date = dateadd(d,-2,convert(char(8),@date,112)+' 17:00:00.000')

print @date

don't think it's flawed.
0
 
Type25Author Commented:
Thanks everyone for helping out, to make it fair i'll split the points between all of you.

Hope this is ok.

Thanks again.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 19
  • 8
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now