So, which is it? Do you need to "copy sep13" or do you need to "put zero"?
Mike The term you need to learn is "data-densification". There are several techniques used to perform data-densification. I suggest you read here: http://www.oracle-developer.net/display.php?id=312 Also, you can take the below query and LEFT OUTER JOIN it to your query and substitute 0 (zero) for cases when the LEFT OUTER JOIN returns NULL; SELECT TO_DATE ('20081101', 'yyyymmdd') + LEVEL - 1 day_date FROM DUAL CONNECT BY LEVEL <= TO_NUMBER ( TO_CHAR (LAST_DAY (TO_DATE ('20081101', 'yyyymmdd')), 'DD')); On Fri, Sep 17, 2010 at 2:19 PM, swaroop gowda <swaroop.t...@gmail.com>wrote: > Hi, > > I am trying calculate average amount for 21 days, but the date which is > available is not continues. > It can be like Sep 17, sep16, sep13, sep 12, sep10 etc... > > I tried to do like this SELECT avg(amount) from table where date between > current_day - 21 and current_day. > > What I need, I need to add zero or last day amount to missing dates like in > above dates we missed sep14 and sep 15 for this I need to copy sep13 amouth > and take avg for 21 days r I have to put zero for these missing dates. > > Please help me. > > -- > Thanks & Regards > Swaroop Thailuru Swamy > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to Oracle-PLSQL@googlegroups.com > To unsubscribe from this group, send email to > oracle-plsql-unsubscr...@googlegroups.com > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
