0

Below is the query to find monthly average of number of records created. CREATED DATE IS TIMESTAMP

select year,month, (ymcount/maxmonthdays) as monthaverage ymcount  from (
select year,month,maxmonthdays, count(1) ymcount  from (
SELECT year(created) as year, MONTHNAME(created) as month,
day(date(date(created))-(day(date(created)) -1) days + 1 month -1 day)  as maxmonthdays 
FROM tablename )
group by year,month,maxmonthdays );

NOTE: To find max number of days in a month, I have used below command.

day(date(date(created))-(day(date(created)) -1) days + 1 month -1 day)  as maxmonthdays

One can use last_day function supported after DB2 version 9.7 onwards, I m using version 9.5.

Vinayak Dornala
  • 1,609
  • 1
  • 21
  • 27
  • 3
    What is your question? Also, can you assume that records are created every day or is the problem to count the number of days in the month? – Gordon Linoff Jun 12 '14 at 15:24
  • This question appears to be off-topic because it is not a question. – mustaccio Jun 12 '14 at 16:36
  • Your query will also ignore any indices on `created` (due to the use of the `YEAR(...)` and `MONTHNAME(...)` functions), making this slow for tables of real size. Do you have a [Calendar Table](http://stackoverflow.com/questions/5635594/how-to-create-a-calender-table-for-100-years-in-sql) (the question/answer is mostly for SQL Server, but the technique still applies)? If not you can generate one on-the-fly for a range... – Clockwork-Muse Jun 13 '14 at 03:10

0 Answers0