dcsimg
Results 1 to 3 of 3

Thread: Complex Query

  1. #1
    Join Date
    Feb 2005
    Posts
    2

    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

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    sample data please and expected results

  3. #3
    Join Date
    Feb 2005
    Posts
    2
    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
    Attached Files Attached Files
    • File Type: zip a.zip (8.3 KB, 94 views)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •