CODEWITHSUNDEEP
ruby-on-railsruby
Kelvin
Kelvin

Reputation: 2288

Ruby on Rails query for a given set of dates

I am trying to write a query for my Rails API that will return the total number of hours of job applications that are accepted per month for a given set of dates.

Table: Job, JobApplication

Job has many JobApplication

JobApplication has a field status "SUCCESS" if matched

Start Date: Jul 16 2015

Is it possible to have it return {Jul 16: 100, Aug 16: 200, .... } ?

I am quite confused. Any help would be appreciated.

This query would give the number of successful Job Applications within a date range.

JobApplication.where(status: "SUCCESS").("created_at < ? ", Jul 16).("created_at < ? ", Aug 15)

JobApplication.job.duration would give the number of hours for that job.

I am not sure how to put them together and then loop through the dates.

My expect results is something like this:

{Jul 16: 100, Aug 16: 200, .... }

Upvotes: 0

Views: 62

Answers (2)

Vishal Jain
Vishal Jain

Reputation: 1940

Hey you can try this way:

JobApplication.joins(:job).where(status: "SUCCESS").where(job_applications => {:created_at => [state_date..end_date]}).group(:created_at).sum("jobs.duration")

It will Return you result like 

{Date => total_duration, :date => total_duration}

If You want you can convert date to specific format like DATE_FORMAT(created_at, "%d-%m-%y") so just put group(DATE_FORMAT(created_at, "%m-%y")). It will remove your time part from timestamp in mysql and returns data daywise

Upvotes: 1

Pascal
Pascal

Reputation: 8637

Something like 

data = JobApplication.where(status: 'SUCCESS').group(:start_date).select('start_date, count(*), sum(duration) as sum')

should do the trick, if i understood your question right. This will return JobApplication instances with sum set

Perhaps it is easier to run a pure SQL query:

SELECT start_date, count(*), sum(duration) FROM job_applications WHERE status LIKE 'SUCCESS' GROUP BY start_date

Upvotes: 0

Related Questions