Total Worked Hours (minus weekends & evenings)

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

SQL Server 2000 Forums

SQL Server Development (2000)

Total Worked Hours (minus weekends & evenings)

Author

Topic

yahazim
Starting Member

13 Posts

Posted - 2002-03-28 : 14:06:12

SETUP:

I have created a database that keeps track of the Status of a piece of hardware that is going through a Quality Assurance process.

There are multiple Statuses, and each time the status changes a timestamp is taken both for the ending status (END_ACTIVITY_DATETIME), and for the new status(START_ACTIVITY_DATETIME). I have a third field in the table (tblSTATUS_HISTORY) that is a calculated column to show me the number of minutes the hardware was in that particular status. (ACTIVITY_NUMBER_MINUTES: (datediff(minute,START_ACTIVITY_DATETIME,END_ACTIVITY_DATETIME))

PROBLEM:

Each person who changes the Status needs to be responsible for the amount of time the hardware sat in that status. Since Weekend and Evening minutes are not considered "Worked Time", how do I calculate the ACTIVITY_NUMBER_MINUTES with this business rule taken into consideration? How do I do this in SQL? I know how to do it in Visual Basic, but since "Yukon" is not here yet... well... a puzzle for those SQL Jedi out there for an aspiring SQL Padawa.

REQUIREMENTS TO SOLUTION:

1) I want to calculate this on the server, not on the front end. A stored procedure or even trigger would be fine.

IDEAS:

I am thinking I need to add a reference table to tblSTATUS_HISTORY that can reference the time span between END_ACTIVITY_DATETIME and START_ACTIVITY_DATETIME, and give a certain number to subtract from the total. NOT SURE. Also, now I am understanding that the client wants to have the option of adding weekend or evening hours.

Help? Ideas?

Thank you in advance.

Jim Meyer
EDS, Inc.

Jay99

468 Posts

Posted - 2002-03-28 : 14:40:14

quote:

I am thinking I need to add a reference table to tblSTATUS_HISTORY that can reference the time span between END_ACTIVITY_DATETIME and START_ACTIVITY_DATETIME, and give a certain number to subtract from the total. NOT SURE. Also, now I am understanding that the client wants to have the option of adding weekend or evening hours.

This will work, except you don't need a table, you need a stored proc (or if in sql 2k a function).


create proc usp_CalcEveWeekend
    @start datetime,
    @end datetime,
    @clientadded int,
    @TotalEveWeekendHours int OUT
as
...

Now you will probably need a table to store holidays.

Jay
<O>

chadmat
The Chadinator

1974 Posts

Posted - 2002-03-28 : 14:57:15

This probably isn't the best solution, but it is the first thing I thought of, so here it goes. This assumes that an activity will never take longer than a week if it does, then this won't work.

It also doesn't take into account "off" hours. You could change how the weekend minutes are calculated, but I just did 2 days x 24 hours x 60 mins.


SET DATEFIRST 1

create table times (starttime datetime, endtime datetime, minutes int)

insert into times values ('3/27/2002 9:00AM', '3/27/2002 2:32PM',null)
insert into times values ('3/22/2002 4:00PM', '3/25/2002 9:15AM',null)

update times
set minutes = case when datepart(dw,starttime)>datepart(dw,endtime) then 
datediff(mi, starttime, endtime) - (2*24*60)
else
datediff(mi, starttime, endtime) 
end

select * from times

-Chad

yahazim
Starting Member

13 Posts

Posted - 2002-03-28 : 15:20:42

Ahhh.... I wish each Status wouldn't last more than a week. However it does! Sometimes a hardware could be sitting in status for more than 1000 hours, accumulating time!

And I get the idea behind the Stored Procedure. Makes complete sense. But how can a SQL script take two dates, and check to see if any datetimes in between fall between a given range, i.e. evening, weekend, or holiday hours? Would I take the first date, assign that value to a variable, increment the variable by, let's say, an hour, check the variables new value against a table to see if it's an evening, weekend, or holiday hour?

I can see where datepart can be used:

IF datepart(d, variable) = 'Saturday' OR 'Sunday' THEN
....

ELSE IF datepart (hh, variable) = 'list the hours here) THEN
....

Jim

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-28 : 15:30:21

I had a cheat for finding the number of business days between two dates:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=13921

What you could do is:

-Find the number of days between the two dates;
-Subtract 2 days for each weekend crossed;
-Multiply the number of days by 1440 to get the number of minutes (for a whole day), or use the number of "working" minutes for a whole day (480 for an 8-hour day)

The only problem after that is to find the actual number of minutes used on the start and end dates. So you'd have to find DateDiff(minute, (date at 12:00AM), START_DATE) and so on. You'd actually use the earliest start time if it's not midnight.

Hang on, revised idea, try THIS:

-Find the number of MINUTES between the two dates;
-Find the number of WEEKENDS between them, multiply by 2880, subtract it from the above;
-Find the number of DAYS between them, multiply this by the number of "off-hour" minutes per day (if 8 hours of work, then 16 hours of non-work=960 minutes), subtract this from the above

The only problem is counting weekend days as full days while counting normal days as partial. If I think of the formula for that I'll add it here; if you could fill us in on the start and end times for each day (before start/after end don't count) that would be appreciated.

Edited by - robvolk on 03/28/2002 15:46:17

ToddV
Posting Yak Master

218 Posts

Posted - 2002-03-28 : 16:22:09

This pretty much accomplishes the deal:


Declare @BeginDate Datetime,
	@EndDate DateTime,
	@BeginDay char(5),
	@EndDay Char(5),
	@ExcludeWeekend Bit

SET @BeginDate = '20020322 10:00'
SET @EndDate = '20020328 12:00'
SET @BeginDay = '09:00'
SET @EndDay = '17:00'
SET @ExcludeWeekend = 1

DECLARE @BeginDateEndDay  Datetime,
	@BeginDateBeginDay  Datetime,
	@EndDateBeginDay Datetime,
	@WorkDayLength INT,
	@Minutes INT

SET @WorkDayLength = DATEDIFF(mi,'20020101 ' + @BeginDay,'20020101 ' + @EndDay)
SET @BeginDateEndDay = CONVERT(Varchar,@BeginDate,101) + ' ' + @EndDay
SET @BeginDateBeginDay = CONVERT(Varchar,@BeginDate,101) + ' ' + @BeginDay
SET @EndDateBeginDay = CONVERT(VARCHAR,@EndDate,101)+ ' ' + @BeginDay

SELECT @Minutes = DATEDIFF(mi,@BeginDate,@BeginDateEndDay)

IF @ExcludeWeekend = 0
	SELECT  @Minutes = @Minutes + (COUNT(*) * @WorkDayLength)
	FROM Tally
	WHERE DateAdd(dd,Tally,@BeginDateBeginDay) < @EndDateBeginDay
ELSE 
	SELECT  @Minutes = @Minutes + (COUNT(*) * @WorkDayLength)
	FROM Tally
	WHERE DateAdd(dd,Tally,@BeginDateBeginDay) < @EndDateBeginDay AND
		DATEPART(dw,DateAdd(dd,Tally,@BeginDateBeginDay)) IN (2,3,4,5,6)


SELECT @Minutes = @Minutes + DATEDIFF(mi,@EndDateBeginDay,@EndDate)

SELECT @Minutes/60,@Minutes

yahazim
Starting Member

13 Posts

Posted - 2002-03-28 : 17:26:22

WOW. Todd's script basically accomplishes everything and shows the logic. Thanks Todd.

---------------------------

Let's say it's a normal workday.

START_DAY: 8:00am
END_DAY: 5:00pm

However, a higher level of sophisitication would allow us to assign parameters to this value. Maybe we would have a table like this:

USER_ID -- id of the user (from the user table, tblUSER)
DAY_ID -- the name of the day (from a new table, tblDAY)
START_DAY_TIME -- the time the workday starts
END_DAY_TIME -- the time the workday ends

Jim

Edited by - yahazim on 03/28/2002 17:27:52

Dee2x
Starting Member

1 Post

Posted - 2008-12-23 : 01:04:04

In Todd's script, there's a table called 'Tally'. Can someone help explain how it works?

IF @ExcludeWeekend = 0
SELECT @Minutes = @Minutes + (COUNT(*) * @WorkDayLength)
FROM Tally
WHERE DateAdd(dd,Tally,@BeginDateBeginDay) < @EndDateBeginDay
ELSE
SELECT @Minutes = @Minutes + (COUNT(*) * @WorkDayLength)
FROM Tally
WHERE DateAdd(dd,Tally,@BeginDateBeginDay) < @EndDateBeginDay AND
DATEPART(dw,DateAdd(dd,Tally,@BeginDateBeginDay)) IN (2,3,4,5,6)

Thanks a lot!

How you do anything is how you do everything.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources