Oracle sql Complete the following code to give me a cursor of ten federal holidays. You pass in the calendar year, it passes back a cursor of the year's holidays. 10 Federal Holidays. All can be logically calculated. Some are always on Monday... one is always on Thursday... the others.. Like Christmas... If Christmas is a Saturday, it's observed on Friday, if it's Sunday, it's observed on Monday. Here's the holidays: New Year's Day Martin Luther King President's Day Memorial Day Independence Day Labor Day Columbus Day Veteran's Day Thanksgiving Day Christmas Day Here's the starter code (I gave you the first two) create or replace PACKAGE BODY pkg_hldy AS PROCEDURE holiday_cursor ( hldy_year_in NUMBER, cur_out OUT SYS_REFCURSOR ) AS BEGIN OPEN cur_out FOR SELECT holiday_name, TRIM(TO_CHAR(holiday_calendar_date,'DAY') ) holiday_day, CASE adj WHEN 0 THEN holiday_calendar_date ELSE CASE TRIM(TO_CHAR(holiday_calendar_date,'DAY') ) WHEN 'SATURDAY' THEN holiday_calendar_date - 1 WHEN 'SUNDAY' THEN holiday_calendar_date + 1 ELSE holiday_calendar_date END END holiday_observed, holiday_calendar_date FROM ( SELECT 'New Year''s Day' AS holiday_name, trunc(TO_DATE(hldy_year_in,'YYYY'),'YYYY') holiday_calendar_date, 1 adj FROM dual UNION SELECT 'Martin Luther King, Jr. Day', next_day(trunc(TO_DATE(hldy_year_in,'YYYY'),'YYYY') - 1,'MONDAY') + 14, 0 FROM dual -- UNION -- GIVE ME THE REST OF THE SELECT STATEMENTS FOR THE REST OF THE HOLIDAYS!!! ); END; END pkg_hldy;
Oracle sql
Complete the following code to give me a cursor of ten federal holidays. You pass in the calendar year, it passes back a cursor of the year's holidays.
10 Federal Holidays. All can be logically calculated. Some are always on Monday... one is always on Thursday... the others.. Like Christmas... If Christmas is a Saturday, it's observed on Friday, if it's Sunday, it's observed on Monday.
Here's the holidays:
New Year's Day
Martin Luther King
President's Day
Memorial Day
Independence Day
Labor Day
Columbus Day
Veteran's Day
Thanksgiving Day
Christmas Day
Here's the starter code (I gave you the first two)
create or replace PACKAGE BODY pkg_hldy AS
PROCEDURE holiday_cursor (
hldy_year_in NUMBER,
cur_out OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN cur_out FOR SELECT
holiday_name,
TRIM(TO_CHAR(holiday_calendar_date,'DAY') ) holiday_day,
CASE adj
WHEN 0 THEN holiday_calendar_date
ELSE
CASE TRIM(TO_CHAR(holiday_calendar_date,'DAY') )
WHEN 'SATURDAY' THEN holiday_calendar_date - 1
WHEN 'SUNDAY' THEN holiday_calendar_date + 1
ELSE holiday_calendar_date
END
END
holiday_observed,
holiday_calendar_date
FROM
(
SELECT
'New Year''s Day' AS holiday_name,
trunc(TO_DATE(hldy_year_in,'YYYY'),'YYYY') holiday_calendar_date,
1 adj
FROM
dual
UNION
SELECT
'Martin Luther King, Jr. Day',
next_day(trunc(TO_DATE(hldy_year_in,'YYYY'),'YYYY') - 1,'MONDAY') + 14,
0
FROM
dual
-- UNION
-- GIVE ME THE REST OF THE SELECT STATEMENTS FOR THE REST OF THE HOLIDAYS!!!
);
END;
END pkg_hldy;
Trending now
This is a popular solution!
Step by step
Solved in 4 steps with 1 images
