-
Complex Query
Hi All.
I am trying to seek advice for a complex query. I am logging data to a MSDE SQL table with 2 fields, timestamp and value. timestamp is obviously the time and value is either 1 or 0.
I am trying to get the time difference between the 1 and 0 for each 'pair' and then add up the total. This is for when a machine is stopped.
I am thinking if writing an ADO script but was wondering if a query may be easier but am uncertain how to do this with a query.
The intention is to link each pair with another table to get a reason for the time so ultimatley the machine was stopped for x minutes for y reason is what i am trying to achieve.
Any ideas welcome ty
-
sample data please and expected results
-
1 Attachment(s)
Hi MAK
I have attached a sample access database with 2 tables. I had to link the tables them export to get the data.
Table Running is the 1/0 where 0 represents running and 1 represents stopped. Table Reason is a reason code 1-6, 0 is ignored.
I am trying to achieve a query to return stoppage times by reason between a start and stop time, ultimatley entered by the user.
So, if we said 6am-6pm on 28 feb 2005
i would like to return
Reason 1 xxxx seconds
Reason 2 yyyy seconds
...
...
...
...
Reason 6 zzz seconds.
Hope this helps you to understand
Ty