Sql-server – How to query two values from the same table from different records combined with fields from other tables

sql server

I have a query similar to the one below which is getting data from a couple of tables and works as expected. The table eventsink.RedEvent has another column called Finished. I want to add the Finished column to the result of my query, however the value I want for Finished will be from another record than the record that Created is queried from. I need the MIN() of Finished that also matches DeviceId and also has an Id = '1'

Thanks for any help you can provide. I am pretty new to such complex SQL queries.

SELECT eventsink.RedEvent.DeviceId,
       MAX(eventsink.RedEvent.Created),
       BranchNumber,
       CountryCode
FROM eventsink.RedEvent
JOIN md.Elevator ON md.Elevator.DeviceId = eventsink.RedEvent.DeviceId
JOIN eventsink.RedMasterDataEvent ON eventsink.RedMasterDataEvent.DeviceId = eventsink.RedEvent.DeviceId
WHERE (Id = '9'
       AND SoftwareVersion LIKE '[1][.]%')
GROUP BY eventsink.RedEvent.DeviceId,
         BranchNumber,
         CountryCode

Best Answer

Try this:

SELECT eventsink.RedEvent.DeviceId,
           MAX(eventsink.RedEvent.Created),
           BranchNumber,
           CountryCode
    FROM eventsink.RedEvent
    JOIN md.Elevator ON md.Elevator.DeviceId = eventsink.RedEvent.DeviceId
    JOIN eventsink.RedMasterDataEvent ON eventsink.RedMasterDataEvent.DeviceId = eventsink.RedEvent.DeviceId
    JOIN (SELECT MIN(Finished), DeviceId FROM eventsink.RedEvent WHERE Id = '1' GROUP BY DeviceId) t 
         ON eventsink.RedEvent.DeviceId = t.DeviceId
    WHERE (Id = '9'
           AND SoftwareVersion LIKE '[1][.]%')
    GROUP BY eventsink.RedEvent.DeviceId,
             BranchNumber,
             CountryCode

Alternatively, you can put the derived table (aliased as t) into a CTE. They are the same thing really.

Related Solutions

Sql-server – Select values from 2 tables with same properties

DECLARE @sql NVARCHAR(MAX) = N'SELECT ';

SELECT @sql += N'
  ' + QUOTENAME(c.name) 
  + ' = COALESCE(es.' + QUOTENAME(c.name) 
       + ', s.' + QUOTENAME(c.name) + '),'
FROM sys.columns AS c
INNER JOIN sys.columns AS c2
ON c.name = c2.name
AND c.[object_id] = OBJECT_ID('dbo.SETTINGS')
AND c2.[object_id] = OBJECT_ID('dbo.EXTENDED_SETTINGS')
AND c.name NOT IN (N'name', N'ID' /* , ... potentially others ... */);

SET @sql += N' s.name, s.ID';

SELECT @sql += N', 
  s.' + QUOTENAME(c.name)
  FROM sys.columns AS c
  WHERE [object_id] = OBJECT_ID('dbo.SETTINGS')
  AND name NOT IN (N'name', N'ID') 
  AND NOT EXISTS 
  (
    SELECT 1 FROM sys.columns AS c2
      WHERE c2.[object_id] = OBJECT_ID('dbo.EXTENDED_SETTINGS')
      AND c2.name = c.name
  );

SET @sql += N'
  FROM dbo.SETTINGS AS s
  INNER JOIN dbo.EXTENDED_SETTINGS AS es
  ON s.ID = es.SETTINGS_ID
  WHERE es.ID = @extSetId AND s.ID = @settID;';

PRINT @sql;
--EXEC sp_executesql @sql;

Please use table aliases and schema prefixes, and here is why I prefer COALESCE over ISNULL. And if those tables really aren't stored as upper case in a case sensitive collation, do you really need all caps? Also, ID is a horrible and ambiguous name for a column. It should be the same name across the schema; always describing exactly what it is - especially when you're doing lazy dynamic things like this.

Sql-server – Query “last” records in a table with non-sequential key fields

You can use ORA_ROWSCN pseudocolumn to find rows that have been changed since last update. By default it tracks changes by database block, not by row, but it can be used to limit the amount of data transferred between databases.

I would suggest a following procedure:

Find the current Oracle SCN (System Change Number)

select dbms_flashback.get_system_change_number from dual;
Get rows that have been changed since last update

select * from [source_table] where ora_rowscn > [last scn] and ora_rowscn <= [current scn]
Resolve possible duplicates in your target database
Store the [current scn] to target database so it can be used as [last scn] during the next iteration

Best Answer

Related Solutions

Sql-server – Select values from 2 tables with same properties

Sql-server – Query “last” records in a table with non-sequential key fields

Related Question