I am trying to extract data from the activity log with this query:
-- [params]
-- int:lunar_months_ago
SELECT
REPLACE (raw, 'opened the door.', '') as member,
COUNT (DISTINCT DATE(created_at)) as days
FROM posts
WHERE topic_id = 2491
AND DATE(current_date) - DATE(created_at) < :lunar_months_ago*28
AND DATE(current_date) - DATE(created_at) > (:lunar_months_ago-1)*28
AND raw LIKE '%opened the door%'
GROUP BY raw
ORDER BY days DESC
Which very nicely spits out a list of members and how many days they visited the space in a 4 week period like so:
member days
Jo Atkin 23
Howard Batchen 19
Mark Johnson 15
Duncan Fairall 14
Tom Newsom 12
Paul Youthed 8
Tom Hedges 7
Andy Paine 7
…and so on
But what I’d like is multiple columns, for each integer value of lunar_months_ago so I don’t have to mess around copy and pasting and reconciling rows in a spreadsheet.
I would be tempted to include the name/number of the month in the select and group statements, and lengthen the time period in the where clause to go back as far as you are interested. So that should give you a long list of how many times a member has opened the door in each month.
You could then wrap that in either use SQL Pivot or Case statements to give you columns for each member name and month. Precise solution depends on your environment. This might be a useful resource:
I’m on phone - otherwise would try to write that in SQL. Happy to help later if I can!
A better way would be using the membership system as it captures the data as events of a specific ID, against a specific user with a timestamp.
Basically it’s really easy to throw this together as a page in your admin area, however you’ll need to first describe what you want, and second reinstate my access to the membership system.
I was going to say have you tried DROP TABLES IF EXISTS * - will make all the problems go away very quickly but I think @TomHedges suggestion is better
@tomnewsom - what environment are you working in? Will try not to suggest SQL commands that won’t work! @unknowndomain - I’d be interested to learn more about the membership system - perhaps you could give me a tour one evening?
It sounds like a meeting with @unknowndomain@systems and interested parties might be productive to get the membership system documented and handed over. Or is this already in hand?
I’ve had a quick go at this for displaying the previous 4 months. It should be easy enough to expand for more. I normally write in t-sql (microsofts flavour of sql) so I’m probably a little off but think this will get you what you need.
Give me a shout if this doesn’t work I’m happy to trouble shoot!
select
replace(raw ,' opened the door.') ,'') as member
,count(distinct case when 0*28 < date(current_date) - date(created_at) and date(current_date) - date(created_at) <= 1*28 then date(current_date) else null end) as within_last_month
,count(distinct case when 1*28 < date(current_date) - date(created_at) and date(current_date) - date(created_at) <= 2*28 then date(current_date) else null end) as one_month
,count(distinct case when 2*28 < date(current_date) - date(created_at) and date(current_date) - date(created_at) <= 3*28 then date(current_date) else null end) as two_month
,count(distinct case when 3*28 < date(current_date) - date(created_at) and date(current_date) - date(created_at) <= 4*28 then date(current_date) else null end) as three_month
,count(distinct case when 4*28 < date(current_date) - date(created_at) then date(current_date) else null end) as four_month_and_more
from
posts
where
topic_id = 2491
and raw like '%opened the door.'
group by
raw
