Address

Author Topic: SQL Question  (Read 1136 times)

aspazatak

  • FamiLAB Member
  • Jr. Member
  • *
  • Posts: 50
    • View Profile
SQL Question
« on: March 28, 2012, 02:14:36 PM »
OK, I've given myself a headache trying to do this and I think I'm running around in circles and trying to same things over now, so I'm asking for advice and guidance.


I am running some reports against a test database (SQL server) and I have 2 tables currently.  the first table is a list of sensor ID's while the second table is a list of customers those sensors are assigned to.  What I want is a SQL query that will look up the latest received data for a sensor ID.


Meaning look in Table 1 for a sensor ID, use that ID to do a query in table 2 to get data, then go back and look up the next sensor ID in table 1 and repeat until all finished.


Here is what we are doing right now, but it requires a query for each sensor ID and there has to be a way to make a single line that will do this.



SELECT  top 1 t2.PercentFull, t1.Customer_Name, t1.Street_Address, t2.SensorTimeStamp, t1.Tank_Size, t2.SensorID
FROM    dbo.CustomerTable as t1 join dbo.LogTable as t2 on t1.Customer_ID = t2.SensorID
WHERE    SensorID = 60005 and SensorTimeStamp BETWEEN DATEADD(day, -1, GetDate()) AND GETDATE()
order by SensorTimeStamp desc;


this looks over the last 24 hours and return the most recent value for sensor ID 6005. I want the 6005 to be dynamic based on a lookup in another table.

aspazatak

  • FamiLAB Member
  • Jr. Member
  • *
  • Posts: 50
    • View Profile
Re: SQL Question
« Reply #1 on: March 29, 2012, 03:00:30 PM »
figure it out finally



declare @t table (Level int, Name varchar(50), Address varchar (50), Time datetime, Tank_Size int, ID int)
insert @t
select PercentFull, Customer_Name, Street_Address, SensorTimeStamp, Tank_Size, SensorID FROM CustomerTable, LogTable Where SensorTimeStamp BETWEEN DATEADD(day, -1, GetDate()) AND GETDATE() AND SensorID = Customer_ID


select * from @t a where Time = (select MAX(Time) from @t where ID = a.ID) order by Level