May 7, 2008 at 7:44 am
Michael, what if starttime and endtime is NOT on weekend as op wrote originally?
Jeff, what if starttime AND endtime is ON weekend as op wrote originally?
DECLARE@Date1 DATETIME,
@Date2 DATETIME
SELECT@Date1 = '20061018 15:00',-- Wednesday
@Date2 = '20061023 15:00'-- Monday
SELECTCOUNT(*)
FROM(
SELECTDATEADD(HOUR, h.Number, DATEADD(DAY, d.Number, @Date1)) AS theDateTime
FROMmaster..spt_values AS d
INNER JOINmaster..spt_values AS h ON h.Type = 'p'
WHEREd.Type = 'p'
AND d.Number <= DATEDIFF(DAY, @Date1, @Date2)
AND h.Number BETWEEN 0 AND 23
AND DATEADD(HOUR, h.Number, DATEADD(DAY, d.Number, @Date1)) < @Date2
) AS p
WHEREDATENAME(WEEKDAY, theDateTime) NOT IN ('Saturday', 'Sunday')
Also try with
PesoJeffMichael
20061018 15:00 to 20061023 15:0072724
20061021 15:00 to 20061022 15:000-240
20061021 15:00 to 20061028 15:001201055
N 56°04'39.16"
E 12°55'05.25"
May 7, 2008 at 8:20 am
Somewhat shorter code
SELECTCOUNT(*)
FROMmaster..spt_values AS d
INNER JOINmaster..spt_values AS h ON h.Type = 'p'
WHEREd.Type = 'p'
AND d.Number <= DATEDIFF(DAY, @Date1, @Date2)
AND h.Number BETWEEN 0 AND 23
AND DATEADD(HOUR, h.Number, d.Number + @Date1) < @Date2
AND DATENAME(WEEKDAY, d.Number + @Date1) NOT IN ('Saturday', 'Sunday')
N 56°04'39.16"
E 12°55'05.25"
May 7, 2008 at 8:33 am
Peso (5/7/2008)
Somewhat shorter code
SELECTCOUNT(*)
FROMmaster..spt_values AS d
INNER JOINmaster..spt_values AS h ON h.Type = 'p'
WHEREd.Type = 'p'
AND d.Number <= DATEDIFF(DAY, @Date1, @Date2)
AND h.Number BETWEEN 0 AND 23
AND DATEADD(HOUR, h.Number, d.Number + @Date1) < @Date2
AND DATENAME(WEEKDAY, d.Number + @Date1) NOT IN ('Saturday', 'Sunday')
You can convert that to a version that supports non-English by substituting this line
AND DATENAME(WEEKDAY, d.Number + @Date1) NOT IN ('Saturday', 'Sunday')
with this line:
and (datediff(dd,'17530101',@Date1)+d.Number)%7 < 5
May 7, 2008 at 9:03 am
Peso,
1. I'm not too sure what your query is trying to accomplish.
2. I responded to daljitphull's question of how to get this sort of thing into a trigger. He will have to replace the workdays function with whatever works.
3. Indeed, Jeff's code produces incorrect results for the 3 set of dates you indicated. It seems to be subtracting a zero-date with only the time portion from a numeric value - strange -or I'm missing something. But, I would have assumed the op had worried about this back in october 2006 when this code was published.
So I guess it'a back to the drawing board.
May 7, 2008 at 2:25 pm
Finally figured out Jeff's code. It relies a lot on default behavior when using datetime variables in arithmetic statements. In any case, it appears to be compensating for Saturday and/or Sunday twice.
So I decided to start from scratch using easy to follow logic. It still uses the base part of Jeff's formula at some point and thus relies on this 'weird' behavior of datediff with 'dw' which really counts the number of week boundaries between the two dates. It also uses Jeff's technique for stripping the time from a datetime variable.
This produces Peso's results.
select dbo.workhours('20061018 15:00','20061023 15:00')
select dbo.workhours('20061021 15:00','20061022 15:00')
select dbo.workhours('20061021 15:00','20061028 15:00')
if exists (select name from sysobjects where name='workhours' and type='FN') drop function workhours
go
create function workhours(@StartDate datetime, @EndDate datetime) returns float as
begin
-- calculates number of hours between two datetime values excluding weekends
declare @dowSat as int
declare @dowSun as int
declare @dowSd as int
declare @dowEd as int
declare @sd as datetime
declare @ed as datetime
declare @sd2 as datetime
declare @ed2 as datetime
declare @diff as bigint
-- capture these for known dates as they may vary based on SET DATEFIRST
set @dowSat=datepart(dw,'2006-10-28')
set @dowSun=datepart(dw,'2006-10-29')
-- if necessary, 'normalize' start date to a Monday date
set @sd=@StartDate
set @dowSd=datepart(dw,@sd)
if @dowSd = @dowSun
set @sd=dateadd(dd,1,@sd) -- add one day to get to Monday
else if @dowSd = @dowSat
set @sd=dateadd(dd,2,@sd) -- add two days to get to Monday
-- if necessary, 'normalize' end date to a Friday date
set @ed=@EndDate
set @dowEd=datepart(dw,@ed)
if @dowEd = @dowSun
set @ed=dateadd(dd,-2,@ed) -- subtract two days to get to Friday
else if @dowEd = @dowSat
set @ed=dateadd(dd,-1,@ed) -- subtract one day to get to Friday
-- generate a start date with time portion set to 23:59:59
set @sd2=dateadd(dd,datediff(dd,0,@sd),0) -- strip time portion
set @sd2=dateadd(ss,86399,@sd2) -- get to 23:59:59
-- generate an end date with time portion set to 00:00:00
set @ed2=dateadd(dd,datediff(dd,0,@ed),0)
set @diff=datediff(dd,@sd,@ed)+1-datediff(wk,@sd,@ed)*2 -- jeff's formula calculating working days
set @diff=@diff*86400 -- convert to seconds
if @dowSd <> @dowSat and @dowSd <> @dowSun begin
set @diff=@diff-86400 -- subtract a full day for start date
set @diff=@diff+datediff(ss,@sd,@sd2) -- add number of seconds from start date/time to end of that day
end
if @dowEd <> @dowSat and @dowEd <> @dowSun begin
set @diff=@diff-86400 -- subtract a full day for end date
set @diff=@diff+datediff(ss,@ed2,@ed) -- add number of seconds from end date at hour 00:00:00 to end date/time
end
return round(cast(@diff/3600.0 as float),2) -- in hours or fraction thereof (choose your rounding value)
end
May 7, 2008 at 11:59 pm
Playing around with Jeff's code a bit, I think I have found the fix. It now returns correct results for Peso's dates and I tried it with thousands of randomly selected dates comparing the results with my function. All's well.
if exists (select name from sysobjects where name='workhours_jeff' and type='FN') drop function workhours_jeff
go
create function workhours_jeff(@StartDate datetime, @EndDate datetime) returns float as
begin
return CAST(
(
(DATEDIFF(dd,@StartDate,@EndDate)+1)
-(DATEDIFF(wk,@StartDate,@EndDate)*2)
-(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw,@EndDate) = 'Saturday' THEN 1 ELSE 0 END)
)
-(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday' or DATENAME(dw,@StartDate) = 'Saturday' THEN 0
ELSE CAST(CONVERT(CHAR(12),@StartDate,114) AS DATETIME) END)
-(CASE WHEN DATENAME(dw,@EndDate) = 'Sunday' or DATENAME(dw,@EndDate) = 'Saturday' THEN 0
ELSE (1.0-CAST(CONVERT(CHAR(12),@EndDate,114) AS DATETIME)) END)
AS FLOAT) * 24
end
go
May 8, 2008 at 12:47 am
Peso (5/7/2008)
Michael, what if starttime and endtime is NOT on weekend as op wrote originally?Jeff, what if starttime AND endtime is ON weekend as op wrote originally?
Dunno.... I wrote the original function to handle whole days. It would always return the correct answer even if something starts on Sat and ends on Sun. I'm thinking I blew it on the time based one.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 8, 2008 at 1:15 am
Excellent!
Nice work and faster than my suggestion.
Michaels improved version of Jeffs original query also works with dates only (time set to 00:00:00).
This is a tool I'll keep. Thanks.
I found a bug in my suggestion and last line should be
AND DATENAME(WEEKDAY, DATEADD(HOUR, h.Number, d.Number + StartDate)) NOT IN ('Saturday', 'Sunday')
N 56°04'39.16"
E 12°55'05.25"
May 8, 2008 at 1:53 am
Peso,
I have decided to take a deeper look into your query. It is certainly one if the strangest pieces of code I have seen - and all for the purpose of calculating the number of hours between two dates. Can you explain how to arrived at this logic - before I go completely crazy.:w00t:
Can you give a short phrase that explains it in a nutshell?
May 8, 2008 at 4:06 am
Sure. No problems. This is not one of the most efficient queries, but it gets the work done and it was fast to write.
DECLARE@Date1 DATETIME,
@Date2 DATETIME
SELECT@Date1 = '20080610',
@Date2 = '20080711'
SELECTCOUNT(*)
FROMmaster..spt_values AS d
INNER JOINmaster..spt_values AS h ON h.Type = 'p'
WHEREd.Type = 'p'
AND d.Number <= DATEDIFF(DAY, @Date1, @Date2)
AND h.Number BETWEEN 0 AND 23
AND DATEADD(HOUR, h.Number, d.Number + @Date1) < @Date2
AND DATENAME(WEEKDAY, DATEADD(HOUR, h.Number, d.Number + @Date1)) NOT IN ('Saturday', 'Sunday')
The master..spt_values table is nothing more than Tally table.
The base of the query above is to make @Date1 as "basedate" and @Date2 as an offset to @Date1.
To do this I calculate the number of days between the the dates with "AND d.Number <= DATEDIFF(DAY, @Date1, @Date2)".
This offset days I then CROSS JOIN with 0-23 which is the number of hours (24) for a day with "AND h.Number BETWEEN 0 AND 23".
This should look something like
DaysHours
00
01
02
03
......
023
10
11
...
2...
3...
and so on.
I then make sure that the offset does not pass beyond @Date2 with "AND DATEADD(HOUR, h.Number, d.Number + @Date1) < @Date2"
And the exclude weekends, I have to check for the offset datetime with "AND DATENAME(WEEKDAY, DATEADD(HOUR, h.Number, d.Number + @Date1)) NOT IN ('Saturday', 'Sunday')"
Does it make some sense?
N 56°04'39.16"
E 12°55'05.25"
May 8, 2008 at 4:39 am
Yes it makes sense now. So you are counting the hours.
The days go from 0 to 255 and the hours from 0 to 23 giving 6144 combinations of day/hour values.
For each such combination you calculate Date1 + D days + H hours and make sure that
- the result doesn't fall into a weekend
- the result is <= Date2
The number of times this occurs out of the 6144 combinations corresponds to the number of hours between Date1 and Date2.
Very interesting use of a tally table. Although this leads me to think that using tally tables can produce lots of hard to understand code - unless you comment it well - as we all do.:)
May 8, 2008 at 6:26 am
If your Tally table is big enough, you can do without the days & hours combination and go for hours directly.
SELECTCOUNT(*)
FROMmaster..spt_values AS h
WHEREh.Type = 'p'
AND h.Number < DATEDIFF(HOUR, @Date1, @Date2)
AND DATENAME(WEEKDAY, DATEADD(HOUR, h.Number, @Date1)) NOT IN ('Saturday', 'Sunday')
N 56°04'39.16"
E 12°55'05.25"
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply