Just wondering if somebody already done this and is willing to share which reports were created and the queries. I guess this will be one of my night projects (yes, I have a context for these ;-))
Thanks
2 Likes
I’m interested in this as well. Wanted to do this for a long time, but can’t find the time 
1 Like
Please o please implement this as a feature!
I made a burn-down chart of sorts using Excel to execute the query and render a graph. The someday-maybe line isn’t accurate because I don’t think there is a way to historically query what was someday/maybe or next/focused on a given day. Not a biggie for me. This serves my primary need–to guilt myself into working more 
Uhh, I mean to keep me honest about how well I’m sticking to my system. You can see I’ve been bad lately.
with b as
(with recursive
dates(date) as (
values (date('now', '-30 days'))
union all
select date(date, '+1 day')
from dates
where date < date('now')
),
items as (select it.description as type,
il.description as list,
date(i.created_on, 'unixepoch') as created_on,
date(i.completed_on, 'unixepoch') as completed_on
from item i
join item_type it on (i.type = it.id)
join item_list il on (i.list = il.id)
where it.description in ('Action', 'Project')
and (il.description in ('Archived', 'Active/Next')
or ( -- a someday/maybe that was completed directly
il.description in ('Someday/Maybe')
and i.completed_on is not null
)
)
and i.id in (select item_id
from tagitem
where tag_id in (select id
from tag
where type = 'a'
and title = 'Professional'
))
)
select d.date,
i2.type,
i2.list,
i2.created_on,
i2.completed_on,
case
when (i2.created_on = d.date and ifnull(i2.completed_on, date('now', '+999 days')) > d.date) then 1
else 0 end as new,
case
when (i2.created_on < d.date and ifnull(i2.completed_on, date('now', '+999 days')) > d.date) then 1
else 0 end as backlog,
case when (i2.created_on < d.date and i2.completed_on = d.date) then 1 else 0 end as completed,
case when (i2.created_on = d.date and i2.completed_on = d.date) then 1 else 0 end as flux
from dates d
join items i2 on (d.date between i2.created_on and ifnull(i2.completed_on, date('now', '+999 days')))
)
select date,
sum(new) as new,
sum(flux) as flux,
sum(backlog) as backlog,
sum(completed) as completed,
(select count(*)
from item i
join item_type it on (i.type = it.id)
join item_list il on (i.list = il.id)
where it.description in ('Action', 'Project')
and (il.description in ('Someday/Maybe') and i.id in (select item_id
from tagitem
where tag_id in (select id
from tag
where type = 'a'
and title = 'Professional'
))
)
and date(i.created_on, 'unixepoch') <= date
)
as someday
from b
group by date
;
6 Likes