on 08-02-2011 5:05 PM
I have period number as a parameter value in several places in the sq(terdata). Need to replace it so that it is not
parameterised anymore.
period number refers to the fiscal month number and the fiscal year starts from june. For example jun 2011 is
fiscal year 2012 and period number 1.
-
I have created the sql to compute period number:
select case when extract( month from ( today - extract(day from today) + 1) -1) = 6 then 1
when extract( month from ( today - extract(day from today) + 1) -1) = 7 then 2
when extract( month from ( today - extract(day from today) + 1) -1) = 8 then 3
when extract( month from ( today - extract(day from today) + 1) -1) = 9 then 4
when extract( month from ( today - extract(day from today) + 1) -1) = 10 then 5
when extract( month from ( today - extract(day from today) + 1) -1) = 11 then 6
when extract( month from ( today - extract(day from today) + 1) -1) = 12 then 7
when extract( month from ( today - extract(day from today) + 1) -1) = 1 then 8
when extract( month from ( today - extract(day from today) + 1) -1) = 2 then 9
when extract( month from ( today - extract(day from today) + 1) -1) = 3 then 10
when extract( month from ( today - extract(day from today) + 1) -1) = 4 then 11
when extract( month from ( today - extract(day from today) + 1) -1) = 5 then 12
else 0 end period_nbr
from
(select distinct current_date as today
from Sys_Calendar.Calendar) a
-
-- sql in which the parameters need to be replaced so that the report is not parameterised anymore and it just runs for the prevoius 12 months
--but period number is used in select.. The following sql repeats itself at 4 places in the main sql. this is just one of them. ANy recomendation on the best way to do this
select
true_std_delv_days,
sum(case when period_nbr=@Prompt('Fiscal Month','N',,mono,free) and re_flg=1 then shpmt_qty else 0 end) as fails,
sum(case when period_nbr=@Prompt('Fiscal Month','N',,mono,free) and se_flg=1 then shpmt_qty else 0 end) as base,
sum(case when re_flg=1 then shpmt_qty else 0 end) as ytd_fails,
sum(case when base_flg=1 then shpmt_qty else 0 end) as ytd_base
from db.fxg
where period_nbr<=@Prompt('Fiscal Month','N',,mono,free) and fiscal_yr_nbr=@Prompt('Fiscal Year','N',,mono,free)
group by 1
Hi,
sorry i don't understand exactly what you want to do.
Perhaps this helps you a little bit:
select today,
to_char( today, 'Month') month,
to_number(to_char( today, 'mm')) nr_month,
mod( to_number(to_char( today, 'mm')) - 6 + 12, 12) + 1 nbr
from
(select level,
sysdate-level*25 today
from dual
connect by level <= 20 ) a;
regards
Kay
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.