Select only record until timestamp from another table-CodePudding

I have three tables.

The first one is Device table

 ---------- ------ 
| DeviceId | Type |
 ---------- ------ 
| 1        | 10   |
| 2        | 20   |
| 3        | 30   |
 ---------- ------

The second one is History table - data received by different devices.

     ---------- ------------- -------------------- 
    | DeviceId | Temperature | TimeStamp          |
     ---------- ------------- -------------------- 
    | 1        | 31          | 15.08.2020 1:42:00 |
    | 2        | 100         | 15.08.2020 1:42:01 |
    | 2        | 40          | 15.08.2020 1:43:00 |
    | 1        | 32          | 15.08.2020 1:44:00 |
    | 1        | 34          | 15.08.2020 1:45:00 |
    | 3        | 20          | 15.08.2020 1:46:00 |
    | 2        | 45          | 15.08.2020 1:47:00 |
     ---------- ------------- --------------------

The third one is DeviceStatusHistory table

     ---------- --------- -------------------- 
    | DeviceId | State  | TimeStamp          |
     ---------- --------- -------------------- 
    | 1        | 1(OK)   | 15.08.2020 1:42:00 |
    | 2        | 1(OK)   | 15.08.2020 1:43:00 |
    | 1        | 1(OK)   | 15.08.2020 1:44:00 |
    | 1        | 0(FAIL) | 15.08.2020 1:44:30 |
    | 1        | 0(FAIL) | 15.08.2020 1:46:00 |
    | 2        | 0(FAIL) | 15.08.2020 1:46:10 |
     ---------- --------- --------------------

I want to select the last temperature of devices, but take into account only those history records that occurs until the first device failure.

Since device1 starts failing from 15.08.2020 1:44:30, I don't want its records that go after that timestamp.

The same for the device2.

So as a final result, I want to have only data of all devices until they get first FAIL status:

 ---------- ------------- -------------------- 
| DeviceId | Temperature | TimeStamp          |
 ---------- ------------- -------------------- 
| 2        | 40          | 15.08.2020 1:43:00 |
| 1        | 32          | 15.08.2020 1:44:00 |
| 3        | 20          | 15.08.2020 1:46:00 |
 ---------- ------------- --------------------

I can select an appropriate history only if device failed at least once:

SELECT * FROM Device D
CROSS APPLY
(SELECT TOP 1 * FROM History H 
WHERE D.Id = H.DeviceId
and H.DeviceTimeStamp < 
(select MIN(UpdatedOn) from DeviceStatusHistory Y where [State]=0 and DeviceId=D.Id)
ORDER BY H.DeviceTimeStamp desc) X
ORDER BY D.Id;

The problems is, if a device never fails, I don't get its history at all.

Update: My idea is to use something like this

SELECT * FROM DeviceHardwarePart HP
CROSS APPLY
(SELECT TOP 1 * FROM History H 
WHERE HP.Id = H.DeviceId
and H.DeviceTimeStamp < 
(select ISNULL((select MIN(UpdatedOn) from DeviceMetadataPart where [State]=0 and DeviceId=HP.Id), 
cast('12/31/9999 23:59:59.997' as datetime)))
ORDER BY H.DeviceTimeStamp desc) X
ORDER BY HP.Id;

I'm not sure whether it is a good solution

CodePudding user response：

You can use COALESCE: coalesce(min(UpdateOn), cast('9999-12-31 23:59:59' as datetime)). This ensures you always have an upperbound for your select instead of NULL.

CodePudding user response：

I will treat this as two parts problem

I will try to find the time at which device has failed and if it hasn't failed I will keep it as a large value like some timestamp in 2099
Once I have the above I can simply join with histories table and take the latest value before the failed timestamp.

In order to get one, I guess there can be several approaches. From top of my mind something like below should work

select device_id, coalesce(min(failed_timestamps), cast('01-01-2099 01:01:01' as timestamp)) as failed_at
(select device_id, case when state = 0 then timestamp else null end as failed_timestamps from History) as X
group by device_id

This gives us the minimum of failed timestamp for a particular device, and an arbitrary large value for the devices which have never failed.

I guess after this the solution is straight forward.