SQL Query that shows new starts within the last 30 days and the next 30 days-CodePudding

Creating a report in Oracle OTBI to show any new starts within the last month days or the next month.

Currently I have an SQL query that shows the new starts within the last month days but unsure how to make it so that it also includes those that start within the next month.

SELECT "Person Names"."Full Name" saw_0,
       "Worker"."Person Start Date" saw_1,
       "Worker"."Termination Date" saw_2,
       "Manager"."Business Unit" saw_3
  FROM "Workforce Management - Person Real Time"
 WHERE ("Manager"."Business Unit" = ' ')
   AND ("Worker"."Person Start Date" >= (TIMESTAMPADD(SQL_TSI_MONTH, -1, CURRENT_DATE)))

CodePudding user response：

You may want to try this

SELECT    
"Person Names"."Full Name" saw_0,    
"Worker"."Person Start Date" saw_1,    
"Worker"."Termination Date" saw_2,    
"Manager"."Business Unit" saw_3    
FROM "Workforce Management - Person Real Time"    
where "Manager"."Business Unit" = ' '    
and "Worker"."Person Start Date" between sysdate -30 and sysdate  30

CodePudding user response：


CREATE TABLE employees (employee_id, first_name, last_name, hire_date) AS
SELECT 1,  'Alice', 'Abbot', DATE '2022-04-03'  FROM DUAL UNION ALL
SELECT 2, 'Beryl', 'Baron',DATE '2022-05-01' FROM DUAL UNION ALL
SELECT 3, 'Carol', 'Chase',DATE '2022-05-18' FROM DUAL UNION ALL
SELECT 4, 'Debra', 'Doris', DATE '2022-05-28' FROM DUAL UNION ALL
SELECT 5, 'Emily', 'Evans',DATE '2022-06-30' FROM DUAL UNION ALL
SELECT 6, 'Fiona', 'Frank',DATE '2022-06-11' FROM DUAL UNION ALL
SELECT 7, 'Gemma', 'Grace', DATE '2022-06-30' FROM DUAL;

SELECT * FROM EMPLOYEES WHERE 
hire_date BETWEEN TRUNC(SYSDATE)-30
AND
TRUNC(SYSDATE)  30;


EMPLOYEE_ID    FIRST_NAME    LAST_NAME    HIRE_DATE
2    Beryl    Baron    01-MAY-22
3    Carol    Chase    18-MAY-22
4    Debra    Doris    28-MAY-22
6    Fiona    Frank    11-JUN-22