Same thing but with AVG:
/* Formatted on 9/17/2010 3:58:57 PM (QP5 v5.149.1003.31008) */
WITH mytab AS ( SELECT day_date, amt
FROM ( SELECT (SELECT MIN (the_date) FROM roop)
+ LEVEL
- 1
day_date
FROM DUAL
CONNECT BY LEVEL <=
(SELECT MAX (the_date)
- MIN (the_date)
FROM roop))
LEFT OUTER JOIN
roop
ON day_date = the_date
ORDER BY day_date)
SELECT AVG(
CASE
WHEN amt IS NOT NULL THEN amt
ELSE (SELECT amt
FROM roop r1
WHERE the_date < mt0.day_date
AND NOT EXISTS
(SELECT 1
FROM roop r2
WHERE r2.the_date < r1.the_date))
END ) averageamt
FROM mytab mt0;
On Fri, Sep 17, 2010 at 4:01 PM, Michael Moore <michaeljmo...@gmail.com>wrote:
> create table roop as
> (select to_date('08/10/2010','mm/dd/yyyy') the_date, 12 amt from dual
> union all
> select to_date('08/12/2010','mm/dd/yyyy') the_date, 13 amt from dual
> union all
> select to_date('08/13/2010','mm/dd/yyyy') the_date, 8 amt from dual
> union all
> select to_date('08/16/2010','mm/dd/yyyy') the_date, 2 amt from dual
> union all
> select to_date('08/17/2010','mm/dd/yyyy') the_date, 6 amt from dual);
>
> /* Formatted on 9/17/2010 3:58:57 PM (QP5 v5.149.1003.31008) */
> WITH mytab AS ( SELECT day_date, amt
> FROM ( SELECT (SELECT MIN (the_date) FROM roop)
> + LEVEL
> - 1
> day_date
> FROM DUAL
> CONNECT BY LEVEL <=
> (SELECT MAX (the_date)
> - MIN (the_date)
> FROM roop))
> LEFT OUTER JOIN
> roop
> ON day_date = the_date
> ORDER BY day_date)
> SELECT day_date,
> CASE
> WHEN amt IS NOT NULL THEN amt
> ELSE (SELECT amt
> FROM roop r1
> WHERE the_date < mt0.day_date
> AND NOT EXISTS
> (SELECT 1
> FROM roop r2
> WHERE r2.the_date < r1.the_date))
> END amt
> FROM mytab mt0;
>
>
>
>
> On Fri, Sep 17, 2010 at 3:30 PM, swaroop gowda <swaroop.t...@gmail.com>wrote:
>
>> 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
>>
>
>
--
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
