Home » SQL & PL/SQL » SQL & PL/SQL » Creating Date break down result (oracle 10g r2 (10.2.0.1))
Creating Date break down result [message #356916] Mon, 03 November 2008 02:14 Go to next message
micro_oracle
Messages: 41
Registered: December 2007
Location: UAE
Member

Hi All

I have the a small problem in creating an SQL code to break down the employee leave days (period) on a special calender maintained in our company.

That our company using strange calender to calculate their payroll amounts and all other activities, and there calender considering the month starting day as 26 of each month and not the 01st day of each month : (here is the script to show our calender)

create table calender_test
(
MONTH		VARCHAR2(13),
MON	     	VARCHAR2(3) NOT NULL,
YEAR		VARCHAR2(4) NOT NULL,
FIRST_DAY		DATE,
LAST_DAY		DATE,
NO_OF_DAYS		NUMBER(2));



INSERT INTO CALENDER_TEST (MONTH,MON,YEAR,FIRST_DAY,LAST_DAY,NO_OF_DAYS) VALUES ('JANUARY','JAN','2008',TO_DATE('26-DEC-2007','DD-MON-YYYY'),TO_DATE('25-JAN-2008','DD-MON-YYYY'),31);
INSERT INTO CALENDER_TEST (MONTH,MON,YEAR,FIRST_DAY,LAST_DAY,NO_OF_DAYS) VALUES ('FEBRUARY','FEB','2008',TO_DATE('26-JAN-2008','DD-MON-YYYY'),TO_DATE('25-FEB-2008','DD-MON-YYYY'),31);
INSERT INTO CALENDER_TEST (MONTH,MON,YEAR,FIRST_DAY,LAST_DAY,NO_OF_DAYS) VALUES ('MARCH','MAR','2008',TO_DATE('26-FEB-2008','DD-MON-YYYY'),TO_DATE('25-MAR-2008','DD-MON-YYYY'),29);
INSERT INTO CALENDER_TEST (MONTH,MON,YEAR,FIRST_DAY,LAST_DAY,NO_OF_DAYS) VALUES ('APRIL','APR','2008',TO_DATE('26-MAR-2008','DD-MON-YYYY'),TO_DATE('25-APR-2008','DD-MON-YYYY'),31);
INSERT INTO CALENDER_TEST (MONTH,MON,YEAR,FIRST_DAY,LAST_DAY,NO_OF_DAYS) VALUES ('MAY','MAY','2008',TO_DATE('26-APR-2008','DD-MON-YYYY'),TO_DATE('25-MAY-2008','DD-MON-YYYY'),30);
INSERT INTO CALENDER_TEST (MONTH,MON,YEAR,FIRST_DAY,LAST_DAY,NO_OF_DAYS) VALUES ('JUNE','JUN','2008',TO_DATE('26-MAY-2008','DD-MON-YYYY'),TO_DATE('25-JUN-2008','DD-MON-YYYY'),31);
INSERT INTO CALENDER_TEST (MONTH,MON,YEAR,FIRST_DAY,LAST_DAY,NO_OF_DAYS) VALUES ('JULY','JUL','2008',TO_DATE('26-JUN-2008','DD-MON-YYYY'),TO_DATE('25-JUL-2008','DD-MON-YYYY'),30);
INSERT INTO CALENDER_TEST (MONTH,MON,YEAR,FIRST_DAY,LAST_DAY,NO_OF_DAYS) VALUES ('AUGUST','AUG','2008',TO_DATE('26-JUL-2008','DD-MON-YYYY'),TO_DATE('25-AUG-2008','DD-MON-YYYY'),31);
INSERT INTO CALENDER_TEST (MONTH,MON,YEAR,FIRST_DAY,LAST_DAY,NO_OF_DAYS) VALUES ('SEPTEMBER','SEP','2008',TO_DATE('26-AUG-2008','DD-MON-YYYY'),TO_DATE('25-SEP-2008','DD-MON-YYYY'),31);
INSERT INTO CALENDER_TEST (MONTH,MON,YEAR,FIRST_DAY,LAST_DAY,NO_OF_DAYS) VALUES ('OCTOBER','OCT','2008',TO_DATE('26-SEP-2008','DD-MON-YYYY'),TO_DATE('25-OCT-2008','DD-MON-YYYY'),30);
INSERT INTO CALENDER_TEST (MONTH,MON,YEAR,FIRST_DAY,LAST_DAY,NO_OF_DAYS) VALUES ('NOVEMBER','NOV','2008',TO_DATE('26-OCT-2008','DD-MON-YYYY'),TO_DATE('25-NOV-2008','DD-MON-YYYY'),31);
INSERT INTO CALENDER_TEST (MONTH,MON,YEAR,FIRST_DAY,LAST_DAY,NO_OF_DAYS) VALUES ('DECEMBER','DEC','2008',TO_DATE('26-NOV-2008','DD-MON-YYYY'),TO_DATE('25-DEC-2008','DD-MON-YYYY'),30);


 select * from calender_test;
MONTH	MON	YEAR	FIRST_DAY	LAST_DAY	NO_OF_DAYS
JANUARY	JAN	2008	12/26/2007 00:00:00	01/25/2008 00:00:00	31
FEBRUARY	FEB	2008	01/26/2008 00:00:00	02/25/2008 00:00:00	31
MARCH	MAR	2008	02/26/2008 00:00:00	03/25/2008 00:00:00	29
APRIL	APR	2008	03/26/2008 00:00:00	04/25/2008 00:00:00	31
MAY	MAY	2008	04/26/2008 00:00:00	05/25/2008 00:00:00	30
JUNE	JUN	2008	05/26/2008 00:00:00	06/25/2008 00:00:00	31
JULY	JUL	2008	06/26/2008 00:00:00	07/25/2008 00:00:00	30
AUGUST	AUG	2008	07/26/2008 00:00:00	08/25/2008 00:00:00	31
SEPTEMBER	SEP	2008	08/26/2008 00:00:00	09/25/2008 00:00:00	31
OCTOBER	OCT	2008	09/26/2008 00:00:00	10/25/2008 00:00:00	30
NOVEMBER	NOV	2008	10/26/2008 00:00:00	11/25/2008 00:00:00	31
DECEMBER	DEC	2008	11/26/2008 00:00:00	12/25/2008 00:00:00	30




and now I have the Employee leave table and it's simple like this :
create table employee_leave_test (
Emp_code  	VARCHAR2(13),
emp_start_lv_dt 	DATE,
emp_end_lv_dt       date,
No_of_lv_days       number);


INSERT INTO employee_leave_test (Emp_code,emp_start_lv_dt,emp_end_lv_dt,No_of_lv_days) values ('5001',TO_DATE('10-MAR-2008','DD-MON-YYYY'),TO_DATE('15-MAR-2008','DD-MON-YYYY'),5);
INSERT INTO employee_leave_test (Emp_code,emp_start_lv_dt,emp_end_lv_dt,No_of_lv_days) values ('5002',TO_DATE('08-JAN-2008','DD-MON-YYYY'),TO_DATE('15-APR-2008','DD-MON-YYYY'),98);
INSERT INTO employee_leave_test (Emp_code,emp_start_lv_dt,emp_end_lv_dt,No_of_lv_days) values ('5003',TO_DATE('17-JUN-2008','DD-MON-YYYY'),TO_DATE('05-JUL-2008','DD-MON-YYYY'),18 );
INSERT INTO employee_leave_test (Emp_code,emp_start_lv_dt,emp_end_lv_dt,No_of_lv_days) values ('5004',TO_DATE('10-SEP-2008','DD-MON-YYYY'),TO_DATE('25-OCT-2008','DD-MON-YYYY'),45 );
INSERT INTO employee_leave_test (Emp_code,emp_start_lv_dt,emp_end_lv_dt,No_of_lv_days) values ('5005',TO_DATE('01-NOV-2008','DD-MON-YYYY'),TO_DATE('01-DEC-2008','DD-MON-YYYY'),30 );



then the select statement :

select * from employee_leave_test;




EMP_CODE	EMP_START_LV_DT	EMP_END_LV_DT	NO_OF_LV_DAYS
5001	03/10/2008 00:00:00	03/15/2008 00:00:00	5
5002	01/08/2008 00:00:00	04/15/2008 00:00:00	98
5003	06/17/2008 00:00:00	07/05/2008 00:00:00	18
5004	09/10/2008 00:00:00	10/25/2008 00:00:00	45
5005	11/01/2008 00:00:00	12/01/2008 00:00:00	30




now my request is :
can any one help me to write an SQL code to get this target result:

EMP_CODE	EMP_START_LV_DT	EMP_END_LV_DT	NO_OF_LV_DAYS	Mon	Year	Break_down
5001	3/10/2008 0:00	3/15/2008 0:00	5	MAR	2008	5
						
5002	1/8/2008 0:00	4/15/2008 0:00	98	JAN	2008	17
5002	1/8/2008 0:00	4/15/2008 0:00	98	FEB	2008	31
5002	1/8/2008 0:00	4/15/2008 0:00	98	MAR	2008	29
5002	1/8/2008 0:00	4/15/2008 0:00	98	APR	2008	21
5003	6/17/2008 0:00	7/5/2008 0:00	18	JUN	2008	8
5003	6/17/2008 0:00	7/5/2008 0:00	18	JUL	2008	10
5004	9/10/2008 0:00	10/25/2008 0:00	45	SEP	2008	15
5004	9/10/2008 0:00	10/25/2008 0:00	45	OCT	2008	30
5005	11/1/2008 0:00	12/1/2008 0:00	30	NOV	2008	24
5005	11/1/2008 0:00	12/1/2008 0:00	30	DEC	2008	6


is this applicable , or can any body give me that script

Thanks
Re: Creating Date break down result [message #356955 is a reply to message #356916] Mon, 03 November 2008 05:57 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Search for row generator, calendar in this forum. You will find loads of examples how to do it.

Regards

Raj
Re: Creating Date break down result [message #356969 is a reply to message #356916] Mon, 03 November 2008 06:33 Go to previous messageGo to next message
micro_oracle
Messages: 41
Registered: December 2007
Location: UAE
Member

Ya Sir,

But since I need the result to be formed as the calender table created the query become complicated than simple producing a sequences of month using the connect by and rownum techniques.

See, I can reach to the result if I'm usinG oracle standard calender and date function but the calender specified ubove is struggling me Embarassed
Re: Creating Date break down result [message #356983 is a reply to message #356969] Mon, 03 November 2008 07:11 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Infact I just overlooked it. You don't need a row generator because you already have one. All you need is a right query to do it. I have written one for you. But it is not matching up correctly with your expected result. So tweak it as per your requirement.

select emp_code, emp_Start_lv_dt, emp_end_lv_dt, first_day, last_day, 
       case when (emp_start_lv_dt between first_day and last_day)  and (emp_end_lv_dt between first_Day and last_day)
            then
                 emp_end_lv_dt - emp_start_lv_dt
            when (emp_start_lv_dt between first_day and last_day)
            then
                 last_day - emp_Start_lv_dt
            when (emp_end_lv_dt between first_day and last_day)
            then
                 emp_end_lv_dt - first_day
            else
                 no_of_days 
        end no_days
from
employee_leave_test,
calender_Test
where
emp_end_lv_dt >= first_day and
emp_start_lv_dt <= last_day
order by 1, 4


Regards

Raj
Previous Topic: interchanging values
Next Topic: Finding Recordset
Goto Forum:
  


Current Time: Thu Dec 08 18:42:22 CST 2016

Total time taken to generate the page: 0.09207 seconds