Former Member
Aug 02, 2011 at 04:05 PM

# sql

24 Views

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