Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sql time intervals problem


sql time intervals problem

Author
Message
PLC-Master
PLC-Master
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 22
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
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
PLC-Master
PLC-Master
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 22
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.
Attachments
dbo_perini.rar (7 views, 8.00 KB)
PLC-Master
PLC-Master
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 22
can anybody help!!!Crying
valkerieforever
valkerieforever
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 96
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
valkerieforever
valkerieforever
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 96
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...
adrian.facio
adrian.facio
Old Hand
Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)

Group: General Forum Members
Points: 342 Visits: 386
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 #MachineStatus ms
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



adrian.facio
adrian.facio
Old Hand
Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)

Group: General Forum Members
Points: 342 Visits: 386
Hey Valkerie,

It looks like we are really late =S, the post is from 2009 ha ha ha
valkerieforever
valkerieforever
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 96
Indeed it does... I just saw the post and posted what I thought would be a possible solution. I like your solution too...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search