0

Background

Piggybacking on a previous question I have a table with these sample values: 

Temp_Date             Building_ID          Sector_ID          Temperature    
[Date/Time]           [I32]                [I32]              [DBL]
1/9/2018 4:14:31 AM    456                   0                20.23    
1/9/2018 4:15:14 AM    123                   1                35.23    
1/9/2018 4:16:21 AM    123                   0                15.23    
1/9/2018 4:15:45 AM    123                   2                25.23    
1/9/2018 4:16:21 AM    456                   0                25.23    
1/9/2018 4:16:59 AM    123                   1                35.23

Currently, I am running this SQL query to get the latest value for each sector:

select 
    t.Building_ID, t.Sector_ID, t.Temperature, t.Temp_Date
from 
    MyTable t
inner join 
    (select 
         Building_ID, Sector_ID, max(date_time) as maxMaxTemp_Date
     from 
         MyTable t
     group by 
         Building_ID, Sector_ID) tm on t.Building_ID = tm.Building_ID 
                                    and t.Sector_ID = tm.Sector_ID 
                                    and t.Temp_Date = tm.MaxTemp_Date
order by
    t.Building_ID, t.Sector_ID

Learning more about SQL, I know that I can:

  1. Add a WHERE Building_ID = [I32 value] to limit the values returned to one Building_ID value

  2. Turn this into a stored procedure show-temps allowing my server to more efficiently return the desired filtered data with the statement `show-temps([value]).

Doing both of these would get me something that looks like

CREATE PROCEDURE show-temps
    @Building int,
AS
    SELECT 
        t.Building_ID, t.Sector_ID, t.Temperature, t.Temp_Date
    FROM 
        MyTable t
    INNER JOIN 
        (SELECT 
             Building_ID, Sector_ID, MAX(date_time) AS maxMaxTemp_Date
         FROM
             MyTable t
         GROUP BY 
             Building_ID, Sector_ID) tm ON t.Building_ID = tm.Building_ID 
                                        AND t.Sector_ID = tm.Sector_ID 
                                        AND t.Temp_Date = tm.MaxTemp_Date
    WHERE 
        (t.Building_ID = @Building)        
    ORDER BY 
        t.Building_ID, t.Sector_ID

My question: is there a way to pass an array of int values to the WHERE() function so that I could get the latest data for a set of buildings instead of calling the function multiple times?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ATE-ENGE
  • 129
  • 10
  • 1
    The `WHERE` clause needs to be **after** the `INNER JOIN` (but before the `ORDER BY`) – marc_s Jan 17 '18 at 19:03
  • Fixed, thank you. (note that the question still stands though) – ATE-ENGE Jan 17 '18 at 19:25
  • What DBMS are you running? MS Access' [`PROCEDURE`](https://msdn.microsoft.com/en-us/library/office/ff845861.aspx) params does not have an [array/list data type](https://msdn.microsoft.com/en-us/library/office/ff193793.aspx). This sounds like an SQL Server procedure. Please tag appropriately. – Parfait Jan 17 '18 at 22:43

0 Answers0