| How to fetch data based on the given criteria..... [message #565245] |
Sat, 01 September 2012 05:16  |
 |
shivakm
Messages: 5
Registered: September 2012
Location: Muscat, Sultanate of Oman
|
Junior Member |
|
|
Dear All,
I am a bit new to oracle, Please can some help me.
I need to design a report out of the below data:
1. bprf_no will be my primary field
2. report parameters will be the bill_month & no_of_months
Based on above 2 parameters I need to scan through the data for BILL_MONTH <= '20-Jun-2012' and NO_OF_MONTHS <= 6 the other criteria being the AVG_IND in (1,2).
In brief the criteria will be to pick all BPRF_NO having AVG_ID in (1, 2) consecutively till a break (AVG_IND not in (1, 2) starting from the given BILL_MONTH and going below this period, that is BILL_MONTH <= '30-Jun-2012'.
For the below data, if my parameters are: BILL_MONTH <= '30-Jun-2012' and NO_OF_MONTHS <= 3, only the underscore added data should be picked (as they fulfill the criteria) and the report output will be like:
BPRF_NO BILL_MONTH NO_OF_TIMES
-------------------------------------------------
BP05 30-Jun-2012 6
BP06 30-Jun-2012 6
BP07 30-Jun-2012 6
BP08 30-Jun-2012 6
Here the NO_OF_TIMES is the count, that is no of times the BPRF_NO falls into the above mentioned criteria consecutively starting from the provided BILL_MONTH.
BPRF_NO BILL_MONTH VOID_STATUS AVG_IND
------- ----------- ------------ --------
BP01 30-Jun-12 0 0
BP02 30-Jun-12 0 0
BP03 30-Jun-12 0 1
BP04 30-Jun-12 0 1
_BP05 30-Jun-12 0 1_
_BP06 30-Jun-12 0 2_
_BP07 30-Jun-12 0 2_
_BP08 30-Jun-12 0 2_
BP09 30-Jun-12 0 0
BP10 30-Jun-12 0 0
BP10 30-Jun-12 0 0
BP01 31-May-12 0 0
BP02 31-May-12 0 0
BP03 31-May-12 0 1
BP04 31-May-12 0 1
_BP05 31-May-12 0 1_
_BP06 31-May-12 0 2_
_BP07 31-May-12 0 2_
_BP08 31-May-12 0 2_
BP09 31-May-12 0 0
BP10 31-May-12 0 0
BP01 30-Apr-12 0 0
BP02 30-Apr-12 0 0
BP03 30-Apr-12 0 0
BP04 30-Apr-12 0 0
_BP05 30-Apr-12 0 1_
_BP06 30-Apr-12 0 2_
_BP07 30-Apr-12 0 2_
_BP08 30-Apr-12 0 2_
BP09 30-Apr-12 0 0
BP10 30-Apr-12 0 0
BP01 31-Mar-12 0 0
BP02 31-Mar-12 0 0
BP03 31-Mar-12 0 1
BP04 31-Mar-12 0 1
_BP05 31-Mar-12 0 1_
_BP06 31-Mar-12 0 2_
_BP07 31-Mar-12 0 2_
_BP08 31-Mar-12 0 2_
BP09 31-Mar-12 0 0
BP10 31-Mar-12 0 0
BP01 29-Feb-12 0 0
BP02 29-Feb-12 0 0
BP03 29-Feb-12 0 1
BP04 29-Feb-12 0 1
_BP05 29-Feb-12 0 1_
_BP06 29-Feb-12 0 2_
_BP07 29-Feb-12 0 2_
_BP08 29-Feb-12 0 2_
BP09 29-Feb-12 0 0
BP10 29-Feb-12 0 0
BP01 31-Jan-12 0 0
BP02 31-Jan-12 0 0
BP03 31-Jan-12 0 0
BP04 31-Jan-12 0 0
_BP05 31-Jan-12 0 1_
_BP06 31-Jan-12 0 2_
_BP07 31-Jan-12 0 2_
_BP08 31-Jan-12 0 2_
BP09 31-Jan-12 0 0
BP10 31-Jan-12 0 0
My below query is fetching me wrong data:
----- Query -----
select bprf_no, no_of_month--count(*)
from
(
select a.bprf_no, count(*) no_of_month
from
(
select
bill_month, avg_ind, bill_month, add_months(bill_month, +1) bm_1, bprf_no
from vw_ibs_bill
where bill_month <= '30-Jun-2012'
and avg_ind in (1, 2)
order by bprf_no desc, bill_month desc
) a, vw_ibs_bill b
where a.bprf_no = b.bprf_no
and a.bill_month = b.bill_month
and a.avg_ind in (1, 2)
and bill_month = bm_1
group by a.bprf_no
having count(a.bprf_no) >= '&no_of_month'
)
group by bprf_no, no_of_month
order by 1
/
BPRF_NO BILL_MONTH NO_OF_TIMES
-------------------------------------------------
BP03 30-Jun-2012 4
BP04 30-Jun-2012 4
BP05 30-Jun-2012 6
BP06 30-Jun-2012 6
BP07 30-Jun-2012 6
BP08 30-Jun-2012 6
Here BP03 & BP04 should not come into the listing itself.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
