Query with dynamic ranges in PostgreSQL based on status

Question

We have a table with invoices classified per day, with normal invoices and void invoices (invoices that are voided, in our country we use the Spanish term "anuladas"). For example suppose that we have the next list of invoices for today (where APL stands for Applied and ANU for Void or Anulada):

ID     TOTAL       STATUS     DATETIME            INVOICE_NUMBER

1      00.50       APL        2014-07-04 07:35    1355
2      10.50       APL        2014-07-04 07:36    1356
3      12.50       APL        2014-07-04 07:38    1357
4      10.50       ANU        2014-07-04 07:35    1358
5      10.50       ANU        2014-07-04 07:45    1359
6      09.50       APL        2014-07-04 07:55    1360
7      10.50       APL        2014-07-04 08:15    1361
8      10.50       APL        2014-07-04 08:25    1362
9      13.50       ANU        2014-07-04 08:35    1363
10     10.50       ANU        2014-07-04 08:45    1364
11     10.50       APL        2014-07-04 09:05    1365
12     14.50       APL        2014-07-04 09:15    1366
13     10.50       ANU        2014-07-04 09:16    1367
14     19.50       APL        2014-07-04 09:37    1368
15     10.50       ANU        2014-07-04 10:45    1369

This is a sample information for one day. In my report I have to show all the sales but as ranges, per day. I don't have problem with getting the information for each day. The problem is that, in this example, for today July 7th, I have 15 sales and it has to show like this:

TOTAL       INITIAL CORRELATIVE        ENDING CORRELATIVE     STATUS

23.50            1355                     1357                 APL
21.00            1358                     1359                 ANU
30.50            1360                     1362                 APL
23.50            1363                     1364                 ANU
25.00            1365                     1366                 APL
10.50            1367                     1367                 ANU
19.50            1368                     1368                 APL
10.50            1369                     1369                 ANU

As you can see it groups the first three sales as a range based on correlatives, but when we have one or more void invoices, it cuts the range and inserts a range of void invoices (1358 to 1359) then continues to the next range.

[UPDATE]: After Erwin's right answer I leave the query that we coded according to his:

SELECT sum(total)      AS total
  ,min(num_documento) AS initial_correlative
  ,max(num_documento) AS ending_correlative
  ,estado,grp
  ,count(1) AS ct
FROM  (
   SELECT *, count(step OR NULL) OVER (ORDER BY vtaprsv_id) AS grp
   FROM  (
      SELECT *, lag(estado) OVER (ORDER BY vtaprsv_id) IS DISTINCT FROM estado AS step
      FROM  (
         SELECT total, num_documento, vtaprsv_id, fecha_venta 
              , CASE estado WHEN 'PEN' THEN 'PGD' WHEN 'APL' THEN 'PGD' ELSE estado END estado
         FROM   venta_variada
         WHERE  fecha_venta BETWEEN '2014-07-02 00:00:00' AND '2014-07-02 23:59:59'
         ) consi
      ) x
   ORDER BY fecha_venta
   ) y
GROUP  BY grp, estado
ORDER  BY grp;

Hope it helps somebody else.

Answer 1

A textbook case for window functions.
Building on a few assumptions, it could look like this:

SELECT grp
      ,count(*) AS ct
      ,sum(total) AS total
      ,min(invoice_number) AS initial_correlative
      ,max(invoice_number) AS ending_correlative
      ,status
FROM  (
   SELECT *, count(step OR NULL) OVER (ORDER BY id) AS grp
   FROM  (
      SELECT *, lag(status) OVER (ORDER BY id) IS DISTINCT FROM status AS step
      FROM   tbl
      ) x
   ORDER BY datetime
   ) y
GROUP  BY grp, status
ORDER  BY grp;

Subquery x marks each beginning of a new group (step).
Subquery y counts beginnings to create a unique number for each group (grp).
Then you can GROUP BY this number in outer query. status is the same for each group per definition.

SQL Fiddle.

Related answers:
- Select longest continuous sequence
- On SO: Group By and Aggregate Sequential Numeric Values