aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
fn_period
Parameters
Name
Type
Mode
period_type
text
IN
start_date
date
INOUT (DEFAULT NULL)
end_date
date
INOUT (DEFAULT NULL)
Definition
BEGIN CASE upper(period_type) WHEN 'TODAY' THEN start_date := COALESCE(start_date, end_date, fn_system_date()); end_date := COALESCE(end_date, start_date); WHEN 'TOMORROW' THEN start_date := COALESCE(start_date, end_date, fn_system_date()+1); end_date := COALESCE(end_date, start_date); WHEN 'YESTERDAY' THEN start_date := COALESCE(start_date, end_date, fn_system_date()-1); end_date := COALESCE(end_date, start_date); WHEN 'MTD' THEN -- Month To Date end_date := COALESCE(end_date, start_date, fn_system_date()); start_date := date_trunc('MONTH', end_date)::DATE; WHEN 'YTD' THEN -- Year To Date end_date := COALESCE(end_date, start_date, fn_system_date()); start_date := date_trunc('YEAR', end_date)::DATE; WHEN 'LMTD' THEN -- Last Month To Date end_date := COALESCE(end_date, start_date, fn_system_date()) - INTERVAL '1 year'; start_date := date_trunc('MONTH', end_date)::DATE; WHEN 'LYTD' THEN -- Last Year To Date end_date := COALESCE(end_date, start_date, fn_system_date()) - INTERVAL '1 year'; start_date := date_trunc('YEAR', end_date)::DATE; WHEN 'LAST_MONTH' THEN -- Last <ont end_date := date_trunc('MONTH', COALESCE(end_date, start_date, fn_system_date())) - INTERVAL '1 day'; start_date := date_trunc('MONTH', end_date)::DATE; WHEN 'LAST_YEAR' THEN -- Last Year end_date := date_trunc('YEAR', COALESCE(end_date, start_date, fn_system_date())) - INTERVAL '1 day'; start_date := date_trunc('YEAR', end_date)::DATE; ELSE CASE WHEN period_type LIKE '%,%' THEN start_date := lower(('['||period_type||')')::daterange); end_date := upper(('['||period_type||')')::daterange); ELSE END CASE; END CASE; END