how to get missing months

Hi, i am using a Rails count method like this : start_date = Date.civil(y=2008,m=1) @users_count = User.count(:all, :conditions => ["last_updated BETWEEN ? and ?", start_date.>>(Date.today.month - 8), Date.today], :group => "DATE_FORMAT(last_updated, '%b %Y')", :order => :last_updated)

getting output like this

[["Jan 2008", 30], ["Feb 2008", 60], ["Mar 2008", 70], ["May 2008", 46], ["Jun 2008", 33], ["Aug 2008", 10]]

but i was missing the months Apr,Jul when there were no records in that months Can we get the 2 months when there was no data in that months as ["Apr 2008", nil], ["Jul 2008", nil] or in any way ?

any help ??

thanks

i am using a Rails count method like this : start_date = Date.civil(y=2008,m=1) @users_count = User.count(:all, :conditions => ["last_updated BETWEEN ? and ?", start_date.>>(Date.today.month - 8), Date.today], :group => "DATE_FORMAT(last_updated, '%b %Y')", :order => :last_updated)

getting output like this

[["Jan 2008", 30], ["Feb 2008", 60], ["Mar 2008", 70], ["May 2008", 46], ["Jun 2008", 33], ["Aug 2008", 10]]

but i was missing the months Apr,Jul when there were no records in that months Can we get the 2 months when there was no data in that months as ["Apr 2008", nil], ["Jul 2008", nil] or in any way ?

any help ??

Don't try to do it in the query itself, just post-process in Ruby:

module DateStepMonth   def succ     (Date.civil(year, month, -1) + 1).extend(DateStepMonth)   end end

first_month = Date.strptime(@users_count.first.first, "%b %Y") last_month = Date.strptime(@users_count.last.first, "%b %Y") first_month.extend(DateStepMonth) last_month.extend(DateStepMonth) hash_counts = @user_counts.inject({}) { |h,(month,count)|   h[Date.strptime(month, "%b %Y")] = count; h } @user_counts = (first_month..last_month).map { |month|   [ month.strftime("%b %Y"), hash_counts(month) ] }

thanks

--Greg

Gregory Seidman wrote:

but i was missing the months Apr,Jul when there were no records in that months Can we get the 2 months when there was no data in that months as ["Apr 2008", nil], ["Jul 2008", nil] or in any way ?

any help ??

Don't try to do it in the query itself, just post-process in Ruby:

module DateStepMonth   def succ     (Date.civil(year, month, -1) + 1).extend(DateStepMonth)   end end

first_month = Date.strptime(@users_count.first.first, "%b %Y") last_month = Date.strptime(@users_count.last.first, "%b %Y") first_month.extend(DateStepMonth) last_month.extend(DateStepMonth) hash_counts = @user_counts.inject({}) { |h,(month,count)|   h[Date.strptime(month, "%b %Y")] = count; h } @user_counts = (first_month..last_month).map { |month|   [ month.strftime("%b %Y"), hash_counts(month) ] }

thanks

--Greg

hi Greg thanks for the Reply,

but i was geting error

3 elements of civil date are necessary

RAILS_ROOT: script/../config/.. Application Trace | Framework Trace | Full Trace

/usr/lib/ruby/1.8/date.rb:650:in `new_with_hash' /usr/lib/ruby/1.8/date.rb:675:in `strptime' app/models/report.rb:51:in `track_users_chart' app/controllers/reporting_controller.rb:29:in `track_users' -e:4:in `load' -e:4 any idea ??

thanks , srinath

Gregory Seidman wrote: >> >> but i was missing the months Apr,Jul when there were no records in that >> months >> Can we get the 2 months when there was no data in that months as ["Apr >> 2008", nil], ["Jul 2008", nil] or in any way ? >> >> any help ?? > > Don't try to do it in the query itself, just post-process in Ruby: > > module DateStepMonth > def succ > (Date.civil(year, month, -1) + 1).extend(DateStepMonth) > end > end > > first_month = Date.strptime(@users_count.first.first, "%b %Y") > last_month = Date.strptime(@users_count.last.first, "%b %Y") > first_month.extend(DateStepMonth) > last_month.extend(DateStepMonth) > hash_counts = @user_counts.inject({}) { |h,(month,count)| > h[Date.strptime(month, "%b %Y")] = count; h > } > @user_counts = (first_month..last_month).map { |month| > [ month.strftime("%b %Y"), hash_counts(month) ] > } > >> thanks > --Greg

hi Greg thanks for the Reply,

but i was geting error

3 elements of civil date are necessary

RAILS_ROOT: script/../config/.. Application Trace | Framework Trace | Full Trace

/usr/lib/ruby/1.8/date.rb:650:in `new_with_hash' /usr/lib/ruby/1.8/date.rb:675:in `strptime' app/models/report.rb:51:in `track_users_chart' app/controllers/reporting_controller.rb:29:in `track_users' -e:4:in `load' -e:4 any idea ??

What version of Ruby are you using? I tested this in 1.8.6. Maybe strptime needs a day of the month as well in the version you have. Try adding " 1" to the end of the string being parsed and " %d" to the end of the format string.

thanks , srinath

--Greg