sql - extracting total days of a month and then use it to get average sales per day -

sql - extracting total days of a month and then use it to get average sales per day -

hi i've been working on project , need this.

select sf.order_qnt, dd.actual_date, dd.month_number sales_fact sf, date_dim dd dd.date_id = sf.date_id , dd.month_number = 1;

the result following:

order_qnt actual_date month_number ---------- ----------- ------------ 1100 05/01/13 1 100 05/01/13 1 140 06/01/13 1 110 07/01/13 1 200 08/01/13 1 500 08/01/13 1 230 08/01/13 1 500 08/01/13 1 200 08/01/13 1 53 15/01/13 1 53 22/01/13 1

now, want average month (average per day).

select sum(sf.order_qnt)/31 avgperday sales_fact sf, date_dim dd dd.date_id = sf.date_id , dd.month_number = 1;

the question is, instead of putting 31, how can total day of month? , how can apply select query. i'm pretty logic(c++), database pretty new me. i'm using oracle 11g way. give thanks help.

the question is, instead of putting 31, how can total day of month?

pick 1 solution :

1. can add together month date , substract both dates :

add_months(date_col, 1) - date_col

example :

sql> dates as( 2 select to_date('05/01/13','mm/dd/rr') dt dual union 3 select to_date('06/01/13','mm/dd/rr') dt dual union 4 select to_date('02/01/13','mm/dd/rr') dt dual) 5 select add_months(dt, 1) - dt num_of_days_per_month 6 dates 7 / num_of_days_per_month --------------------- 31 30 28

or,

you can extract lastly day of month :

extract(day last_day (date_col))

example :

sql> dates as( 2 select to_date('05/01/13','mm/dd/rr') dt dual union 3 select to_date('06/01/13','mm/dd/rr') dt dual union 4 select to_date('02/01/13','mm/dd/rr') dt dual) 5 select extract(day last_day(dt)) num_of_days_per_month 6 dates 7 / num_of_days_per_month --------------------- 31 30 28

sql oracle oracle11g report