Count data per day in a specific month in Postgresql

Question

I have a table with a create date called created_at and a delete date called delete_at for each record. If the record was deleted, the field save that date; it's a logic delete.

I need to count the active records in a specific month. To understand what is an active record for me, let's see an example:

For this example we'll use this hypothetical record:

id | created_at | deleted_at
1  | 23-01-2014 | 05-06-2014

This record is active for every days between its creation date and delete date. Including that last. So if I need count the active record for March, in this case, this record must be counted in every days of that month.

I have a query (really easy to do) that show the actives records for a specific month, but my principal problem is how to count that actives for each day in that month.

SELECT
  date_trunc('day', created_at) AS dia_creacion,
  date_trunc('day', deleted_at) AS dia_eliminacion
FROM
  myTable
WHERE
  created_at < TO_DATE('01-04-2014', 'DD-MM-YYYY')
  AND (deleted_at IS NULL OR deleted_at >= TO_DATE('01-03-2014', 'DD-MM-YYYY'))

possible duplicate of [PostgreSQL query to count/group by day and display days with no data](http://stackoverflow.com/questions/15691127/postgresql-query-to-count-group-by-day-and-display-days-with-no-data) — Jakub Kania, Jun 02 '15 at 21:29
That's a solution for a simple count of records. In this case we must remember the `active records`. — Pistorius, Jun 02 '15 at 21:38
What is the expected output you want to have? Just the number of days? — , Jun 02 '15 at 21:40
I expected as output the number of `active records` per day. — Pistorius, Jun 02 '15 at 21:42
I think it's the same solution and all you need is a diffrent join. — Jakub Kania, Jun 03 '15 at 07:09

score 1 · Accepted Answer · answered Jun 02 '15 at 23:30

Here you are:

select 
  TO_DATE('01-03-2014', 'DD-MM-YYYY') + g.i, 
  count( case (TO_DATE('01-03-2014', 'DD-MM-YYYY') + g.i) between created_at and coalesce(deleted_at, TO_DATE('01-03-2014', 'DD-MM-YYYY') + g.i)
    when true then 1
    else null
  end)
from generate_series(0, TO_DATE('01-04-2014', 'DD-MM-YYYY') - TO_DATE('01-03-2014', 'DD-MM-YYYY')) as g(i)
left join myTable on true
group by 1
order by 1;

You can add more specific condition for joining only relevant records from myTable, but even without it gives you idea how to achieve counting as desired.

Count data per day in a specific month in Postgresql

1 Answers1