Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL SELECT HELP

Status
Not open for further replies.

Mahinda

Programmer
Joined
Jun 16, 2007
Messages
4
Location
US
I have a database table where TAGNAMEID, DATETIME, TAGNAMEVALUE are the columns. The values in each columns may look as:

555, 6-14-2007 12:34:00, 23.51
666, 6-14-2007 12:34:00, 190.32
555, 6-15-2007 12:34:00, 21.31
666, 6-15-2007 12:34:00, 187.35
555, 6-16-2007 12:34:00, 32.37
666, 6-16-2007 12:34:00, 167.32

I want to do a query on this data to get values between two given DATETIME values for TAGNAMEIDs and feed into a graph as X and Y values. In other words X axis will have 555 values and Y will have 666 values. I can do SELECT TAGNAMEVALUE FROM TABLENAME WHERE (TAGNAMEID=555 OR TAGNAMEID=666) AND DATETIME < timestamp AND DATETIME > timestamp ORDER BY DATETIME

But I cannot feed it without checking for TAGNAMEID in a while loop in the program itself. Could someone shed some light on me.

If I do the table as DATETIME, TAGNAME555, TAGNAME666 getting the data the way I want can be done as SELECT TAGNAME555, TAGNAME666 FROM tableName WHERE DATETIME<timestamp AND DATETIME>timestamp.

Thank you
 
And what about something like this ?
SELECT X.DATETIME, X.TAGNAMEVALUE Xvalue, Y.TAGNAMEVALUE Yvalue
FROM tableName X INNER JOIN tableName Y
ON X.DATETIME = Y.DATETIME
WHERE X.TAGNAMEID = 555 AND Y.TAGNAMEID = 666
AND X.DATETIME < timestamp AND X.DATETIME > timestamp
ORDER BY X.DATETIME

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
PHV Thank you very much
 
If I add another column to the above given table as LOCATIONID, that means the values will be as:

20, 555, 6-14-2007 12:34:00, 23.51
20, 666, 6-14-2007 12:34:00, 190.32
20, 555, 6-15-2007 12:34:00, 21.31
20, 666, 6-15-2007 12:34:00, 187.35
21, 555, 6-16-2007 12:34:00, 32.37
21, 666, 6-16-2007 12:34:00, 167.32

Can I do the following:

SELECT X.DATETIME, X.TAGNAMEVALUE Xvalue, Y.TAGNAMEVALUE Yvalue
FROM tableName X INNER JOIN tableName Y
ON X.DATETIME = Y.DATETIME AND X.SITEID = Y.SITEID
WHERE X.SITEID = 20 AND Y.SITEID = 20
AND X.TAGNAMEID = 555 AND Y.TAGNAMEID = 666
AND X.DATETIME < timestamp AND X.DATETIME > timestamp
ORDER BY X.DATETIME

Thank you
 
You may do the above, provided you choose the right columns (LOCATIONID or SITEID ?)
 
Yes, it was a typo!

Lets say you have more than 2 TAGNAMEIDs (50 of them). Is this the only way to get all 50 with a single call?

Thank you again for all the replies.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top