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


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'


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