Calculating order works times - SQL Server Forums

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

Transact-SQL (2000)

Calculating order works times

Author

Topic

jeusdi
Starting Member

27 Posts

Posted - 2007-11-26 : 09:38:39

I've created an application that saves (into database) orders movements of the workers (begin, preparation, production, cleaning and end states) as follow -->

I need to knowm, How I can calculate the difference between two states of each order.
order worker date(timestamp) state_id stae_description
1 0 mari 2007-06-04 11:07:53.400 1 Begin 0
1 0 mari 2007-06-04 11:07:53.433 2 Preparation 0
1 0 mari 2007-06-04 13:20:29.487 3 Production 0
1 0 mari 2007-06-04 13:31:00.160 4 Cleaning 0
1 0 mari 2007-06-04 13:40:04.703 5 End 0
1 0 ivet 2007-06-04 13:46:41.983 1 Begin 2
1 0 ivet 2007-06-04 13:46:42.013 2 Preparation 2
1 0 ivet 2007-06-04 13:50:54.390 3 Troduction 2
1 0 ivet 2007-06-04 13:55:54.607 4 Cleanning 2
1 0 ivet 2007-06-04 13:56:05.483 5 End 2

For example -->
I need to get a table (view or select result) as follow-->

ORDER_WORK WORKER PREPARATION* PRODUCTION* CLEANING*
1 mari 1:00:00 1:00:00 1:00:00
1 ivet 1:00:00 1:00:00 1:00:00

* PREPARATION is equal at prduction timestamp less preparation timestamp.
* PRODUCTION is equal at clenning timestamp less production timestamp
* CLEANNING is equal at ending timestamp less clenning timestamp

Note each state is each bellow the other so, calculating the time between each state corresponds to the timestamp of one row less the previous one.

Can you help please, your help will be very gratefull for me.
Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-26 : 09:53:07

[code]DECLARE @sample TABLE
(
order_id int,
nothing int,
worker varchar(4),
[date] datetime,
state_id int,
state_description varchar(15)
)
INSERT INTO @sample
SELECT 1, 0, 'mari', '2007-06-04 11:07:53.400', 1, 'BEGIN 0' UNION ALL
SELECT 1, 0, 'mari', '2007-06-04 11:07:53.433', 2, 'Preparation 0' UNION ALL
SELECT 1, 0, 'mari', '2007-06-04 13:20:29.487', 3, 'Production 0' UNION ALL
SELECT 1, 0, 'mari', '2007-06-04 13:31:00.160', 4, 'Cleaning 0' UNION ALL
SELECT 1, 0, 'mari', '2007-06-04 13:40:04.703', 5, 'END 0' UNION ALL
SELECT 1, 0, 'ivet', '2007-06-04 13:46:41.983', 1, 'BEGIN 2' UNION ALL
SELECT 1, 0, 'ivet', '2007-06-04 13:46:42.013', 2, 'Preparation 2' UNION ALL
SELECT 1, 0, 'ivet', '2007-06-04 13:50:54.390', 3, 'Troduction 2' UNION ALL
SELECT 1, 0, 'ivet', '2007-06-04 13:55:54.607', 4, 'Cleanning 2' UNION ALL
SELECT 1, 0, 'ivet', '2007-06-04 13:56:05.483', 5, 'END 2'

SELECT order_id, worker,
[preparation] = CONVERT(varchar(8), DATEADD(second, MAX(CASE WHEN state_id = 2 THEN [time] ELSE 0 END), 0), 108),
[production] = CONVERT(varchar(8), DATEADD(second, MAX(CASE WHEN state_id = 3 THEN [time] ELSE 0 END), 0), 108),
[cleaning] = CONVERT(varchar(8), DATEADD(second, MAX(CASE WHEN state_id = 4 THEN [time] ELSE 0 END), 0), 108)
FROM
(
SELECT s.order_id, s.worker, s.state_id, s.state_description,
[time] = DATEDIFF(second, s.[date], e.[date])
FROM @sample s INNER JOIN @sample e
ON s.order_id = e.order_id
AND s.worker = e.worker
AND s.state_id = e.state_id - 1
WHERE s.state_id IN (2,3,4)
) d
GROUP BY order_id, worker
/*

order_id worker preparation production cleaning
----------- ------ ----------- ---------- --------
1 ivet 00:04:12 00:05:00 00:00:11
1 mari 02:12:36 00:10:31 00:09:04

(2 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

jeusdi
Starting Member

27 Posts

Posted - 2007-11-27 : 04:44:26

Thank you a lot for your quick answer.

Now, I need it a little more complicated, as follows:

For example, It can appear the different states but several of them doubled. Then, We have to calculate the same result taking into account only the first of each repeated state.

For example, We could have a situation like this:

1 mari BEGIN
1 mari PREPARATION
1 mari PREPARATION
1 mari PREPARATION
1 mari PRODUCTION
1 mari PRODUCTION
1 mari CLEANING
1 mari CLEANING
1 mari END

To get the results, we have to less the red coloured states.

We can also have another order state. This state is PAUSE and it goes with another state named END-PAUSE. And these ones can't be repeated as the states above mentioned.

1 mari BEGIN
1 mari PREPARATION
1 mari PREPARATION
1 mari PAUSE
1 mari END-PAUSE
1 mari PREPARATION
1 mari PRODUCTION
1 mari PRODUCTION
1 mari PAUSE
1 mari END-PAUSE
1 mari PRODUCTION
1 mari PRODUCTION
1 mari CLEANING
1 mari CLEANING
1 mari PAUSE
1 mari END-PAUSE
1 mari END

In order to explain it to you more clearly, END-PAUSE could be treated as follows (if it's usefull for you) -->

1 mari BEGIN
1 mari PREPARATION
1 mari PREPARATION
1 mari PAUSE
1 mari PREPARATION
1 mari PREPARATION
1 mari PRODUCTION
1 mari PRODUCTION
1 mari PAUSE
1 mari PRODUCTION
1 mari PRODUCTION
1 mari PRODUCTION
1 mari CLEANING
1 mari CLEANING
1 mari PAUSE
1 mari CLEANING
1 mari END

after that the transformation would be to delete the repeated stated states-->

1 mari BEGIN
1 mari PREPARATION
1 mari PAUSE
1 mari PREPARATION
1 mari PRODUCTION
1 mari PAUSE
1 mari PRODUCTION
1 mari CLEANING
1 mari PAUSE
1 mari CLEANING
1 mari END

Thanks for your attention.
Regards.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-27 : 04:49:43

can you post the sample data as insert . .. statement ? I am bit lazy now to type in all those and also the sample data is not complete, need the timestamp etc.

Please also post the expected result

KH
[spoiler]Time is always against us[/spoiler]

jeusdi
Starting Member

27 Posts

Posted - 2007-11-27 : 11:26:24

For example-->

1) 305 silv 2007-06-29 15:13:30.560 1 BEGIN 2
2) 305 silv 2007-06-29 15:13:30.590 2 PREPARATION 2
3) 305 silv 2007-06-29 15:14:50.521 2 PREPARATION 2 <---it could be obviated*
4) 305 silv 2007-06-29 15:34:12.876 2 PREPARATION 2 <---it could be obviated*
5) 305 silv 2007-06-29 15:45:26.123 3 PRODUCTION 2
6) 305 silv 2007-06-29 16:34:56.145 3 PRODUCTION 2 <---it could be obviated*
7) 305 silv 2007-06-29 16:35:12.967 10 PAUSE 2
8) 305 silv 2007-06-29 16:36:32.651 11 END-PAUSE 2
9) 305 silv 2007-06-29 16:39:45.765 3 PRODUCTION 2 <---it could be obviated**
10) 305 silv 2007-06-29 17:46:25.077 4 CLEANING 2
11) 305 silv 2007-06-29 17:47:11.451 4 CLEANING 2 <---it could be obviated*
12) 305 silv 2007-06-29 17:48:05.763 10 PAUSE 2
13) 305 silv 2007-06-29 18:15:45.233 11 END-PAUSE 2
14) 305 silv 2007-06-29 18:23:39.653 5 END 2

* In order to calculate preparation time, we need only less the fifth row with second row.
* In order to calculate production time, we need only less the seventh with the fifth one adding tenth less the eighth one.
* CLEANING TIME = (12 row less 10 row) + (14 row less 13 row).

** After the END-PAUSE we have to obviate the next state, in this case PRODUCTION, because the END-PAUSE state acts as if it was a PRODUCTION state as well. So, as I explained you before, the repeated states can be obviated.

PRODUCTION
PRODUCTION
PAUSE
END-PAUSE
PRODUCTION
CLEANING

can be transformed as

PRODUCTION
PRODUCTION
PAUSE
PRODUCTION <----
PRODUCTION
CLEANING

Applying the *) one -->

PRODUCTION
PAUSE
CLEANING

I'm writing you down the SQL code corresponding to previous sample data -->

INSERT INTO @sample
SELECT 305, 0, 'mari', '2007-29-06 15:13:30.560', 1, 'BEGIN 0' UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 15:13:30.590', 2, 'PREPARATION 0' UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 15:14:50.521', 2, 'PREPARATION 0' UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 15:45:26.123', 3, 'PRODUCTION 2' UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 16:34:56.145', 3, 'PRODUCTION 2' UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 16:35:12.967', 10, 'PAUSE 2' UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 16:36:32.651', 11, 'END-PAUSE 2' UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 16:39:45.765', 3, 'PRODUCTION' UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 17:46:25.077', 4, 'CLEANING 2' UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 17:47:11.451', 4, 'CLEANING 2' UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 17:48:05.763', 10, 'PAUSE 2' UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 18:15:45.233', 11, 'END-PAUSE 2' UNION ALL
SELECT 305, 0, 'mari', '2007-29-06 18:23:39.653', 5, 'END 2'

Thanks for all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources