CODEWITHSUNDEEP
sqlpostgresql
Daryn
Daryn

Reputation: 1609

Postgres SQL query

If I have a table like follows:

Meter Serial    Date       |  Reading
    A          2017-01-01      10
    B          2017-02-10      20
    A          2017-03-05      20
    B          2017-05-01      100
    A          2017-06-01      300

Is it possible to get a query that displays like follows:

Meter   |    Date Start  |  Start Reading  |  Date End  |  End Reading
 A           2017-01-01          10          2017-03-05      20
 B           2017-02-10          20          2017-05-01      30
 A           2017-03-05          20          2017-06-01      300

Note: The readings do not come daily. But they are unique for a given day (e.g. you cannot have two readings on the same day)

This is the current query I am working with:

with tbl as (select row_number() over(order by read_date) as rn, meter_serial, meter_channel, total_meter_read, read_date
from meter_reading_total)
select l.meter_serial, l.read_date, l.total_meter_read, r.read_date, r.total_meter_read
from tbl as l
left outer join
tbl as r
on r.rn = l.rn + 1
and r.meter_serial = l.meter_serial
and r.meter_channel = l.meter_channel

Upvotes: 0

Views: 84

Answers (3)

greg
greg

Reputation: 3495

Window functions FTW

SELECT
  meter_serial,
  meter_date                     AS start_date,
  readings                       AS start_reads,
  lead(meter_date) OVER read_wdw AS end_date,
  lead(readings)   OVER read_wdw AS end_reads
FROM read_meter
WINDOW read_wdw AS (
  PARTITION BY meter_serial
  ORDER BY     meter_date ASC
)
ORDER BY start_date ASC

Here is the SQLFiddle for this case.

Upvotes: 3

kjmerf
kjmerf

Reputation: 4345

This one should work:

SELECT sub1.meter, sub1.date as date_start, sub1.reading as start_reading, sub2.meter, sub2.date as date_end, sub2.reading as end_reading
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY meter, date ASC) AS row, t.*
FROM t) sub1
INNER JOIN
(SELECT ROW_NUMBER() OVER(ORDER BY meter, date ASC) AS row, t.*
FROM t) sub2 ON sub1.row = sub2.row -1
AND sub1.meter = sub2.meter

Try it here: http://rextester.com/XZDOX35079

Upvotes: 0

yesemsanthoshkumar
yesemsanthoshkumar

Reputation: 360

select l.date as startdate, l.reading as startreading, r.date as 
enddate, r.endreading from
sample as l
left outer join
sample as r
on r.date = l.date + interval '1' day;

Self join by 1 day interval if you want it by dates. Else if you want by the record order change join condition to r.id = l.id + 1

If your table has no id column, you can use the rownumber function and cte as

with tbl as (select row_number() over(order by dt) as rn, rd, dt
from sample)
select l.dt, l.rd, r.dt, r.rd
from tbl as l
left outer join
tbl as r
on r.rn = l.rn + 1;

Changing order by dt to order by 1 will join the table at the date ranges even if they are not sorted.

Upvotes: 0

Related Questions