How to break a date range into 2 different rows?

Analytics
Enthusiast

How to break a date range into 2 different rows?

Hi,

I have Table 1
EmployeeNo StartDt EndDt
1 1/1/09 12/31/09
1 1/1/10 12/31/10
1 1/1/11 12/31/11

Table2
EmployeeNo ManagerStartDate ManagerEndDate
1 7/1/10 6/30/11

Employee 1 was promoted as Manager from 7/1/10 and 6/30/11 and then demoted back as employee starting 7/1/11
Results
Employee StartDate EndDate Designation
1 1/1/09 12/31/09 Employee

1 1/1/10 6/30/10 Employee
1 7/1/10 12/31/10 Manager

1 1/1/11 6/30/11 Manager
1 7/1/11 12/31/11 Employee

I want to break row 2 and 3 from Table1 into 2 different rows as illustrated above.
1 REPLY
Junior Supporter

Re: How to break a date range into 2 different rows?

This solution may fit:

BTEQ -- Enter your SQL request or BTEQ command:
SELECT *
FROM MY_DB.MY_TABLE
;

*** Query completed. 6 rows found. 3 columns returned.
*** Total elapsed time was 1 second.

ID_EMP START_DATE END_DATE
----------- ---------- ----------
1 2009-01-01 2009-07-31
1 2009-08-01 2009-10-31
1 2009-11-01 2010-01-31
1 2010-02-01 2010-07-31
1 2010-08-01 2011-02-01
1 2011-03-01 2011-12-31

BTEQ -- Enter your SQL request or BTEQ command:
SELECT ID_EMP,
START_DATE S_DATE,
CASE WHEN END_DATE > (START_DATE/10000)*10000 + 1231
THEN CAST((START_DATE/10000)*10000 + 1231 AS DATE)
ELSE END_DATE
END as E_DATE
FROM MY_DB.MY_TABLE
UNION
SELECT ID_EMP,
CASE WHEN START_DATE < (END_DATE/10000)*10000 + 0101
THEN CAST((END_DATE/10000)*10000 + 0101 AS DATE)
ELSE START_DATE
END as S_DATE,
END_DATE as E_DATE
FROM MY_DB.MY_TABLE;

*** Query completed. 8 rows found. 3 columns returned.
*** Total elapsed time was 1 second.

ID_EMP S_DATE E_DATE
----------- ---------- ----------
1 2009-01-01 2009-07-31
1 2009-08-01 2009-10-31
1 2009-11-01 2009-12-31
1 2010-01-01 2010-01-31
1 2010-02-01 2010-07-31
1 2010-08-01 2010-12-31
1 2011-01-01 2011-02-01
1 2011-03-01 2011-12-31

HTH.

Cheers.

Carlos.