Need data beween date range [message #641923] |
Thu, 27 August 2015 11:22 |
|
sathishh.mk@gmail.com
Messages: 3 Registered: August 2015
|
Junior Member |
|
|
Hi,
I am new forums, hope i will get some inputs.
I need to show the all data between date range.
Pleae find the attached excel for reference.
Here, the situation i fall into:
Data stored in the table like below:
ID Dept From_date To_date
ID_123 Dep_1 1-Jan-15 31-May-15
ID_123 Dep_2 1-Jun-15 31-Dec-99
I need to show results like below:
ID Dept From_date To_date Comments
ID_123 Dep_1 1-Jan-15 31-May-15
ID_123 Dep_1 1-Feb-15 31-May-15
ID_123 Dep_1 1-Mar-15 31-May-15
ID_123 Dep_1 1-Apr-15 31-May-15
ID_123 Dep_1 1-May-15 31-May-15
ID_123 Dep_2 1-Jun-15 31-Dec-99 Latest Record
ID_123 Dep_2 1-Jul-15 31-Dec-99 Till current Month
ID_123 Dep_2 1-Aug-15 31-Dec-99 Till current Month
Any help would be appriciate.
Thanks,
Sathish
|
|
|
|
|
|
|
|
|
Re: Need data beween date range [message #641938 is a reply to message #641933] |
Thu, 27 August 2015 14:12 |
|
sathishh.mk@gmail.com
Messages: 3 Registered: August 2015
|
Junior Member |
|
|
Hi Manu,
Sorry..please find the below statements and do the needful.
drop table test_employee;
CREATE TABLE test_employee
( emp_id varchar2(50) NOT NULL, dept_id varchar2(50) NOT NULL, emp_from_date date, emp_to_date date);
INSERT INTO test_employee (emp_id, dept_id, emp_from_date,emp_to_date)
values ('ID_123','Dep_1',to_date('01-01-2015', 'dd-mm-yyyy'),to_date('31-05-2015', 'dd-mm-yyyy'));
INSERT INTO test_employee (emp_id, dept_id, emp_from_date,emp_to_date)
values ('ID_123','Dep_2',to_date('01-06-15', 'dd-mm-yyyy'),to_date('31-12-9999', 'dd-mm-yyyy'));
select * from test_employee;
Data stored in DB like below
ID Dept From_date To_date
ID_123 Dep_1 1-Jan-15 31-May-15
ID_123 Dep_2 1-Jun-15 31-Dec-99
Required output is:
Need to get results as below
ID Dept From_date To_date Comments
ID_123 Dep_1 1-Jan-15 31-May-15
ID_123 Dep_1 1-Feb-15 31-May-15
ID_123 Dep_1 1-Mar-15 31-May-15
ID_123 Dep_1 1-Apr-15 31-May-15
ID_123 Dep_1 1-May-15 31-May-15
ID_123 Dep_2 1-Jun-15 31-Dec-99 Latest Record
ID_123 Dep_2 1-Jul-15 31-Dec-99 Till current Month
ID_123 Dep_2 1-Aug-15 31-Dec-99 Till current Month
Regards,
Sathish
Lalit : Added code tags. It is already been told to you to use code tags.
[Updated on: Fri, 28 August 2015 04:24] by Moderator Report message to a moderator
|
|
|
Re: Need data beween date range [message #641940 is a reply to message #641938] |
Thu, 27 August 2015 14:42 |
manubatham20
Messages: 566 Registered: September 2010 Location: Seattle, WA, USA
|
Senior Member |
|
|
May be something like this
WITH test_employees
AS (SELECT 'ID_123' id,
'Dep_1' dept,
TO_DATE ('01-JAN-15', 'DD-MON-YY') from_date,
TO_DATE ('31-MAY-15', 'DD-MON-YY') TO_DATE
FROM DUAL
UNION ALL
SELECT 'ID_123' id,
'Dep_2' dept,
TO_DATE ('01-JUN-15', 'DD-MON-YY') from_date,
TO_DATE ('31-DEC-99', 'DD-MON-YY') TO_DATE
FROM DUAL)
SELECT id,
dept,
ADD_MONTHS (from_date, LEVEL),
TO_DATE
FROM test_employees
CONNECT BY LEVEL <= MONTHS_BETWEEN (TO_DATE, from_date);
Manu
|
|
|
|