473,554 Members | 4,762 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Calculating Opentime between several records.Using SQL Server 2000.

2 New Member
Using SQL Server 2000. Basically, I am trying to calculate each minute of an employee scheduled time during their work shift. In the table below shows only the times that the shift begin (start_minute), ended (sched_endtime) and times (dtl_start_min, dtl_length, dtl_end_min) the employee went on Break, lunch, etc... What the table does not show is the times that the employee was not on any breaks, lunch etc.. that is called OPENTIME. I am trying to retrieve the OPENTIMES that the employee was available. Below is a sample of the data that I am trying to retrieve. Keep in mind this table contains millions of records.


DATE EMP_ID START_MINUTE SCHED_ENDTIME DTL_EXCPT_DESC DTL_START_MIN DTL_LENGTH DTL_END_MIN
6/19/2008 19261086 10:00:00 19:00:00 Break 12:00:00 0:15:00 12:15:00
6/19/2008 19261086 10:00:00 19:00:00 Lunch 15:00:00 1:00:00 16:00:00
6/19/2008 19261086 10:00:00 19:00:00 Break 17:15:00 0:15:00 17:30:00
6/19/2008 19261087 10:00:00 19:00:00 Break 12:00:00 0:15:00 12:15:00
6/19/2008 19261087 10:00:00 19:00:00 Lunch 15:00:00 1:00:00 16:00:00
6/19/2008 19261087 10:00:00 19:00:00 Break 17:15:00 0:15:00 17:30:00
6/19/2008 19261090 10:00:00 19:00:00 Break 12:00:00 0:15:00 12:15:00
6/19/2008 19261090 10:00:00 19:00:00 Lunch 15:00:00 1:00:00 16:00:00
6/19/2008 19261090 10:00:00 19:00:00 Break 17:15:00 0:15:00 17:30:00
6/19/2008 19261091 8:00:00 17:00:00 Break 10:15:00 0:15:00 10:30:00
6/19/2008 19261091 8:00:00 17:00:00 Lunch 11:30:00 1:00:00 12:30:00
6/19/2008 19261091 8:00:00 17:00:00 Break 15:45:00 0:15:00 16:00:00
6/19/2008 19261093 9:00:00 18:00:00 VP 9:00:00 3:30:00 12:30:00
6/19/2008 19261093 9:00:00 18:00:00 Lunch 12:30:00 1:00:00 13:30:00
6/19/2008 19261093 9:00:00 18:00:00 VP 13:30:00 4:30:00 18:00:00


For instance Emp #19261086 clocked in at 10:00:00 (start_min) and went on his first break at 12:00:00 (dtl_start_min) - this break lasted 0:15:00, therefore the 1st OPENTIME is 10:00:00 (start_time) and 2:00:00 hours (LENGTH (HR, MI)). Meaning that the employee worked 2 hours before his first break and the next item shows BREAK from 12:00:00 (dtl_start_min - start_time) and the length of the BREAK 0:15:00. The next record shows OPENTIME 2 from 12:15:00 with a 2:45:00 (LENGTH (2HR, 45MI) OPENTIME). Bascially, I am adding the DTL_START_MIN + DTL_LENGTH = next record START_TIME, but must calculate the previous records DTL_START_MIN + DTL_LENGTH to get the OPENTIME.


Employee 19261086
EVENT START_TIME LENGTH (HR, MI)
Open 10:00:00 2:00:00
Break 12:00:00 0:15:00
Open 12:15:00 2:45:00
Lunch 15:00:00 1:00:00
Open 16:00:00 1:15:00
Break 17:15:00 0:15:00
Open 17:30:00 19:00:00
Jun 27 '08 #1
1 1699
Delerna
1,134 Recognized Expert Top Contributor
Your comment to 'keep in mind that the table contains millions of records' suggests to me you know the subquery method for getting the next record for a particular entity. This is the only way I know of to do such a task.

Can you insert the 'Open' records into the table. If so you could run the above on a regular basis to insert them. That way you could arrange to only work with the records that have been added since the last time the insert routine ran. If you know what I mean
Jun 29 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

3
1914
by: Oren | last post by:
Hi, I have an Access application with linked tables via ODBC to MSSQL server 2000. Having a weird problem, probably something i've done while not being aware of (kinda newbie). the last 20 records (and growing)of a specific table are locked - cant change them - ("another user is editing these records ... ").
0
1720
by: Jason | last post by:
I need help designing a query (or two) in MS Access that will calculate a compounded rate of interest. This would be no problem using a simple FV function if the Interest Rate were constant. Unfortunately the Interest rate changes through time. Whenever a new interest rate period begins, I need to calculate the interest for the new period...
4
1533
by: Chris | last post by:
Any good routines or suggestions to assist me in re-ordering my records in my datagrid? i.e. I have a field in each record that is used for ordering (i.e. 1,2,3,4). I would like to implement a set of options to move the record up one, down one, move to top, move to bottom of this order. Thus changing that field values, and the others in the...
14
2398
by: imani_technology_spam | last post by:
I have the following table; CREATE TABLE ( IDENTITY (1, 1) NOT NULL , (7200) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NOT NULL , CONSTRAINT PRIMARY KEY CLUSTERED ( ,
5
3507
by: Wired Hosting News | last post by:
I tried to be breif and give a scenario so as not to be overlooked because it was soooo long. Let me give you real world. I am a manufacturer of goods and produce 11 items that are distributed to 1800 stores of a national home improvement chain store. Every week I electronicaly receive an excel spreadsheet "inventory report" with 19,800...
8
2791
by: rbg | last post by:
I did use query plans to find out more. ( Please see the thread BELOW) I have a question on this, if someone can help me with that it will be great. In my SQL query that selects data from table, I have a where clause which states : where PermitID like @WorkType order by WorkStart DESC
5
2790
by: Neil | last post by:
I'm running Access 2000 with a SQL 7 back end, using ODBC linked tables in an MDB file. The db is used by about 30 users on a LAN, and an additional 10 or so on a WAN. Recently, one of the WAN users complained of intermittently not being able to find certain customers in the customers table (contains around 40,000 records). There are...
6
5681
by: mazenblue | last post by:
Dear All i have the following problem and i am using microsoft access 2000 forms:- i am working on a system for a store, and i have a table named orders (order id , order_date , order_price, order_company_owner_name), and i have created a form for this table , what i am trying to do is that i want to add a field that calculate the sum of the...
0
1150
by: RubyRed | last post by:
Basically, I am trying to calculate each minute of an employee scheduled time during their work shift. In the table below shows only the times that the shift begin (start_minute), ended (sched_endtime) and times (dtl_start_min, dtl_length, dtl_end_min) the employee went on Break, lunch, etc... What the table does not show is the times that the...
0
7580
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7503
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7781
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7538
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6121
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5421
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
1
2003
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1113
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
817
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.