Say we need avg for 17 days from today, Employee_id Start_date Amount_by_day
123 08/28/2010 10 123 08/29/2010 10 123 08/30/2010 10 123 08/31/2010 10 123 09/01/2010 10 123 09/02/2010 10 Missing dates -> I need to add 2nd day amount for these dates 123 09/05/2010 10 Missing dates - I need to add 5th day amount for these dates 123 09/07/2010 10 Missing dates -> I need to add 7th day amount for these dates 123 09/11/2010 10 123 09/12/2010 10 123 09/13/2010 10 123 09/14/2010 10 123 09/15/2010 10 123 09/16/2010 10 123 09/17/2010 10 After getting all amount for missing dates take avg for 17 days. On Fri, Sep 17, 2010 at 5:16 PM, Javier Montani <jmont...@gmail.com> wrote: > Can you provide the requirements and an example? > > > 2010/9/17 swaroop gowda <swaroop.t...@gmail.com> > > I don't get all 21 days dates to calculate that. If there is missing date >> in between 21 days dates I have to copy previous day amount and then do a >> avg for 21 days. >> >> >> On Fri, Sep 17, 2010 at 5:05 PM, Javier Montani <jmont...@gmail.com>wrote: >> >>> If it's always 21 days, why don't you do SELECT *sum(amount) / 21* from >>> table where date between current_day - 21 and current_day. ? >>> >>> 2010/9/17 Michael Moore <michaeljmo...@gmail.com> >>> >>> 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 >>>> >>> >>> -- >>> 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 >>> >> >> >> >> -- >> 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 > -- 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
