oracle sql doubt - experts plz

[KakiJanaky]

bhayya,

I wanted to capture 3 months prior data. For example, if am running my report today, I will be running for last month data. So one of my field needs average where I have to get numerator/avg(denominator). I am good with numerator. 

So for denominator, I need 2,3,4 months back

For example: September = my numerator/avg(May,June,July).   --Note: in this case we should leave aug month.

So, please tell me if you can, how to capture previous 3 months data leaving 1st prior month.

Thanks for your help. 

[chicchara]

aa average ni inko column lo call cheskodame. the table u are accessing is it a hist table? or does it have snapshots of all those months?

[KakiJanaky]

4 minutes ago, chicchara said:

aa average ni inko column lo call cheskodame. the table u are accessing is it a hist table? or does it have snapshots of all those months?

no it is not. It is just a normal table

[chicchara]

but history data aa table lo lekapotey how can u get older months data? are u looking at a date column to decide if its may june or july ani ?

[Suhaas]

Bro, Can you sql fiddle your schema and query?

May be, we can use LEAD/LAG function but I am not entirely sure I understand the requirement. 

 

[KakiJanaky]

1 hour ago, chicchara said:

but history data aa table lo lekapotey how can u get older months data? are u looking at a date column to decide if its may june or july ani ?

yes i have to look at the post date to verify the data is being pulled for properly

[Megacamp]

1 hour ago, KakiJanaky said:

bhayya,

I wanted to capture 3 months prior data. For example, if am running my report today, I will be running for last month data. So one of my field needs average where I have to get numerator/avg(denominator). I am good with numerator. 

So for denominator, I need 2,3,4 months back

For example: September = my numerator/avg(May,June,July).   --Note: in this case we should leave aug month.

So, please tell me if you can, how to capture previous 3 months data leaving 1st prior month.

Thanks for your help. 

Why dont you try sysdate -3*months

[Megacamp]

ex : LAST_UPDATED_DATE >= SYSDATE -120 it will give last 4 months data 

[former]

2 hours ago, KakiJanaky said:

bhayya,

I wanted to capture 3 months prior data. For example, if am running my report today, I will be running for last month data. So one of my field needs average where I have to get numerator/avg(denominator). I am good with numerator. 

So for denominator, I need 2,3,4 months back

For example: September = my numerator/avg(May,June,July).   --Note: in this case we should leave aug month.

So, please tell me if you can, how to capture previous 3 months data leaving 1st prior month.

Thanks for your help. 

Rolling 3-Months.

Do you have Rolling calendar table in your DB? If Yes, you can write Denominator in separate SubQuery & need to join back to ur Numerator?

Which DB type ur using Oracle or MS SQL Server?

[KakiJanaky]

26 minutes ago, former said:

Rolling 3-Months.

Do you have Rolling calendar table in your DB? If Yes, you can write Denominator in separate SubQuery & need to join back to ur Numerator?

Which DB type ur using Oracle or MS SQL Server?

Orcale bhayya

[KakiJanaky]

47 minutes ago, Megacamp said:

ex : LAST_UPDATED_DATE >= SYSDATE -120 it will give last 4 months data 

Like I said, I have to skip last month of the month we are running for and get all 3 months before that.

For example: September = my numerator/avg(May,June,July).   --Note: in this case we should leave aug month.

 

[CheGuevara]

Select a.numerator/b.denom from table a, (

Select unique_key_col, sum(column_name)/3 as denom from table where date_column between sysdate -30 and sysdate-120

group by unique_key_col) b where a.unique_key_col = b.unique_key_col;

 

edi edo rough idea.. konchem kastam without table desc but change it according to your table structure

[former]

44 minutes ago, KakiJanaky said:

Orcale bhayya

How is your table looks like ??

[former]

Just now, former said:

How is your table looks like ??

Do you have date field or month Field??

[former]

Try with Analytic functions and check sample below one:

 

 Query with a RANGE windowing clause that uses the BETWEEN and FOLLOWING parameters 

SQL> select last_name, first_name, department_id, hire_date, salary,
  2     SUM (salary)
  3    OVER (PARTITION BY department_id ORDER BY hire_date
  4          RANGE BETWEEN 365 PRECEDING AND 365 FOLLOWING) department_total
  5  from employee
  6  order by department_id, hire_date;

LAST_NAME FIRST_NAME       DEPARTMENT_ID  HIRE_DATE   SALARY DEPARTMENT_TOTAL
————————— ———————————— ————————————————— —————————— ———————— ———————————————— 
Eckhardt   Emily                      10 07-JUL-04   100000           100000
Newton     Donald                     10 24-SEP-06    80000           270000
James      Betsy                      10 16-MAY-07    60000           270000
Friedli    Roger                      10 16-MAY-07    60000           270000
Michaels   Matthew                    10 16-MAY-07    70000           270000
Dovichi    Lori                       10 07-JUL-11
peterson   michael                    20 03-NOV-08    90000           155000
leblanc    mark                       20 06-MAR-09    65000           155000
Jeffrey    Thomas                     30 27-FEB-10   300000           370000
Wong       Theresa                    30 27-FEB-10    70000           370000
Newton     Frances                       14-SEP-05    75000            75000