PL/SQL :: Get Holidays Count  Between Startdate And Enddate

Dec 12, 2012

i have to get holidays count including sturday and sunday using HOLIDAYDATEMASTER if between stardate and enddate having any leave below is my procedure if run this procedure

startdate:12/21/2012 -- mm/dd/yyyy endate:12/26/2012 iam getting result=2 only actual not
included 25 holidaydate(22 sat ,23 sun)

CREATE TABLE HOLIDAYDATEMASTER(HOLIDAYDATEID NUMBER not null,HOLIDAYNAME VARCHAR2(100),
HOLIDAYDATE DATE,HOLIDAYYEAR NUMBER(10));

insert into HOLIDAYDATEMASTER (HOLIDAYDATEID, HOLIDAYNAME, TO_CHAR(HOLIDAYDATE,'DD-MON-YY, HOLIDAYDATE, HOLIDAYYEAR)
values (461, 'Christmas Day', '25-DEC-2012', to_date('25-12-2012', 'dd-mm-yyyy'), 2012);

CREATE OR REPLACE PROCEDURE p_Holiday_DAYS
(V_START_DATE IN DATE, V_END_DATE IN DATE,OC_DUR OUT NUMBER)
IS

[Code]....

View 5 Replies


ADVERTISEMENT

Create Trigger That Can Control Startdate And EndDate?

Apr 30, 2013

create table TestDate(
idDate int not null primary key,
StartDate date not null,
EndDate date not null
)

I want to create a trigger that can control the Startdate and the EndDate when inserting data,the Endate must be less than the StartDate

View 1 Replies View Related

SQL & PL/SQL :: Query To Find Continuous Holidays From Holiday Master

Nov 9, 2011

I've a holiday master in which date of holiday is present. It also contain all Sundays.

I've a process which is not executed on Sundays and Holidays. After holidays it must process all data.

E.g.

01-NOV-2011 --Holiday
02-NOV-2011 --Holiday
03-NOV-2011 --Sunday

On 31-OCT-2011 Process is executed for same day data. Since 1,2-Nov are holidays and 3-nov is Sunday. The process will be executed on 04-Nov-2011 for data between 01-Nov-2011 to 04-Nov-2011. On 05-nov-2011 process will be executed for same day data. Then the process will be executed on 07-nov for 6,7-nov data, because 6-nov was Sunday.

It's like

SELECT *
FROM TABLE_NAME
WHERE BUSINESS_DATE BETWEEN LV_START_DATE AND LV_END_DATE ;

How to select this LV_START_DATE, LV_END_DATE i.e. MIN & MAX date for a topmost continuous range less than the business date.

Sample Data.
.
.
01-NOV-2011
02-NOV-2011
03-NOV-2011
06-NOV-2011
.
.

View 6 Replies View Related

SQL & PL/SQL :: Distinct Values - Get Records For Which All Startdate Same

Jul 13, 2010

I have table as below :-

Table ABC(
ID Number,
startDate date,
City varchar2(10)
)

I need to write query which will get me all the CityNames for which there are no Startdate differs,

i.e. To get all the city name records for which all of the Startdate are same across all the records.

I dont want to go after Self Join due to performance issues, do we have any better way?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved