October 8, 2009 at 2:29 pm
Hi,
I want to sum the times for each machine state for each shift.
state0-machine stooped
state1-machine with alarm
state2-machine running
I have table in sql server with next fields:
Id(int)
Time_stamp(datetime)
Machine_state(int)
I'm tracking machine state which can be 0,1 and 2.
Now I want to calculate how long machine was in each state in each shift.
Shifts are 1. 8:00-17:00, 2- 17:00-01:00, 3- 01:00-08:00.
My problem is how I can calculate time of each state of machine(sum_time_0, sum_time_1, sum_time_2) and group that times by the shift:
Example data from table:
Id, time_stamp, machine_state
1, 27.3.2009 23:00, 0
2, 27.3.2009 23:45, 1
3, 27.3.2009 23:55, 2
4, 28.3.2009 00:30, 1
5, 28.3.2009 02:00, 2
6, 28.3.2009 04:00, 1
7, 28.3.2009 06:00, 2
8, 28.3.2009 09:00, 1
9, 28.3.2009 16:30, 2
10, 28.3.2009 16:50, 0
11, 28.3.2009 16:30, 2
12, 28.3.2009 18:50, 1
13, 28.3.2009 19:50, 2
If I use selec form dat1 to date 2 I’ losing time between two shifts, example:
11, 28.3.2009 16:30, 2
12, 28.3.2009 18:50, 1
Shift 2 starts at 17:00, if I select data from 17:00 I’ losing time from 17:00 to 18:50.
Because with select table I will get data from 18:50…
(example, sum_time_1=sum_time_1 + (datediff(s,data12, data13))
How to get data from 17:00 to 18:50 and how to get machine state in that period(because some part of time between data11 and data12 is in shift1 and some in shift2)
Please help
Thanks in advance
Any idea will help
October 8, 2009 at 3:05 pm
Try something like this:
create table #T (
ID int identity primary key,
Time_stamp datetime,
Machine_state int);
insert into #T (Time_stamp, Machine_state)
select '3.27.2009 23:00', 0 union all
select '3.27.2009 23:45', 1 union all
select '3.27.2009 23:55', 2 union all
select '3.28.2009 00:30', 1 union all
select '3.28.2009 02:00', 2 union all
select '3.28.2009 04:00', 1 union all
select '3.28.2009 06:00', 2 union all
select '3.28.2009 09:00', 1 union all
select '3.28.2009 16:30', 2 union all
select '3.28.2009 16:50', 0 union all
select '3.28.2009 16:30', 2 union all
select '3.28.2009 18:50', 1 union all
select '3.28.2009 19:50', 2;
create table #Shifts (
ID int identity primary key,
SNumber int,
SStart int,
SStop int);
insert into #Shifts (SNumber, SStart, SStop)
select 1, 8, 17 union all
select 2, 17, 24 union all
select 2, 0, 1 union all
select 3, 1, 8;
select SNumber as Shift, Machine_State,
case
when datepart(hour, State_Start) >= SStart then State_Start
else dateadd(hour, SStart, dateadd(day, datediff(day, 0, State_Start), 0))
end as EffectiveStart,
case
when datepart(hour, State_End) >= SStop then State_End
else dateadd(hour, SStop, dateadd(day, datediff(day, 0, State_End), 0))
end as EffectiveEnd
from #Shifts Shifts
inner join
(select Machine_State, time_stamp as State_Start,
(select min(time_stamp)
from #T T2
where time_stamp > T1.time_stamp
and Machine_state != T1.Machine_state) as State_End
from #T T1
where ID = 11) StartStop
on SStart >= datepart(hour, State_Start)
or SStop > datepart(hour, State_End);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 8, 2009 at 3:07 pm
I didn't know if you wanted to have the amount of time it was in the particular state, and, if so, by what interval (minutes, seconds, hours, etc.), so you'll have to wrap the EffectiveStart and EffectiveEnd columns in a datediff calculation if you want to get that.
I also have an ID = 11 in the sub-query. That was just for testing one of the rows that crossed a shift boundary. You'll want to remove it to test more of the rows.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 9, 2009 at 1:18 am
Thanks on prompt replay(I was thinking that nobody will answer).
I want to sum time of each state in minutes and group that times by the shifts), but time_stamps sometimes have difference in seconds, so I have to calculate in seconds and on the end transform in minutes.
so each shift for selected date range(from date1 to date2) will have report with:
total_alarm_time,
total_running_time,
total_stop_time,
number of alarms(number machine_state=1),
longest interval of machine running condition(machine_state=2),
longest interval of machine alarm(machine state=1)
To have better picture I did export table to access(file in attachment).
I will wait your advice before I test your code which you did send me in last replay.
Again many thanks on support.
October 22, 2009 at 11:36 am
can anybody help!!!:crying:
July 31, 2012 at 2:25 am
Hi.
I think this may help....
select id,
time_stamp,
cast(cast(datepart(yyyy,time_stamp) as nvarchar(4))
+'-'+
cast(datepart(month,time_stamp) as nvarchar(2))
+'-'+
cast(datepart(day,time_stamp) as nvarchar(2))
+' '+
cast(datepart(Hour,time_stamp) as nvarchar(2))
+':'+
cast(datepart(minute,time_stamp) as nvarchar(2))
+':00' as datetime) [normalised_TS],
machine_state
into #temp
from export_table
----
-- Running Times Assuming 2 = start and 1 = stop
----
select a.id [startid],
b.id [stopid],
a.normalised_TS [starttime],
a.machine_state [Start],
b.normalised_TS [endtime],
b.machine_state [stop],
datediff(minute,a.normalised_TS,b.normalised_TS) [elapsed_running_minutes]
into #runningtimes
from #temp a
join #temp b
on b.id = a.id+1
where a.machine_state = 2
order by a.id
----
-- Stop Times Assuming 2 = start and 1 = stop
----
select a.id [startid],
b.id [stopid],
a.normalised_TS [starttime],
a.machine_state [Start],
b.normalised_TS [endtime],
b.machine_state [stop],
datediff(minute,a.normalised_TS,b.normalised_TS) [elapsed_running_minutes]
into #stoptimes
from #temp a
join #temp b
on b.id = a.id+1
where a.machine_state = 1
order by a.id
----
-- Create Result sets
----
select 'Max_running_time'[typetime],
max(elapsed_Running_minutes) [value]
from #runningtimes
UNION ALL
select 'Max_stop_time'[typetime],
max(elapsed_Running_minutes) [value]
from #stoptimes
UNION ALL
select 'Total_running_time'[typetime],
sum(elapsed_Running_minutes) [Value]
from #runningtimes
UNION ALL
select 'Total_Stop_time'[typetime],
sum(elapsed_Running_minutes) [value]
from #stoptimes
UNION ALL
select 'Total_Alarm_count'[typetime],
count(*)
from #temp
where machine_state = '1'
drop table #temp
drop table #runningtimes
drop table #stoptimes
July 31, 2012 at 2:28 am
I mixed up the statusess... you'll just have to change them accordingly, but I think you get the rough idea what Iw as trying to do...
August 2, 2012 at 7:30 am
What's up !
I used GSquared tables for a solution like this:
-- This is just to create the table for testing.
create table #MachineStatus (
ID int identity primary key,
Time_stamp datetime,
Machine_state int);
insert into #MachineStatus (Time_stamp, Machine_state)
select '3.27.2009 23:00', 0 union all
select '3.27.2009 23:45', 1 union all
select '3.27.2009 23:55', 2 union all
select '3.28.2009 00:30', 1 union all
select '3.28.2009 02:00', 2 union all
select '3.28.2009 04:00', 1 union all
select '3.28.2009 06:00', 2 union all
select '3.28.2009 09:00', 1 union all
select '3.28.2009 16:30', 2 union all
select '3.28.2009 16:50', 0 union all
select '3.28.2009 16:30', 2 union all
select '3.28.2009 18:50', 1 union all
select '3.28.2009 19:50', 2;
create table #Shifts (
ID int identity primary key,
SNumber int,
SStart int,
SStop int);
insert into #Shifts (SNumber, SStart, SStop)
select 1, 8, 17 union all
select 2, 17, 24 union all
select 2, 0, 1 union all
select 3, 1, 8;
-- Here is the actual solution.
;With StatusFlow
AS
(
SELECT Time_Stamp,
Machine_State,
ChangeId = ROW_NUMBER() OVER(ORDER BY Time_Stamp), -- In case the identity gets messed up.
Shift = SNumber
FROM #MachineStatusms
LEFT JOIN #Shifts s ON DATEPART(HOUR,ms.Time_Stamp) BETWEEN s.SStart AND s.SStop
),
Changes AS
(
SELECT Status = ss.Machine_State,
NewStatus = se.Machine_State,
StateStart = ss.Time_Stamp,
StateEnd = se.Time_Stamp,
Duration = DATEDIFF(MINUTE,ss.Time_Stamp, se.Time_Stamp),
ss.Shift
FROM StatusFlow ss
INNER JOIN StatusFlow se ON se.ChangeId = ss.ChangeId + 1
)
SELECT Shift,Status, TotalTime = SUM(Duration)
FROM Changes
GROUP BY Shift,Status
ORDER BY Shift ,Status
August 2, 2012 at 7:35 am
Hey Valkerie,
It looks like we are really late =S, the post is from 2009 ha ha ha
August 2, 2012 at 7:58 am
Indeed it does... I just saw the post and posted what I thought would be a possible solution. I like your solution too...
August 2, 2012 at 8:27 am
Hi,
thanks on replay(after 3 years)!
It is very fast!
In any case I will check this solution, I did make solution for this(with help from SQLServerCentral) and it is running stable for 3 years.
I'm collecting time stamps about machine states from 10 machines over OPC server and store that to SQL server.
Than ERP send request to SQL to calculate how much machine was running/stooped from some time frame.
Is runs perfectly for 3 years(no bug).
Thanks on your wish to help!
Thanks
August 2, 2012 at 9:03 am
Thank you Valkeri =D
August 2, 2012 at 9:10 am
You project is nice,
how does your ERP gets your data?.
By the way , im kindof Arduino fan, i like machine things too =D, but im not a master.
August 3, 2012 at 12:52 am
Hi,
In production we have labeling system with scanners which counts the produced products.
On end of each shift ERP collect data about produced quantity's from labeling system and in same time collect data from SQL server(SQL gets data over OPC and DataHub)
about time when machine was running and stooped.
So on the end in ERP we have machine/shift productivity.
-machine down time
-machine running time
-average machine running speed(products/min)
-machine alarm time(how much of time machine was in alarm state)
All this is happening full automatically.
From all of this in ERP we get very powerful reports which we use to mark the shifts and adjust salary accordingly.
Thanks
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply