June 25, 2008 at 12:31 pm
I am trying to get the opentime between the employee exceptions during the day. For instance if employee 19261086 started work at 10:00:00 and toke a break at 12:00:00 for 15 mins, his opentime should be from 10:00:00 to 12:00:00 and from 12:15:00 to 15:00:00, because he toke another break at 15:00:00 for 1 hour. Basically I am trying to get all of the opentime (time the employee was not on break, lunch, etc) during the day. Starting with the time that employee started work until his shift ended.
DATE EMP_IDSTART_MINUTE SCHED_ENDTIME DTL_EXCPT_DESCDTL_START_MINDTL_LENGTHDTL_END_MIN
6/19/20081926108610:00:0019:00:00Break 12:00:000:15:0012:15:00
6/19/20081926108610:00:0019:00:00Lunch 15:00:001:00:0016:00:00
6/19/20081926108610:00:0019:00:00Break 17:15:000:15:0017:30:00
6/19/20081926108710:00:0019:00:00Break 12:00:000:15:0012:15:00
6/19/20081926108710:00:0019:00:00Lunch 15:00:001:00:0016:00:00
6/19/20081926108710:00:0019:00:00Break 17:15:000:15:0017:30:00
6/19/20081926109010:00:0019:00:00Break 12:00:000:15:0012:15:00
6/19/20081926109010:00:0019:00:00Lunch 15:00:001:00:0016:00:00
6/19/20081926109010:00:0019:00:00Break 17:15:000:15:0017:30:00
6/19/2008192610918:00:0017:00:00Break 10:15:000:15:0010:30:00
6/19/2008192610918:00:0017:00:00Lunch 11:30:001:00:0012:30:00
6/19/2008192610918:00:0017:00:00Break 15:45:000:15:0016:00:00
6/19/2008192610939:00:0018:00:00VP 9:00:003:30:0012:30:00
6/19/2008192610939:00:0018:00:00Lunch 12:30:001:00:0013:30:00
6/19/2008192610939:00:0018:00:00VP 13:30:004:30:0018:00:00
Final Result - This only shows results for 1 employee. I would like to information on all employees (there are millions or records). The calculation should start over when the date and emp_id change.
Employee 19261086
EVENTSTART_TIMELENGTH (HR, MI)
Open10:00:002:00:00
Break12:00:000:15:00
Open12:15:002:45:00
Lunch15:00:001:00:00
Open16:00:001:15:00
Break17:15:000:15:00
Open17:30:0019:00:00
June 25, 2008 at 1:34 pm
RubyRed (6/25/2008)
I am trying to get the opentime between the employee exceptions during the day. For instance if employee 19261086 started work at 10:00:00 and toke a break at 12:00:00 for 15 mins, his opentime should be from 10:00:00 to 12:00:00 and from 12:15:00 to 15:00:00, because he toke another break at 15:00:00 for 1 hour. Basically I am trying to get all of the opentime (time the employee was not on break, lunch, etc) during the day. Starting with the time that employee started work until his shift ended.DATE EMP_IDSTART_MINUTE SCHED_ENDTIME DTL_EXCPT_DESCDTL_START_MINDTL_LENGTHDTL_END_MIN
6/19/20081926108610:00:0019:00:00Break 12:00:000:15:0012:15:00
6/19/20081926108610:00:0019:00:00Lunch 15:00:001:00:0016:00:00
6/19/20081926108610:00:0019:00:00Break 17:15:000:15:0017:30:00
6/19/20081926108710:00:0019:00:00Break 12:00:000:15:0012:15:00
6/19/20081926108710:00:0019:00:00Lunch 15:00:001:00:0016:00:00
6/19/20081926108710:00:0019:00:00Break 17:15:000:15:0017:30:00
6/19/20081926109010:00:0019:00:00Break 12:00:000:15:0012:15:00
6/19/20081926109010:00:0019:00:00Lunch 15:00:001:00:0016:00:00
6/19/20081926109010:00:0019:00:00Break 17:15:000:15:0017:30:00
6/19/2008192610918:00:0017:00:00Break 10:15:000:15:0010:30:00
6/19/2008192610918:00:0017:00:00Lunch 11:30:001:00:0012:30:00
6/19/2008192610918:00:0017:00:00Break 15:45:000:15:0016:00:00
6/19/2008192610939:00:0018:00:00VP 9:00:003:30:0012:30:00
6/19/2008192610939:00:0018:00:00Lunch 12:30:001:00:0013:30:00
6/19/2008192610939:00:0018:00:00VP 13:30:004:30:0018:00:00
Final Result - This only shows results for 1 employee. I would like to information on all employees (there are millions or records). The calculation should start over when the date and emp_id change.
Employee 19261086
EVENTSTART_TIMELENGTH (HR, MI)
Open10:00:002:00:00
Break12:00:000:15:00
Open12:15:002:45:00
Lunch15:00:001:00:00
Open16:00:001:15:00
Break17:15:000:15:00
Open17:30:0019:00:00
how about:
DECLARE @T TABLE ( dtedatetime,
emp_idint,
start_minutedatetime,
sched_endtimedatetime,
dtl_excpt_desc varchar(20),
dtl_start_min datetime,
dtl_length datetime,
dtl_end_mindatetime
)
INSERT INTO @T (dte, emp_id,start_minute,sched_endtime,dtl_excpt_desc,dtl_start_min,dtl_length,dtl_end_min)
SELECT '6/19/2008', 19261086, '10:00:00', '19:00:00', 'Break', '12:00:00', '0:15:00', '12:15:00' UNION ALL
SELECT '6/19/2008', 19261086, '10:00:00', '19:00:00', 'Lunch', '15:00:00', '1:00:00', '16:00:00' UNION ALL
SELECT '6/19/2008', 19261086, '10:00:00', '19:00:00', 'Break', '17:15:00', '0:15:00', '17:30:00' UNION ALL
SELECT '6/19/2008', 19261087, '10:00:00', '19:00:00', 'Break', '12:00:00', '0:15:00', '12:15:00' UNION ALL
SELECT '6/19/2008', 19261087, '10:00:00', '19:00:00', 'Lunch', '15:00:00', '1:00:00', '16:00:00' UNION ALL
SELECT '6/19/2008', 19261087, '10:00:00', '19:00:00', 'Break', '17:15:00', '0:15:00', '17:30:00' UNION ALL
SELECT '6/19/2008', 19261090, '10:00:00', '19:00:00', 'Break', '12:00:00', '0:15:00', '12:15:00' UNION ALL
SELECT '6/19/2008', 19261090, '10:00:00', '19:00:00', 'Lunch', '15:00:00', '1:00:00', '16:00:00' UNION ALL
SELECT '6/19/2008', 19261090, '10:00:00', '19:00:00', 'Break', '17:15:00', '0:15:00', '17:30:00' UNION ALL
SELECT '6/19/2008', 19261091, '8:00:00', '17:00:00', 'Break', '10:15:00', '0:15:00', '10:30:00' UNION ALL
SELECT '6/19/2008', 19261091, '8:00:00', '17:00:00', 'Lunch', '11:30:00', '1:00:00', '12:30:00' UNION ALL
SELECT '6/19/2008', 19261091, '8:00:00', '17:00:00', 'Break', '15:45:00', '0:15:00', '16:00:00' UNION ALL
SELECT '6/19/2008', 19261093, '9:00:00', '18:00:00', 'VP', '9:00:00', '3:30:00', '12:30:00' UNION ALL
SELECT '6/19/2008', 19261093, '9:00:00', '18:00:00', 'Lunch', '12:30:00', '1:00:00', '13:30:00' UNION ALL
SELECT '6/19/2008', 19261093, '9:00:00', '18:00:00', 'VP', '13:30:00', '4:30:00', '18:00:00'
---=======
--- Query
---=======
;WITH cte
AS
(
SELECT dte, emp_id, MIN([event]) ev, start_time , ROW_NUMBER() OVER ( PARTITION BY dte, emp_id ORDER BY start_time) n
FROM
(
SELECT dte, emp_id, start_minute AS start_time, 'Open' AS [event] FROM @T
UNION ALL
SELECT dte, emp_id, dtl_start_min, dtl_excpt_desc FROM @T
UNION ALL
SELECT dte, emp_id, dtl_end_min, 'Open' FROM @T
UNION ALL
SELECT dte, emp_id, sched_endtime, 'Close' FROM @T
)d
GROUP BY dte, emp_id, start_time
)
SELECT c1.dte, c1.emp_id, c1.ev,
CONVERT(char(8), c1.start_time, 108) start_time ,
CONVERT(char(8),c2.start_time - c1.start_time,108) AS time_elapsed
FROM cte c1 INNER JOIN cte c2
ON c1.dte = c2.dte
AND c1.emp_id = c2.emp_id
AND c2.n = c1.n + 1
ORDER BY c1.dte, c1.emp_id, c1.start_time
* Noel
June 25, 2008 at 2:15 pm
This did not work. Basically I am trying to calculate each minute of an employee time during their work shift. In the table above 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
EVENTSTART_TIMELENGTH (HR, MI)
Open10:00:00 2:00:00
Break12:00:00 0:15:00
Open12:15:00 2:45:00
Lunch15:00:00 1:00:00
Open16:00:00 1:15:00
Break17:15:00 0:15:00
Open17:30:00 19:00:00
June 25, 2008 at 3:54 pm
RubyRed (6/25/2008)
This did not work. Basically I am trying to calculate each minute of an employee time during their work shift. In the table above 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
EVENTSTART_TIMELENGTH (HR, MI)
Open10:00:00 2:00:00
Break12:00:00 0:15:00
Open12:15:00 2:45:00
Lunch15:00:00 1:00:00
Open16:00:00 1:15:00
Break17:15:00 0:15:00
Open17:30:00 19:00:00
Erm... correct me if I am wrong but:
DECLARE @T TABLE ( dtedatetime,
emp_idint,
start_minutedatetime,
sched_endtimedatetime,
dtl_excpt_desc varchar(20),
dtl_start_min datetime,
dtl_length datetime,
dtl_end_mindatetime
)
INSERT INTO @T (dte, emp_id,start_minute,sched_endtime,dtl_excpt_desc,dtl_start_min,dtl_length,dtl_end_min)
SELECT '6/19/2008', 19261086, '10:00:00', '19:00:00', 'Break', '12:00:00', '0:15:00', '12:15:00' UNION ALL
SELECT '6/19/2008', 19261086, '10:00:00', '19:00:00', 'Lunch', '15:00:00', '1:00:00', '16:00:00' UNION ALL
SELECT '6/19/2008', 19261086, '10:00:00', '19:00:00', 'Break', '17:15:00', '0:15:00', '17:30:00' UNION ALL
SELECT '6/19/2008', 19261087, '10:00:00', '19:00:00', 'Break', '12:00:00', '0:15:00', '12:15:00' UNION ALL
SELECT '6/19/2008', 19261087, '10:00:00', '19:00:00', 'Lunch', '15:00:00', '1:00:00', '16:00:00' UNION ALL
SELECT '6/19/2008', 19261087, '10:00:00', '19:00:00', 'Break', '17:15:00', '0:15:00', '17:30:00' UNION ALL
SELECT '6/19/2008', 19261090, '10:00:00', '19:00:00', 'Break', '12:00:00', '0:15:00', '12:15:00' UNION ALL
SELECT '6/19/2008', 19261090, '10:00:00', '19:00:00', 'Lunch', '15:00:00', '1:00:00', '16:00:00' UNION ALL
SELECT '6/19/2008', 19261090, '10:00:00', '19:00:00', 'Break', '17:15:00', '0:15:00', '17:30:00' UNION ALL
SELECT '6/19/2008', 19261091, '8:00:00', '17:00:00', 'Break', '10:15:00', '0:15:00', '10:30:00' UNION ALL
SELECT '6/19/2008', 19261091, '8:00:00', '17:00:00', 'Lunch', '11:30:00', '1:00:00', '12:30:00' UNION ALL
SELECT '6/19/2008', 19261091, '8:00:00', '17:00:00', 'Break', '15:45:00', '0:15:00', '16:00:00' UNION ALL
SELECT '6/19/2008', 19261093, '9:00:00', '18:00:00', 'VP', '9:00:00', '3:30:00', '12:30:00' UNION ALL
SELECT '6/19/2008', 19261093, '9:00:00', '18:00:00', 'Lunch', '12:30:00', '1:00:00', '13:30:00' UNION ALL
SELECT '6/19/2008', 19261093, '9:00:00', '18:00:00', 'VP', '13:30:00', '4:30:00', '18:00:00'
;with cte
AS
(
SELECT dte, emp_id, MIN([event]) ev, start_time , ROW_NUMBER() OVER ( PARTITION BY dte, emp_id ORDER BY start_time) n
FROM
(
SELECT 1 as t, dte, emp_id, start_minute AS start_time, 'Open' AS [event] FROM @T
UNION ALL
SELECT 2, dte, emp_id, dtl_start_min, dtl_excpt_desc FROM @T
UNION ALL
SELECT 3, dte, emp_id, dtl_end_min, 'Open' FROM @T
UNION ALL
SELECT 4, dte, emp_id, sched_endtime, 'Close' FROM @T
)d
WHERE emp_id = 19261086
GROUP BY dte, emp_id, start_time
)
SELECT c1.dte, c1.emp_id, c1.ev,
CONVERT(char(8), c1.start_time, 108) start_time ,
CONVERT(char(8),c2.start_time - c1.start_time,108) AS time_elapsed
FROM cte c1 INNER JOIN cte c2
ON c1.dte = c2.dte
AND c1.emp_id = c2.emp_id
AND c2.n = c1.n + 1
ORDER BY c1.dte, c1.emp_id, c1.start_time
Returns:
dte emp_id ev start_time time_elapsed
2008-06-19 00:00:00.00019261086Open10:00:0002:00:00
2008-06-19 00:00:00.00019261086Break 12:00:0000:15:00
2008-06-19 00:00:00.00019261086Open12:15:0002:45:00
2008-06-19 00:00:00.00019261086Lunch15:00:0001:00:00
2008-06-19 00:00:00.00019261086Open16:00:0001:15:00
2008-06-19 00:00:00.00019261086Break 17:15:0000:15:00
2008-06-19 00:00:00.00019261086Open17:30:0001:30:00
which I think "matches" what you posted at the end. Am I missing something ?
* Noel
June 26, 2008 at 8:24 am
I am using SQL Server 2000 and it does not like the ROW_NUMBER syntax. Is there something else that I can use?
June 26, 2008 at 9:23 am
I am using SQL Server 2000. This query appears to work in SQL 2005 - what can I do to convert this query to SQL 2000?
Thanks for your help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply