CODEWITHSUNDEEP
sqlsql-servert-sql
user8400863
user8400863

Reputation: 745

SQL query to multiple tables for the most recent data

My SQL query at the moment is:

select 
    t.ZoneNumber, t.Mode, t.DeviceA, t.DeviceB, t.LevelA, t.LevelB,
    t.Counter, t.Options, t.AzureDeviceID_Field_Device, t.AzureDeviceID,
    t.TimeStamp,
    tt.ZoneName, tt.AzureDeviceID_Field_Device, tt.ZoneNumber, tt.TimeStamp 
from 
    [dbo].[tblZone] t, [dbo].[tblZoneName] tt   
inner join 
    (select 
         ZoneNumber, max(TimeStamp) as MaxDate 
     from
         [dbo].[tblZone] 
     where 
         AzureDeviceID_Field_Device like 'myFirstDevicea' 
     group by 
         ZoneNumber) tm on t.ZoneNumber = tm.ZoneNumber and t.TimeStamp = tm.MaxDate

What I am trying to do is query two or more tables at the same time for the most recent records on each table based on the DeviceID which is AzureDeviceID_Field_Device and Zonenumber.

For my example my DeviceID which is also a KEY is "myFirstDevicea". It will produce records of multiple Zones,each zone has a number e.g. 1 to 10. For each zone number i would like to produce only the most recent.

tblZone has zone parameters, and tbl zonename as the name the zone is been given. This name can change from time to time.

So my aim is to produce a recent list from tablezone, which i can do and then get the most recent name given from table zonename.

When I run the query, I am getting various could not bound errors?

Can someone help me out in regards to this query.

tblZone

tableZoneName

  select t.ZoneNumber,t.Mode,t.DeviceA,t.DeviceB,t.LevelA,t.LevelB,t.Counter,t.Options,t.AzureDeviceID_Field_Device,t.AzureDeviceID,t.TimeStamp
  from [dbo].[tblZone] t
  inner join (select ZoneNumber, max(TimeStamp) as MaxDate FROM [dbo].[tblZone] WHERE AzureDeviceID_Field_Device LIKE 'myFirstDevicea' group by ZoneNumber )tm 
  on  t.ZoneNumber = tm.ZoneNumber and t.TimeStamp = tm.MaxDate 

  select tt.ZoneName,tt.AzureDeviceID_Field_Device,tt.ZoneNumber,tt.TimeStamp
  from  [dbo].[tblZoneName] tt 
  inner join (select ZoneNumber, max(TimeStamp) as MaxDate FROM [dbo].[tblZoneName] WHERE AzureDeviceID_Field_Device LIKE 'myFirstDevicea' group by ZoneNumber )tm 
  on  tt.ZoneNumber = tm.ZoneNumber and tt.TimeStamp = tm.MaxDate

Upvotes: 1

Views: 622

Answers (1)

iamdave
iamdave

Reputation: 12243

You can do this by using derived tables within a common table expression, utilising windowed functions to identify the most recent TimeStamp value in both tables:

-- Built test data:
declare @tblZone table(AzureDeviceID_Field_Device nvarchar(100),Zonenumber int, [TimeStamp] datetime2);
declare @tableZoneName table(AzureDeviceID_Field_Device nvarchar(100),ZoneNumber int, ZoneName nvarchar(100), [TimeStamp] datetime2);
insert into @tblZone values ('a1',1 ,getdate()-2),('a1',1 ,getdate()-1),('a1',1 ,getdate()),('a1',15,getdate()-4),('a1',15,getdate()-3),('a1',15,getdate()-2);
insert into @tableZoneName values ('a1',1 ,'Zone A1',getdate()-2),('a1',1 ,'Zone A1',getdate()-1),('a1',1 ,'Zone A1',getdate()),('a1',15,'Zone A15',getdate()-4),('a1',15,'Zone A15',getdate()-3),('a1',15,'Zone A15',getdate()-2);

-- Common Table Expressions (CTE) to add a Row Number to both tables:
with z as
(
    select AzureDeviceID_Field_Device
            ,Zonenumber
            ,[TimeStamp]    -- row_number() returns the order the rows are in, grouped by the PARTITION columns and ordered by the ORDER BY colmns
            ,row_number() over (partition by AzureDeviceID_Field_Device, ZoneNumber
                                order by [TimeStamp] desc
                               ) as rn
    from @tblZone
),zn as
(
    select AzureDeviceID_Field_Device
            ,Zonenumber
            ,ZoneName
            ,[TimeStamp]
            ,row_number() over (partition by AzureDeviceID_Field_Device, ZoneNumber
                                order by [TimeStamp] desc
                               ) as rn
    from @tableZoneName
)
-- Join the two derived tables together to get your result:
select *
from z
    left join zn
        on z.AzureDeviceID_Field_Device = zn.AzureDeviceID_Field_Device
            and z.ZoneNumber = zn.Zonenumber
            and zn.rn = 1    -- Rows with a rn value of one will have the most recent TimeStamp.
where z.rn = 1
order by z.AzureDeviceID_Field_Device
        ,z.ZoneNumber;

Output:

+----------------------------+------------+-----------------------------+----+----------------------------+------------+----------+-----------------------------+----+
| AzureDeviceID_Field_Device | Zonenumber |          TimeStamp          | rn | AzureDeviceID_Field_Device | Zonenumber | ZoneName |          TimeStamp          | rn |
+----------------------------+------------+-----------------------------+----+----------------------------+------------+----------+-----------------------------+----+
| a1                         |          1 | 2017-10-23 09:33:43.3066667 |  1 | a1                         |          1 | Zone A1  | 2017-10-23 09:33:43.3233333 |  1 |
| a1                         |         15 | 2017-10-21 09:33:43.3066667 |  1 | a1                         |         15 | Zone A15 | 2017-10-21 09:33:43.3233333 |  1 |
+----------------------------+------------+-----------------------------+----+----------------------------+------------+----------+-----------------------------+----+

Upvotes: 1

Related Questions