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