Hi,
there is a vast log-table that collects several state data for objects.
(log_id, project_fk, object_fk, state_fk, log_type_fk,
created_on::timestamp, .......)
log_id is a sequence,
project_fk foreign key on a project-table
object_fk foreign key on a object-table
state_fk can have 10 values 0, 10, 20, 30, ...
log_type_fk describes the event that caused the entry
I need counts of states of objects for a project starting at a given
date t0 in 14 days distances.
Because I need a row for every reporting day, I started out by creating
a view that selects the relevant project_fk and only those log_type_fk
that MIGHT be relevant. state_fk = 0 is irrelevant, too.
The same view does a case when ... for every state_fk so that I can add
them up later to get a cross-table.
e.g.
case when state_fk = 10 then 1 else 0 end as sate_10,
case when state_fk = 20 then 1 else 0 end as sate_20,
...
Then the view adds a integer-column period_nr that represents the nr
of 14 day periods since t0.
In the first 14 days have period_nr = 0, in the second 14 days it is 1
and so on.
Now I need a query that calculates the sum for every column state_10,
state_20, ..., state_90 from t0 to the current period_nr.
t0 until t0 + 1 * 14 days ===> count(state_10), count(state_20),
count(state_30) ...
t0 until t0 + 2 * 14 days
...
This would be nice.
I'd be glad if you could hint me up to here.
Even nicer would be a solution that adds just the last occurance for
every object_fk within the current t0 - period.
e.g.
object_fk = 42 might appear
in period 1 with state 50
in period 3 twice with state 40 and 20
The report should count it in period 1+2 as 50
and in period 3 and further just 1 time as 20 until the object gets
logged again.
This might prove to be a wee bit tricky.
