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.