1

Dynamically determine the starting time and ending time for a between statement based on the results of a previous query's table records when ordered appropriately for TOP and BOTTOM records datetime values!! Breakfast of Champions!!

Too BIG for SQLFiddler text file is right here and it'll create the data in the MYSQL tables as well

A zipped text file with the T-SQL in it to create tables and insert values.

Information

  1. I have table Weight and another table SetBit

    • The Weight table can have records inserted more than once a second and this is a sample of real data but the recording mechanism only records to the second time wise and not to a millisecond. The machine can generate the records more than once per second and they are imported by a routine insert operation.The recording mechanism recording this data only records when the process is running so if nothing is running; we get no data.

    • The SetBit table has its values recorded once a minute only and there are never any duplicates in these records based on the Time and TimeUTC column never being the same / unique keys. This process will always run recording the values per run so these are updated to the DB every minute or so via an import process.

The task and the problem:

Somehow I need to tell it to show Time and Weight records from the Weight table between a certain date like with a between statement which is not a problem at all from the dates of the same table. The trouble I'm having is when trying to include or show the value of the Bit record from the SetBit table when the Time of the records from the Weight table occurs when there not a matching Time record value in the SetBit table to join, or do a between topmost Time on Weight ordered by, etc. and have it dynamically figure it out.

The bit value doesn't change often but I'd like to be able to show:

SELECT Weight.Time, Weight.weight, SetBit.Bit

Every JOIN statement I've run so far has not given me the desired output so I'm not sure how to match up or JOIN the Time values between the table if that is even possible for it to check the SetBit table time value whent he time value of the record in the Weight table is run and show me what the bit value is whether 0, 1, or 3.

I need help or told this is not even possible if you know for sure not though.

NOTE: please see the link with the SQL to create test data tables and insert a small sample of the data I'm working with.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.