Solved

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

Posted on 2013-02-05
11
178 Views
Last Modified: 2013-11-05
I have a 2 tables of data, Table1 has integer values constantly added by an instrument all day long, Table2 has a boolean data indicating whether that process is being used and subsequently whether that value should be ignored or not.

How do I select from Table1, only the values withing the time span where the Table2 bit is true?
0
Comment
Question by:Salad-Dodger
11 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38856286
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
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38856290
<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
0
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 38856316
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.
0
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
LVL 1

Author Comment

by:Salad-Dodger
ID: 38856444
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

Open 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)
0
 
LVL 1

Author Comment

by:Salad-Dodger
ID: 38857091
Was this enough information?
0
 
LVL 9

Assisted Solution

by:rajeevnandanmishra
rajeevnandanmishra earned 500 total points
ID: 38857944
Below is the code:
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

and exists(SELECT TIMEandDATE as MyDate, Value as On_Line
  FROM StatusBits
  Where TagID = 10003
  and Value = 1
  and TimeAndDate >= @StDate
and TimeAndDate < @EndDate)

Group by dateadd(day,(datediff(day,0,TIMEandDATE)),0)
Order by dateadd(day,(datediff(day,0,TIMEandDATE)),0)

Open in new window


I have considered that the final required output will come from query one. Though it should come only when the second query satisfies the condition. so i have joined the two here above.
But now you may need to run this routine continously as the timing of event 10003 is not known beforehand.

Let us know if this is something that you required?
0
 
LVL 1

Author Comment

by:Salad-Dodger
ID: 38859971
Results of QueryThanks 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

Open in new window

I get this output
Results of Query
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?
0
 
LVL 9

Accepted Solution

by:
rajeevnandanmishra earned 500 total points
ID: 38863311
Hi,
It definitely seems to be little different story here. The StatusBits table doesn't store any orderly fashioned data and it becomes difficult to identify the change status.
But with a few more assumptions like:
  * The StatusBits table shall be changing its bit value to 1 atleast once in a day for 10003
  * The requirement is to find the max value from Turbs till the status was 1 for 10003

I have written below code:
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 T, 
(
select dateadd(day,(datediff(day,0, a.TIMEandDate)),0) myDate, min(a.TIMEandDate) as fdt 
from StatusBits a where a.TagID = 10003 and a.TIMEandDate >= (select max(b.TIMEandDate) from StatusBits b 
	where b.TagID = 10003 and dateadd(day,(datediff(day,0, a.TIMEandDate)),0) = 
		dateadd(day,(datediff(day,0, b.TIMEandDate)),0) 
	and b.value = 1) and a.TimeAndDate >= @StDate and a.TimeAndDate < @EndDate 
	group by dateadd(day,(datediff(day,0, a.TimeAndDate)),0)
) F 
where dateadd(day,(datediff(day,0,TIMEandDATE)),0) = F.myDate 
	and T.TagID = 3004 
	and T.TIMEandDate <= F.fdt
Group by dateadd(day,(datediff(day,0,TIMEandDATE)),0)
Order by dateadd(day,(datediff(day,0,TIMEandDATE)),0)

Open in new window


You can try the block written for alias F as separately to identify the maximum datetime value for a particular day when the value was 1 for tag 10003. I have just joined it with the main Turbs table. Give it a try.

I am now thinking strongly about a trigger that should keep the required snapshot whenever bit value for 10003 changes and to keep it in a separate table.

anyway good-luck to you :)
0
 
LVL 1

Author Comment

by:Salad-Dodger
ID: 39625449
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
0
 
LVL 1

Author Closing Comment

by:Salad-Dodger
ID: 39625450
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.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question