Rails: select maximum date of each month

Question

In my app users save a report each Friday for an organization. All my records in the reports table hold organization_id, day and workdays.

I want to get the sum of all workdays at the last Friday of each month.

With the following line I am able to select all reports for organization #8 and sum workdays for each Friday:

@all_report_sums = Report.where(:organization_id => 8).group(:day).select("day, SUM(workdays) AS sum_workdays")

The result:

[{"id":null,"day":"2017-02-03","sum_workdays":3},{"id":null,"day":"2017-02-24","sum_workdays":33},{"id":null,"day":"2017-04-07","sum_workdays":12}]

However in my output I only want the maximum dates of each month (which is the last Friday of each month) - so in my case this would be 2017-02-24 and 2017-04-07.

How can I achieve that?

score 0 · Answer 1 · edited May 23 '17 at 12:25

0

This is what are you looking for

How to get the latest record from each group in ActiveRecord?

User.group(:user_id).having('day = MAX(day)')

edited May 23 '17 at 12:25

Community

1
1

answered Mar 07 '17 at 19:18

Hsiu Chuan Tsao

1,396
1
12
23

Unfortunately not. This gives me only the reports with maximum date for each user (so literally 1 report per year per user). What I want is for each user FOR EACH MONTH the latest report. So, literally, I want for each user 12 reports per year (maximum in Jan, Feb, Mar etc.). – Oliver Mar 07 '17 at 19:28
`User.group([:user_id, :day.beginning_of_month]).having('day = MAX(day)')` – Hsiu Chuan Tsao Mar 07 '17 at 19:55
undefined method `beginning_of_month' for :day:Symbol – Oliver Mar 07 '17 at 20:01
`User.group_by{|e| [e.user_id, e.day.beginning_of_month]}.having('day = MAX(day)')` – Hsiu Chuan Tsao Mar 07 '17 at 20:04
undefined method `having' for # – Oliver Mar 07 '17 at 20:06
`User.select(:user_id, "date_trunc('month', day) as month", :value).group(:user_id, :month).having('day = MAX(day)')` – Hsiu Chuan Tsao Mar 07 '17 at 20:19
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/137491/discussion-between-tsao-and-oliver). – Hsiu Chuan Tsao Mar 07 '17 at 20:32
I rephrased my question, maybe its better from this starting point. – Oliver Mar 07 '17 at 22:06

Rails: select maximum date of each month

1 Answers1