Hello Gurus, I have a new report requirement where I need to get records with minimum effective_date(s). It's a little more complex than it seems. Please see example below.
Data resides in one custom table
1) I have one record that meets my search criteria and has an effective_date of 07/02/2018
2) I have 5 records that meet my search criteria but have a date range of 05/07/2018 to 05/11/2018 as effective dates
3) I have 10 records that meet my search criteria and have a date range of 09/03/2018 to 09/14/2018 (excludes dates that fall on weekends)
Irrespective of what the other column data looks like (identical or different), the report should pick up the rows with effective_dates - 07/02/2018, 05/07/2018 and 09/03/2018
Is it possible?
Here's a scenario:
Table creation
Create table smc_org_hierarchy_changes
(effective_date date,
employee_number number,
emp_name varchar2(150),
emp_email_address varchar2(150),
emp_position varchar2(150),
organization_name varchar2(150),
location_name varchar2(150),
department varchar2(150),
line_of_business varchar2(150),
supervisor_name varchar2(150),
supervisor_emp_num number,
supervisor_email_address varchar2(150) )
------------------------------------------------------------------------
Insert Statements:
Insert Into smc_org_hierarchy_changes Values
('05/07/2018',
'123456',
'Smith, John',
'jsmith@oreon.com',
'Sourcing Specialist',
'Oreon Inc',
'Corporate',
'Procurement',
'Default',
'Vacant',
'',
''
); Commit;
--
Insert Into smc_org_hierarchy_changes Values
('05/08/2018',
'123456',
'Smith, John',
'jsmith@oreon.com',
'Sourcing Specialist',
'Oreon Inc',
'Corporate',
'Procurement',
'Default',
'Vacant',
'',
''
); Commit;
--
Insert Into smc_org_hierarchy_changes Values
('05/09/2018',
'123456',
'Smith, John',
'jsmith@oreon.com',
'Sourcing Specialist',
'Oreon Inc',
'Corporate',
'Procurement',
'Default',
'Vacant',
'',
''
); Commit;
--
Insert Into smc_org_hierarchy_changes Values
('05/10/2018',
'123456',
'Smith, John',
'jsmith@oreon.com',
'Sourcing Specialist',
'Oreon Inc',
'Corporate',
'Procurement',
'Default',
'Vacant',
'',
''
); Commit;
--
Insert Into smc_org_hierarchy_changes Values
('05/11/2018',
'123456',
'Smith, John',
'jsmith@oreon.com',
'Sourcing Specialist',
'Oreon Inc',
'Corporate',
'Procurement',
'Default',
'Vacant',
'',
''
); Commit;
--
Insert Into smc_org_hierarchy_changes Values
('05/14/2018',
'123456',
'Smith, John',
'jsmith@oreon.com',
'Sourcing Specialist',
'Oreon Inc',
'Corporate',
'Procurement',
'Default',
'Jackson, Adam',
'234567',
'adsmith@oreon.com');
Commit;
--
Insert Into smc_org_hierarchy_changes Values
('05/15/2018',
'123456',
'Smith, John',
'jsmith@oreon.com',
'Sourcing Specialist',
'Oreon Inc',
'Corporate',
'Procurement',
'Default',
'Jackson, Adam',
'234567',
'adsmith@oreon.com');
Commit;
--
Insert Into smc_org_hierarchy_changes Values
('05/16/2018',
'123456',
'Smith, John',
'jsmith@oreon.com',
'Sourcing Specialist',
'Oreon Inc',
'Corporate',
'Procurement',
'Default',
'Jackson, Adam',
'234567',
'adsmith@oreon.com');
Commit;
--
Insert Into smc_org_hierarchy_changes Values
('05/17/2018',
'123456',
'Smith, John',
'jsmith@oreon.com',
'Sourcing Specialist',
'Oreon Inc',
'Corporate',
'Procurement',
'Default',
'Jackson, Adam',
'234567',
'adsmith@oreon.com');
Commit;
--
Insert Into smc_org_hierarchy_changes Values
('05/18/2018',
'123456',
'Smith, John',
'jsmith@oreon.com',
'Sourcing Specialist',
'Oreon Inc',
'Corporate',
'Procurement',
'Default',
'Jackson, Adam',
'234567',
'adsmith@oreon.com');
Commit;
--
Insert Into smc_org_hierarchy_changes Values
('07/02/2018',
'123456',
'Smith, John',
'jsmith@oreon.com',
'Sourcing Specialist',
'Oreon Inc',
'Corporate',
'Procurement',
'Default',
'Vacant',
'',
''
); Commit;
--
Insert Into smc_org_hierarchy_changes Values
('09/03/2018',
'123456',
'Smith, John',
'jsmith@oreon.com',
'Sourcing Specialist',
'Oreon Inc',
'Corporate',
'Procurement',
'Default',
'Vacant',
'',
''
); Commit;
--
Insert Into smc_org_hierarchy_changes Values
('09/04/2018',
'123456',
'Smith, John',
'jsmith@oreon.com',
'Sourcing Specialist',
'Oreon Inc',
'Corporate',
'Procurement',
'Default',
'Vacant',
'',
''
); Commit;
--
Insert Into smc_org_hierarchy_changes Values
('09/05/2018',
'123456',
'Smith, John',
'jsmith@oreon.com',
'Sourcing Specialist',
'Oreon Inc',
'Corporate',
'Procurement',
'Default',
'Vacant',
'',
''
); Commit;
--
Insert Into smc_org_hierarchy_changes Values
('09/06/2018',
'123456',
'Smith, John',
'jsmith@oreon.com',
'Sourcing Specialist',
'Oreon Inc',
'Corporate',
'Procurement',
'Default',
'Vacant',
'',
''
); Commit;
--
Insert Into smc_org_hierarchy_changes Values
('09/07/2018',
'123456',
'Smith, John',
'jsmith@oreon.com',
'Sourcing Specialist',
'Oreon Inc',
'Corporate',
'Procurement',
'Default',
'Vacant',
'',
''
); Commit;
--
Insert Into smc_org_hierarchy_changes Values
('09/10/2018',
'123456',
'Smith, John',
'jsmith@oreon.com',
'Sourcing Specialist',
'Oreon Inc',
'Corporate',
'Procurement',
'Default',
'Vacant',
'',
''
); Commit;
--
Insert Into smc_org_hierarchy_changes Values
('09/11/2018',
'123456',
'Smith, John',
'jsmith@oreon.com',
'Sourcing Specialist',
'Oreon Inc',
'Corporate',
'Procurement',
'Default',
'Vacant',
'',
''
); Commit;
--
Insert Into smc_org_hierarchy_changes Values
('09/12/2018',
'123456',
'Smith, John',
'jsmith@oreon.com',
'Sourcing Specialist',
'Oreon Inc',
'Corporate',
'Procurement',
'Default',
'Vacant',
'',
''
); Commit;
--
Insert Into smc_org_hierarchy_changes Values
('09/13/2018',
'123456',
'Smith, John',
'jsmith@oreon.com',
'Sourcing Specialist',
'Oreon Inc',
'Corporate',
'Procurement',
'Default',
'Vacant',
'',
''
); Commit;
--
Insert Into smc_org_hierarchy_changes Values
('09/14/2018',
'123456',
'Smith, John',
'jsmith@oreon.com',
'Sourcing Specialist',
'Oreon Inc',
'Corporate',
'Procurement',
'Default',
'Vacant',
'',
''
); Commit;
--
Thanks and appreciate any help!
Naveen G.