3

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.

  • So what are the desired results?Anu at the end for each day? – Mihai Jul 4 '14 at 18:57
  • Did you try any query yourself? Why didn't it work? Or do you expect us to write it for you? – Colin 't Hart Jul 4 '14 at 19:44
  • Let me explain @Mihai. The first 3 invoices have a status of APL or applied. Then we have 2 invoices with status of ANU or void (or "anuladas" translated in spanish). So, in the report, those 3 first invoices are shown as a rang from the first invoice to the third, because all are APL, and it summarizes the totals of the three. Then, because i have 2 ANU, those are grouped as a range from the 4th to the 5th invoice, and so on. – Oscar Calderon Jul 4 '14 at 21:15
  • Well @Colin'tHart i just want some orientation if there's a function or something like that in PostgreSQL, unfortunately my PostgreSQL knowledge isn't so advanced so right know i can't figure out if there's a function to do that or a way to write the query, also i don't know how to search for it in Google, i tried using words like custom range Postgresql and things like that but the results aren't what i'm looking for. But, i just want a guide or a tip and then i'm going to write the query. – Oscar Calderon Jul 4 '14 at 21:17
  • 23.50 1363 1364 ANU in your result should be 24.00 1363 1364 ANU – Erwin Brandstetter Jul 5 '14 at 1:01
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

  • 1
    Thank you Erwin, it worked like a charm! We just added some filters to filter per day and it works excellent, the query is fast too. Thank you so much, i didn't know those functions (i'm talking about OVER and LAG and IS DISTINCT FROM). Unfortunately i cannot vote your answer yet because of my low reputation, but thank you so much again. – Oscar Calderon Jul 7 '14 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.