SQL Server - get first and last (by datetime) DataPoints value
I have two tables in SQL Server database:
Meters:
MeterId int [PK]
Name varchar
DataPoints:
DataPointId int [PK]
DateTime datetimeoffset
Value decimal
MeterId int [FK -> Meters.MeterId]
What I need to do is to read the first and last datapoint's DateTime and Value for each Meter - the result table should look like this:
Meter Name | First DateTime | First Value | Last DateTime | Last Value
-----------+----------------+-------------+---------------+-----------
I was able to write a query which would read min and max datapoint DateTimes for each Meter, but this query does not contain Value field:
select
Meters.Name as [Meter Name],
min(DataPoints.DateTime) as [First DateTime],
max(DataPoints.DateTime) as [Last DateTime]
from Meters
left join DataPoints on DataPoints.MeterId = Meters.MeterId
group by Meters.Name
How to modify this query so that First/Last Value fields would also be included in result?
Meters:
MeterId int [PK]
Name varchar
DataPoints:
DataPointId int [PK]
DateTime datetimeoffset
Value decimal
MeterId int [FK -> Meters.MeterId]
What I need to do is to read the first and last datapoint's DateTime and Value for each Meter - the result table should look like this:
Meter Name | First DateTime | First Value | Last DateTime | Last Value
-----------+----------------+-------------+---------------+-----------
I was able to write a query which would read min and max datapoint DateTimes for each Meter, but this query does not contain Value field:
select
Meters.Name as [Meter Name],
min(DataPoints.DateTime) as [First DateTime],
max(DataPoints.DateTime) as [Last DateTime]
from Meters
left join DataPoints on DataPoints.MeterId = Meters.MeterId
group by Meters.Name
How to modify this query so that First/Last Value fields would also be included in result?
Комментарии
Отправить комментарий