Summing differences between time stamps

Question

Summing differences between time stamps

PLC-Master

Old Hand

Points: 308
More actions
October 22, 2009 at 11:59 am

#74194

Hi,
I'm tracking machine state which can be 0,1 and 2,
and storing that data in sql table with time_stamp.
I have table in sql server with next fields:
id(int)
time_stamp(datetime)
machine_state(int)
Machine state is connected with machine condition:
machine_state =0 -machine stooped
machine_state =1-machine with alarm
machine_state =2-machine running
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.
I want to calculate time in seconds and then convert to minutes.
To have better picture I did export table to access(file in attachment).
Any advice will help.
Thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 1

I've seen this exact same problem a couple of times over the years... and it was homework for a course. Please show what you've tried and if you still need help, please read and heed the article at the first link in my signature below. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

Are you referring to exercise 3 of Ms Brawn course ? :hehe:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 3

Heh... is that what it is? I'm going to have to get a copy of that book. You don't happen to have the ISBN for it do you?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

Select top 1

3 as LuckyExerciseNumber

, isnull([Title],'') + [LastName]

FROM [AdventureWorks].[Person].[Contact]

ORDER BY SUBSTRING(CONVERT(VARCHAR(40), NEWID()), 1, 8)

😀

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

PLC-Master Old Hand Points: 308 More actions · Answer 5

Hi,

in attachment is sql code what I did try for one shift(for other shifts i was thinking to make loop where I will change @t1 and@t2 according shift for which I'm calculating times).

My problems is time during shift transition.

For example:

'23.10.2009 06:00', 2

'23.10.2009 09:00', 1

'23.10.2009 16:30', 2

If I you use select between 8:00 and 17:00 I don't know what was between 8:00 and 9:00 because my first data in temp table will be with time 9:00.

So in my sum I'm losing 1 hour (from 8:00 to 9:00).

Thanks in advance