edit sql

[Former Member]

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

[Former Member · Accepted solution]

The best practice recommendation is to build a financial calendar table.

Typicall columns that you'd want are:

datekey - integer PK column, ideally in yyyymmdd format (index this column)

calendar_date - date format column, one day per row (index this column)

day_number_of_month - e.g. 3 for today

month_number - e.g. 8 for today

year_number - e.g. 11 for today

day_name - e.g. Wednesday for today

month_name - e.g. August for today

working_day - e.g. 1 for today, 0 for weekends and public holidays

calendar_year_month - e.g. 201108 for today

financial_year - e.g. 2012 for today

financial_period - e.g. 3 for today

financial_year_period - e.g. 201203 for today

There are plenty of other columns that you can add.

One set that I like to use are offset columns where you can use scheduled reports based on the offsets. You would update your calendar table each night to recalculate the offsets, such as:

day_offset - 0 for today, 1 for yesterday

week_offset - 0 for today, 1 for Saturday 30th July

month_offset - 0 for today, 1 for Saturday 30th July

financial_year_offset - 0 for today, 0 for Saturday 30th July, 1 for Saturday 30th April

calendar_year_offset - 0 for today, 0 for Saturday 30th July, 0 for Saturday 30th April, 1 for 31st December 2010

You'll need to write some sql to build the table and populate it but the one-off effort saves so much time in the long run that you'll be very happy.

Instead of your complex sql, you can simply create whatever aliases of the calendar table you need for each date in your universe and create a class of date items related to that date, e.g. sales date, shipping date, invoice date, payment date.

That way you can see what payments where made in which financial period and compare it to sales made in financial periods with just drag and drop in Webi.

Regards,

Mark