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.
23.50 1363 1364 ANU
in your result should be24.00 1363 1364 ANU