Solved

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

Posted on 2013-02-05
11
158 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
Comment Utility
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
Comment Utility
<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
Comment Utility
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
 
LVL 1

Author Comment

by:Salad-Dodger
Comment Utility
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
Comment Utility
Was this enough information?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 9

Assisted Solution

by:rajeevnandanmishra
rajeevnandanmishra earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now