mardi 23 juin 2015

How to get number of invoices for last 12 weeks in Postgres

Invoice database contains invoice dates:

create table dok (
  dokumnr serial primary key,
  invoicedate date not null
);

Dashboard requires comma separated list containing number of invoices for last 12 weeks, e.q

4,8,0,6,7,6,0,6,0,4,5,6

List contains always 12 elements. If there are no invoices for some 7 day interval, 0 should appear. Every element should contain number of invoices for 7 days.

Query should find maximum date before current date:

select max(invoicedate) as last_date from dok;

And after that probably use count(*) and string_agg() to create list.

Last (12th) element should contain number of invoices for

  last_date .. last_date-interval'6days'

11 element (one before last) should contain number of invoices for days

  last_date-interval'7days' .. last_date-interval'14days'

etc.

How to write this query in Postgres 9.1+ ? This is ASP.NET MVC3 C# application and some parts of query can also done in C# code if this helps.

I ended with

with list as (

SELECT count(d.invoicedate) as cnt
   FROM  (
      SELECT max(invoicedate) AS last_date
      FROM   dok
      WHERE   invoicedate< current_date
      ) l
   CROSS  JOIN generate_series(0, 11*7, 7) AS g(days)
   LEFT   JOIN dok d ON d.invoicedate>  l.last_date - g.days - 7
                    AND d.invoicedate<= l.last_date - g.days
   GROUP  BY g.days
   ORDER  BY g.days desc
)

SELECT string_agg( cnt::text,',')
from list

Aucun commentaire:

Enregistrer un commentaire