21 lines
812 B
SQL
21 lines
812 B
SQL
set search_path to kiss, public;
|
|
|
|
--\a
|
|
\C \t \f ;
|
|
|
|
\o ../data/projects_passiv.csv
|
|
select m2.trackid id1 , m1.trackid idx, m1.data->>'community' Gemeinde, m1.item Projekt,
|
|
m1.data->>'projectState' Status, m1.data->>'active' Aktiv,
|
|
to_char(m2.timestamp, 'DD.MM.YYYY') Datum1,
|
|
to_char(m1.timestamp, 'DD.MM.YYYY') Datumx
|
|
from messages_current m1, messages m2
|
|
where m1.domain='kiss' and m1.class='project'
|
|
and (m1.data->>'projectState')::integer in (30, 40)
|
|
--and m1.payload->>'active' is not null
|
|
and m2.trackid = (select min(trackid) from messages m3
|
|
where m3.domain = m1.domain and m3.action = m1.action
|
|
and m3.class = m1.class and m3.item = m1.item
|
|
and (m3.data->>'projectState')::integer in (30, 40))
|
|
order by m1.data->>'community', m1.item
|
|
--limit 20
|
|
--\g ../data/projects_passiv.csv
|