Current Position:Home > Average of a field between two dates

Average of a field between two dates

Update:11-30Source: network consolidation
Advertisement
Hi,
I have a table with two fields. Date Field and Percentage field. For ex,
Date Percentage
1/1/2006 11
1/4/2006 23
2/4/2006 34
11/11/2006 354
1/4/2007 75
10/09/2007 67
2/3/2008 876
I want to find the average(sum of percentage/total no) of Percentage field between two dates.
I am passing only the end date. And, the start date I have to find according to the end date.
For ex,if I am passing the end date as 10/09/2007, starting date should be 1/4/2007.
And, if I am passing the end date as 11/11/2006, starting date should be 1/4/2006.
For end date 2/3/2008, start date should be 1/4/2007.
Starting date is taken as by considering the year end as April to March. So, I have to retrieve the average of the percentage field from April to the selected date as end date.
thanks

The Best Answer

Advertisement
Getting the sum, number of rows and average since the last 1st of April to the current date :
SQL> with tbl as
  2  (select to_date('01/01/2006','dd/mm/yyyy') as dt, 11  prct from dual union all
  3   select to_date('01/04/2006','dd/mm/yyyy') as dt, 23  prct from dual union all
  4   select to_date('02/04/2006','dd/mm/yyyy') as dt, 34  prct from dual union all
  5   select to_date('11/11/2006','dd/mm/yyyy') as dt, 354 prct from dual union all
  6   select to_date('01/04/2007','dd/mm/yyyy') as dt, 75  prct from dual union all
  7   select to_date('10/09/2007','dd/mm/yyyy') as dt, 67  prct from dual union all
  8   select to_date('02/03/2008','dd/mm/yyyy') as dt, 876 prct from dual)
  9  select dt, prct,
10         sum(prct) over (order by dt range between dt-add_months(trunc(add_months(dt,-3),'yyyy'),3) preceding and current row) sum_prct,
11         count(prct) over (order by dt range between dt-add_months(trunc(add_months(dt,-3),'yyyy'),3) preceding and current row) nb_prct,
12         avg(prct) over (order by dt range between dt-add_months(trunc(add_months(dt,-3),'yyyy'),3) preceding and current row) avg_prct
13  from tbl;
DT             PRCT   SUM_PRCT    NB_PRCT   AVG_PRCT
01/01/06         11         11          1         11
01/04/06         23         23          1         23
02/04/06         34         57          2       28,5
11/11/06        354        411          3        137
01/04/07         75         75          1         75
10/09/07         67        142          2         71
02/03/08        876       1018          3 339,333333
7 rows selected.Nicolas.
  • Average of a field between two dates Update:11-30

    Hi, I have a table with two fields. Date Field and Percentage field. For ex, Date Percentage 1/1/2006 11 1/4/2006 23 2/4/2006 34 11/11/2006 354 1/4/2007 75 10/09/2007 67 2/3/2008 876 I want to find the average(sum of percentage/total no) of Percentag

  • Start Routine to Populate Account Group Field from Master data of 0Customer Update:10-11

    Hello Friends. Please help me edit this ABAP code to make it work. I am putting this code in start routine in between two DSO. where I am using the Start Routine to Populate Account Group Field from Master data of 0Customer. I do not want to use read

  • Fields missing from Data Source but displayed in RSA3 Update:10-11

    Hello Experts I have an issue while finding for a field in the data source. As I am looking for some fields : MABST, MINBE, EISBE on BW Side but not found. When I llok the same fields in ECC Side in the table RSOSFieldmap, then I get these fields in

  • Expanding text field to hold data Update:10-11

    This sounds like a pretty simple problem, but... I have created a form using Designer that consists for 4 text boxes that span the width of the page. We are using a middleware product, LogiXML, to populate the form. We have successfully accomplished

  • Adding new field to Master Data Update:10-11

    Hi All, I have one master data which is business content. i need to add one custom field in that with the status information of records. that new field will get data from R/3 status table via extractor from CMOD. now this Master data is having delta.

  • Newly added field in the data Source not getting populated Update:10-11

    Hello All, We have added few fields in the Data Source. The Data Source is based on the InfoSet. We have included the field in the Infoset and have updated the code to fetch the value for the newly added fields. When we perform the test extraction fo

  • MDM JAVA API to enter value into field of type date Update:10-11

    Hi All, I am having an input field in a typical Dynpro application where a date would be entered.I want to enter  the date in my MDM table in a field of type "Date".Do you have any idea about MDM JAVA API's I need to do this.Also how can I do th

  • How to access a field of an data source w/o binding a form field to it Update:10-11

    Hi   This is a query regarding ADOBE print form.   How to access a record field (of an data source.... typical multi row records) where the record is bound to a subform. Data is something like (I have used abap parlance for understanding) data : begi

  • Custom field in Basic data screen of invoice entry screen Update:10-11

    hello, I want to add custom fields in basic data screen of Invoice entry in SRM 5.0. As invoice Entry screen is divided into three parts: 1. Basic data Screen 2. Header data screen 3. Item data screen Now according to OSS Note number 672960 we are ab

  • How to add Fields in Additional Data B in VA01/VA02.PLease help me out. Update:10-11

    HI All, My requirement to add a Custom Screen in Va01/VA02 .Kindly suggest if any screen exists are there. How to Add extra fields in Additional Data B in header of VA01/VA02.is access key is neccessary for that. Thanks in  Advance.Hi Check the link