authen.public

Parameters

Name	Type	Mode
period_type	text	IN
include_current	boolean	IN (DEFAULT true)
date_ref	date	IN (DEFAULT CURRENT_DATE)
start_date	date	INOUT (DEFAULT NULL)
end_date	date	INOUT (DEFAULT NULL)

Definition

declare 
	current_date_end date    = case when include_current then date_ref else date_ref-1 end;
 	current_date_start date  = case when include_current then date_ref else date_ref+1 end;
BEGIN 
	
    CASE upper(period_type) 
       WHEN 'TODAY' THEN
            start_date  := COALESCE(start_date, end_date, date_ref);
            end_date    := COALESCE(end_date, start_date);
       WHEN 'TOMORROW' THEN
            start_date  := COALESCE(start_date, end_date, date_ref+1);
            end_date    := COALESCE(end_date, start_date);
       WHEN 'YESTERDAY' THEN
            start_date  := COALESCE(start_date, end_date, date_ref-1);
            end_date    := COALESCE(end_date, start_date);
       WHEN 'MTD' THEN  -- Month To Date
            end_date    := COALESCE(end_date, start_date, current_date_end);
            start_date  := date_trunc('MONTH', end_date)::DATE;
       WHEN 'THIS_MONTH' THEN  
            end_date    := date_trunc('MONTH', COALESCE(end_date, start_date, date_ref)) + INTERVAL '1 month - 1 day';
            start_date  := date_trunc('MONTH', end_date)::DATE;
       WHEN 'LAST_MONTH' THEN -- Last <ont
            end_date    := date_trunc('MONTH', COALESCE(end_date, start_date, date_ref)) - INTERVAL '1 day';
            start_date  := date_trunc('MONTH', end_date)::DATE;
       WHEN 'NEXT_MONTH' THEN -- Last <ont
            end_date    := date_trunc('MONTH', COALESCE(end_date, start_date, date_ref)) + INTERVAL '2 month - 1 day';
            start_date  := date_trunc('MONTH', end_date)::DATE;
       WHEN 'LAST_7_DAYS' THEN -- Last <ont
            end_date    := COALESCE(start_date-1, end_date, current_date_end);
            start_date  := end_date - INTERVAL '6 day';
       WHEN 'NEXT_7_DAYS' THEN -- Last <ont
            start_date  := COALESCE(end_date+1, start_date, current_date_start);
            end_date    := start_date + INTERVAL '6 day';
       WHEN 'LAST_30_DAYS' THEN -- Last <ont
            end_date    := COALESCE(start_date-1, end_date, current_date_end);
            start_date  := end_date - INTERVAL '29 day';
       WHEN 'NEXT_30_DAYS' THEN -- Last <ont
            start_date  := COALESCE(end_date+1, start_date, current_date_start);
            end_date    := start_date + INTERVAL '29 day';
       WHEN 'YTD' THEN  -- Year To Date
            end_date    := COALESCE(end_date, start_date, current_date_end);
            start_date  := date_trunc('YEAR', end_date)::DATE;
       WHEN 'LMTD' THEN -- Last Year's Month-To-Date
            end_date    := COALESCE(end_date, start_date, date_ref) - INTERVAL '1 year';
            start_date  := date_trunc('MONTH', end_date)::DATE;
       WHEN 'LYTD' THEN -- Last Year's Year-To-Date
            end_date    := COALESCE(end_date, start_date, date_ref) - INTERVAL '1 year';
            start_date  := date_trunc('YEAR', end_date)::DATE;
       WHEN 'THIS_YEAR' THEN -- 
            end_date    := date_trunc('YEAR' , COALESCE(end_date, start_date, date_ref)) + INTERVAL '1 year - 1 day';  --date_trunc('YEAR', COALESCE(end_date, start_date, date_ref)) - INTERVAL '1 day';
            start_date  := date_trunc('YEAR', end_date)::DATE; 
       WHEN 'LAST_YEAR' THEN -- Last Year
            end_date    := date_trunc('YEAR', COALESCE(end_date, start_date, date_ref)) - INTERVAL '1 day';
            start_date  := date_trunc('YEAR', end_date)::DATE; 
       WHEN 'NEXT_YEAR' THEN -- Last Year
            end_date    := date_trunc('YEAR' , COALESCE(end_date, start_date, date_ref)) + INTERVAL '2 year - 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

fn_period

Parameters

Definition