Filter out Weekend and Evening Hours -- tough SQL question.

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)

Filter out Weekend and Evening Hours -- tough SQL question.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-31 : 22:56:18

Jim writes "***

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.

Help?

Thank you in advance.

Jim Meyer
EDS, Inc."

nr
SQLTeam MVY

12543 Posts

Posted - 2002-04-01 : 00:27:43

haven't read this carefully but I guess you just want to include working days in the time calculation.

you have a start and end time so

endtime - starttime gives the total time.

If it is in the same day then simple.
If it's multiple days then bit more of a problem.

probably easiest to create a temp table told the times to avoid

declare @mindate datetime, @maxdate datetime
select @mindate = min(convert(varchar(8),starttime, 112) from tbl
select @maxdate = min(convert(varchar(8),endtime, 112) from tbl

create table #dates (startdate datetime, enddate datetime)
while @mindate <= @enddate
begin
if datepart(dw,@mindate) in (6,7)
insert #dates select @mindate, dateadd(dd,1,@mindate)
else
insert #dates select dateadd(hh,9,@mindate), dateadd(hh,18,@mindate)

select @mindate = dateadd(dd,1,@mindate)
end

now we just total the time

select (tbl.endtime - tbl.starttime)
- (select sum(#dates.endtime - #dates.starttime) from #dates where tbl.starttime < #dates.starttime and tbl.endtime > #dates.endtime)
- (select sum(tbl.endtime - #dates.starttime) from #dates where tbl.starttime > #dates.starttime and tbl.endtime > #dates.endtime and tbl.starttime < #dates.endtime)
- (select sum(#dates.endtime - tbl.starttime) from #dates where tbl.starttime < #dates.starttime and tbl.endtime < #dates.endtime and tbl.endtime > #dates.starttime)
from tbl

that's probably not quite correct but you'll get thye idea.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

dsdeming

479 Posts

Posted - 2002-04-01 : 08:17:53

If you're ignoring weekend and evening minutes, I would filter out weekends altogether using the DATEPART function. Then you could add the minutes the process was active and the first and last days of the period to the product of the number of days in between times the allowable minutes per day.

Assuming your weekend is Saturday and Sunday and your business hours are 9am to 5pm and no one modifies your data outside of those hours, if the process starts at noon on Friday and runs through 3pm the following Wednesday:

SELECT DATEDIFF( mm, CAST( CONVERT( char( 8 ), @Start, 112 ) + ' 17:00:00' AS datetime ), @Start ) + DATEDIFF( mm, @End, CAST( CONVERT( char( 8 ), @End, 112 ) + ' 17:00:00' AS datetime ))

should get you the minutes in the first and last days. To get the minutes from the intervening days you have 2 choices. Either cursor through the remaining dates, incrementing a counter for each valid workday, and multiply the final result by 540 ( 60 minutes x 9 hours ); or add your weekend dates to a holiday table and do something like so they can be filtered out in a set-based operation. I like the second alternative better because I'm sure you need to filter out holidays as well.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-01 : 08:21:40

Does this help:

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

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-04-02 : 05:43:19

sounds like you need upto-date stats on 'work-in-progress'...so why not consider the following....

1. add a column "lastupdated"
2. create a job which will work on all records and apply the appropriate calculation based on the 'assigned time' elapsed since the lastupdated column was changed.
3. schedule job to run everyday @ end of day...this should simplify the problem/solution in the cross-day boundaries can be minimised...only issue you have then is working through historical data.

couple of points to ponder....
1. public-holidays....do you consider them non-working days, similiar to weekends...if so, it's not so easy to automatically cater for same.
2. overtime...does it not apply in your operation....your business rule presumes all work done in regular time....

how would you cater for the following in this system....?
hardware item is assigned to "personA" @ 5pm,
that person works for 3 hours untl 8pm on the item....

yet....by this business rule....no elapsed time (which may be equated to work) was spent on the item...because at 8am the next day, the item was passed onwards to the next stage....

if this is a problem for you, then the business rule and supporting data may need to be adapted.

also....will you have any need to examine the detail of what makes up ACTIVITY_NUMBER_MINUTES...?

if so you may need a sub-table to indicate the start and end of each active period....something like...

recordid, itemid, phasestart, phaseend
record1, item1, monday 11am, monday 5pm
record2, item1, tuesday 8am, tuesday 5pm
record3, item1, wednesday 8am, wednesday 4pm

total active time for item 1 thus is 6+9+8=23 hours

record1 when created would have 5pm as it's default phaseend
and at 7.59am on tuesday record2 would be created as shown (by a scheduled job)
and at 7.59am on tuesday record2 would be created with 5pm (by a scheduled job) as it's default phaseend....but at 4pm when activity ceases, the phaseend would be 'corrected' from a projected value to the actual value...

breaking it down into smaller parts, may provide a simpler solution than dealing with it in 1 go.....the business requirement/rule is more complicated than it seems at face value.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources