Postgres SQL query

Question

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

greg · Accepted Answer · 2017-10-29 01:37:01Z

3

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.

edited Oct 29, 2017 at 1:37

answered Oct 27, 2017 at 8:19

greg

3,5351 gold badge27 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

yesemsanthoshkumar · Accepted Answer · 2017-10-27 03:51:46Z

0

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.

edited Oct 27, 2017 at 3:51

answered Oct 27, 2017 at 3:09

yesemsanthoshkumar

3602 silver badges9 bronze badges

3 Comments

Daryn Over a year ago

The table does not have equal date ranges. Have a look at the fourth reading.

Daryn Over a year ago

I think the problem I am facing is that I have multiple meters in the same table and I need to sort by meter. At the bottom of my question I will post my actual query I am running

yesemsanthoshkumar Over a year ago

Change row_number() over(order by read_date) to row_number() over(partition by meter_serial order by read_date) in your query

kjmerf · Accepted Answer · 2017-10-28 03:21:35Z

0

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

edited Oct 28, 2017 at 3:21

answered Oct 27, 2017 at 3:44

kjmerf

4,3853 gold badges24 silver badges29 bronze badges

2 Comments

Daryn Over a year ago

same result. I am only getting results where the days are only 1 day apart, but I need a range.

Daryn Over a year ago

I think its because I have more information that I need. e.g. different meters in the same table. have updated the question...

Collectives™ on Stack Overflow

Postgres SQL query

3 Answers 3

Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related