SELECT house.name, point.id, rw.data FROM Unit AS city JOIN Unit AS street ON street.ParentID = city.UnitID JOIN Unit AS house ON house.ParentID = street.UnitID JOIN Points AS point ON point.ID = house.UnitID JOIN Data AS rw ON rw.DevID = point.DevID AND rw.Time BETWEEN '20161209' AND '20161211' WHERE city.id = 1

I get the street, then the house number, and after the id of the device tied to the house. From the device, between dates in 3 days, I get 3 entries of float values.

How to get the difference of values ​​(rw.Value) of 11 and 9 numbers, and put it in rw.data?

  • I fight for several days with the request. I would be grateful for the help and detailed answer - Mr. Black
  • Show the request to create a table, an example of the source data and an example of the expected result. This will significantly speed up the search for a solution. - Sergey Gornostaev
  • @SergeyGornostaev, the table was created by third-party software. Data is displayed in the form of "Street, Home, Value (rw.Value)" 3 entries for the same house, that is, the interval of dates for each number. I need to take the last entry from the range and subtract the first one from it. That is, instead of a range and three entries, I need one entry of one house - Mr. Black
  • Take the record for the average date and count for it LEAD (value) -LAG (value) ... :) - Akina
  • @SergeyGornostaev, visually looks like this . 2 houses, 3 entries - Mr. Black

1 answer 1

Based on the changed statement in comments (the field value always increases with time):

 SELECT house.name, point.id, max(rw.Value)-min(rw.Value) FROM Unit AS city JOIN Unit AS street ON street.ParentID = city.UnitID JOIN Unit AS house ON house.ParentID = street.UnitID JOIN Points AS point ON point.ID = house.UnitID JOIN Data AS rw ON rw.DevID = point.DevID AND rw.Time BETWEEN '20161209' AND '20161211' WHERE city.id = 1 GROUP BY rw.DevID, house.name, point.id
  • Conditions remain the same. Max and Min without GROUP BY , as I understand it, will not work. Only by adding some variables in GROUP BY did I get what I needed. Can you explain? - Mr. Black
  • @ Mr.Black If the group by not specified, then everything is grouped to one total. Respectively min/max are taken for all selected devices, and not for each separately - Mike