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.
I have table
Weightand another table
Weighttable 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.
SetBittable 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
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
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.