Anyone build their own statistics for Everdo using SQLLite queries

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 :slight_smile:

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 :wink: 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
;
5 Likes