Query language to return data from one table based on a value from a second table

You can join the tables using a common column

select t1.*
from t1
inner join t2 on t1.col1 = t2.col1
where t2.col2 = 1

<air code, spell out for us how Table1 and Table2 are related and we'll finish it... >

SELECT t1.blah, t1.blah, t1.blah
FROM Table1 t1
   JOIN (SELECT Min(somedate) as date_min, Max(somedate) as date_max
             FROM Table2
             WHERE SomeBitValue = 1) t2 on {???}
WHERE t1.somedate BETWEEN t1.date_min AND t1.date_max

It will be  better (or may be the only way) to write a trigger on Table2.
Which on the change of bit value to true, it takes a snapshot of the integer value from table1 and on the change of bit value to false, it again takes a snapshot of the integer value from table1.

May be as jimhorn suggested to give more details about Table1 & Table2 could provide more insight.

Apologies - I am just getting my feet wet with this and I'm a bit long in the tooth. Not even sure what questions to ask yet.

--Returns Max Value of instrument per day
Declare @StDate DateTime
Declare @EndDate DateTime
Set @STDate = '11/1/2012'
Set @EndDate = '12/1/2012'
Select dateadd(day,(datediff(day,0,TIMEandDATE)),0) as MyDate, MAX(Value) AS Max_Turb 
From Turbs where TagID = 3004
and TimeAndDate >= @StDate
and TimeAndDate < @EndDate
Group by dateadd(day,(datediff(day,0,TIMEandDATE)),0)
Order by dateadd(day,(datediff(day,0,TIMEandDATE)),0)

--returns when the process line status bit changes to On_Line
Declare @StDate DateTime
Declare @EndDate DateTime
Set @STDate = '11/1/2012'
Set @EndDate = '12/1/2012'
SELECT TIMEandDATE as MyDate, Value as On_Line
  FROM StatusBits
  Where TagID = 10003
  and Value = 1
  and TimeAndDate >= @StDate
and TimeAndDate < @EndDate

Select allOpen in new window

The TagID 10003 is not populated all day, it is only logged and time stamped when it changes state.

The TagID 3004 is logged all day long without regard to anything else. Because of this it will log values that are meaningless the TagID 10003 bit indicates that the process is running, the values are valid and should be considered for the output. TagID 10003 bit os a global indication pertaining to many different processes and is not unique to this one value.

The result needs to show the MAX value of TagID 3004 each day (From table Turbs), but ONLY using the values during the time between transitions of TagID 10003 i.e. if the process was On_Line (from Table StatusBits)

Was this enough information?

Thanks for the reply - you have the right idea but it only returns a value for midnight each day which is what I have been seeing with my own attempts. I am beginning to think this database design is the real issue and that is going to drive me crazy if kept this way.
TagID 3004 is logged every 5 minutes all day long.
TagID 10003 only gets logged when it changes value (and also at midnight each day). So if it doesn't change value for many hours or days I get no values from the second query. ANd even when it does, the timestamps for both would rarely match so its unlikely that a result would be found.


To demonstrate - with this query on the Online bit you can see how few entries exist per day for OnLine.

DECLARE @OnLine TABLE (RN int IDENTITY(1,1), TIMEandDATE datetime, VALUE float)
Declare @StartDT DateTime
Declare @EndDT DateTime
Set @StartDT = '11/1/2012'
Set @EndDT = '12/1/2012'

INSERT INTO @OnLine
               SELECT TOP 1 @StartDT, 
		VALUE FROM StatusBits 
		WHERE TagID = 10003 
		AND TIMEandDATE < @StartDT 
		ORDER BY TIMEandDATE DESC
INSERT INTO @OnLine
               SELECT TIMEandDATE, 
		VALUE FROM StatusBits 
		WHERE TagID = 10003
               	AND TIMEandDATE >= @StartDT 
		AND TIMEandDATE < @EndDT
               ORDER BY TIMEandDATE
INSERT INTO @OnLine
               SELECT TOP 1 @ENDDT, 
		VALUE FROM StatusBits 
		WHERE TagID = 10003 
		AND TIMEandDATE < @EndDT 
		ORDER BY TIMEandDATE DESC

Select allOpen in new window

I get this output

I am trying to return results of tagID 3004 during the time between when TagID 10003 turns from a 0 until it turns to a 1.
I can't see how that is even possible to query for.
Maybe using BETWEEN somehow?

I've requested that this question be deleted for the following reason:

the answer required a CTE and was provided by a consultant. If I could give points for helping I would because I am grateful for the attempts but since there was no "Solution" provided I cant give points. Thank you all anyway

A consultant was brought oveer to write a bunch of code that made this work. His solution makes me want to chnage the way I capture...
Thank you all for trying.  If I had better design, this would have worked out better.