CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Joe Starnes
Joe Starnes

Reputation: 491

Is this possible using LEAD or is there a better way?

I'm wanting to return the dataset below from 2 tables. Table A will have temperature readings and table B will have Humidity readings. They will also have timestamps in each table so for instance like this:

Table A

enter image description here

Table B

enter image description here

Basically I'm wanting the output to get me the Temperature and the next closest Humidity reading to that temperature timestamp. So for instance the output would look like this:

enter image description here

So I'm wanting to start with Table A and then find the next closest following record for the same sensor in Table B based on timestamp.

Upvotes: 2

Views: 86

Answers (2)

xQbert
xQbert

Reputation: 35333

I like a cross apply here. Example: http://rextester.com/RGPS53348 or http://rextester.com/EKDX38312 with closer times

Essentially for each record in A, Find the record in B with matching trailer and zone and the one with the closest time

This should return the "Closest time" for humidity in relation to the time for the temperature and in case of ties, return the earlier time/record from humidity.

SELECT A.Trailer, A.Zone, A.temp, B.Humidity, A.Time A_time, B.Time B_Time
FROM tableA A
CROSS APPLY (SELECT Top 1 Z.* 
             FROM tableB  Z
             WHERE A.Trailer = Z.Trailer
               and A.Zone = Z.Zone
             --ORDER BY abs(A.Time- Z.Time) Asc, Z.Time) B --if int data
             ORDER BY abs(datediff(ss,A.Time,Z.Time)),Z.time) B --

The query basically says..

  • For each record in A, run the sub query to find the record with the time closest to the time [abs(A.time-Z.time)] on the A Record having the same trailer and zone.

  • In the case of ties, return the earlier b record.

  • Display the A record's trailer, zone, temp, and time and the b record's humidity and time.

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use first_value window function to do this.

select distinct a.*
,first_value(b.humidity) over(partition by a.trailer,a.zone,a.time order by b.time) as humidity
from tblA a
join tblB b on a.trailer=b.trailer and a.zone=b.zone and a.time<=b.time

Use a left join on tableA if you want to show nulls if no such humidity values exist at a later time in tableB.

Upvotes: 0

Related Questions