Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

sql time intervals problem Expand / Collapse
Author
Message
Posted Thursday, October 8, 2009 2:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 28, 2012 6:38 AM
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










Post #800358
Posted Thursday, October 8, 2009 3:05 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #800391
Posted Thursday, October 8, 2009 3:07 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #800393
Posted Friday, October 9, 2009 1:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 28, 2012 6:38 AM
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.


  Post Attachments 
dbo_perini.rar (5 views, 8.99 KB)
Post #800544
Posted Thursday, October 22, 2009 11:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 28, 2012 6:38 AM
Points: 10, Visits: 22
can anybody help!!!
Post #807354
Posted Tuesday, July 31, 2012 2:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 9, 2014 8:47 AM
Points: 34, Visits: 75
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
Post #1337728
Posted Tuesday, July 31, 2012 2:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 9, 2014 8:47 AM
Points: 34, Visits: 75
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...
Post #1337729
Posted Thursday, August 2, 2012 7:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 16, 2014 4:26 PM
Points: 338, Visits: 385
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


Post #1339153
Posted Thursday, August 2, 2012 7:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 16, 2014 4:26 PM
Points: 338, Visits: 385
Hey Valkerie,

It looks like we are really late =S, the post is from 2009 ha ha ha
Post #1339156
Posted Thursday, August 2, 2012 7:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 9, 2014 8:47 AM
Points: 34, Visits: 75
Indeed it does... I just saw the post and posted what I thought would be a possible solution. I like your solution too...
Post #1339184
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse