CODEWITHSUNDEEP
sqlsql-server
MrAquatic
MrAquatic

Reputation: 35

Combining multiple complex SELECT statements so they appear on one row

I'm fairly new to SQL so please be gentle. I've got a table that records temperature from various sensors in various locations. I'm happy to re-jig the database structure if it's easier.

The columns are: ID int, SensorID tinyint, LocationID tinyint, TempReadDT datetime, Temp (decimal 4,1)

The sensors record a reading every few mins and i'd like to have one stored proc to grab the following information for display on a summary page:

  1. Current Indoor Temp (the latest reading from sensor 1)
  2. Current Outdoor Temp (the latest reading from sensor 2)
  3. Average indoor temp for last 24 hours
  4. Average indoor temp for last 7 days
  5. Last updated date time (the latest date recorded)

I've created the individual SELECT statements (below) but I'm unsure of how to combine them into one query so the data is returned on one row.

SELECT TOP 1 Temp AS "intTemp"
  FROM Temperature
 WHERE SensorID = 1
 ORDER BY ID DESC

SELECT TOP 1 Temp AS "extTemp"
  FROM Temperature
 WHERE SensorID = 2
 ORDER BY ID DESC

SELECT AVG(Temp) AS "avgTemp24h"
  FROM Temperature
 WHERE SensorID = 1
   AND TempReadDT >= DATEADD(DAY,-1, SYSDATETIME())

SELECT AVG(Temp) AS "avgTemp7d"
  FROM Temperature
 WHERE SensorID = 1
   AND TempReadDT >= DATEADD(DAY,-7, SYSDATETIME())

SELECT MAX(TempReadDT)
  FROM Temperature

Upvotes: 3

Views: 3597

Answers (3)

Mr T.
Mr T.

Reputation: 4518

you can use the sub queries and the alias the result for example

SELECT t1.col1, t2.col2 from (select * from tbl1) as t1, (select * from tbl2) as t2

Upvotes: 0

Adriano Carneiro
Adriano Carneiro

Reputation: 58645

SELECT 
   Max(case SensorID when 1 then Temp else null end) AS "intTemp",
   Max(case SensorID when 2 then Temp else null end) AS "intTemp",
   AVG(case 
           when SensorID = 1  
            AND TempReadDT >= DATEADD(DAY,-1, SYSDATETIME()) 
           then Temp else null end) AS "avgTemp24h",
   AVG(case 
           when SensorID = 1  
            AND TempReadDT >= DATEADD(DAY,-7, SYSDATETIME()) 
           then Temp else null end) AS "avgTemp7d",
   MAX(TempReadDT) MaxTemp
FROM Temperature

Upvotes: 3

Radu Caprescu
Radu Caprescu

Reputation: 993

I HATE cross joins, but...

Select * from 
(SELECT TOP 1 Temp AS "intTemp"
  FROM Temperature
 WHERE SensorID = 1
 ORDER BY ID DESC
) A
cross join
(SELECT TOP 1 Temp AS "extTemp"
  FROM Temperature
 WHERE SensorID = 2
 ORDER BY ID DESC
)B
cross join
(
SELECT AVG(Temp) AS "avgTemp24h"
  FROM Temperature
 WHERE SensorID = 1
   AND TempReadDT >= DATEADD(DAY,-1, SYSDATETIME())
)C
cross join
(
SELECT AVG(Temp) AS "avgTemp7d"
  FROM Temperature
 WHERE SensorID = 1
   AND TempReadDT >= DATEADD(DAY,-7, SYSDATETIME())
)D 
cross join
(
SELECT MAX(TempReadDT)
  FROM Temperature) E

should work.

Upvotes: 3

Related Questions