SQL 2019 I'm having difficulty to generate similar groups by date.
This is the source table and image what is expected
IF (SELECT OBJECT_ID('tempdb..#EmpType')) IS NOT NULL
DROP TABLE #EmpType
CREATE TABLE #EmpType
(
EMPID INT,
EmpType VARCHAR(10),
PayPeriod DATETIME
)
INSERT INTO #EmpType (EMPID, EmpType, PayPeriod)
SELECT 1, 'Contractor', '2019-12-20'
UNION ALL
SELECT 1, 'Contractor', '2020-01-08'
UNION ALL
SELECT 1, 'Contractor', '2020-01-22'
UNION ALL
SELECT 1, 'Perm', '2020-05-01'
UNION ALL
SELECT 1, 'Perm', '2020-05-10'
UNION ALL
SELECT 1, 'Contractor', '2020-06-10'
UNION ALL
SELECT 1, 'Contractor', '2020-06-20'
INSERT INTO #EmpType (EMPID, EmpType, PayPeriod)
SELECT 55, 'Temp', '2019-12-20'
UNION ALL
SELECT 55, 'Temp', '2020-01-08'
UNION ALL
SELECT 55, 'Temp', '2020-01-22'
UNION ALL
SELECT 55, 'Perm', '2020-02-05'
UNION ALL
SELECT 55, 'Perm', '2020-05-01'
UNION ALL
SELECT 55, 'Perm', '2020-05-10'
UNION ALL
SELECT 55, 'Temp', '2020-06-10'
UNION ALL
SELECT 55, 'Temp', '2020-06-20'
UNION ALL
SELECT 55, 'Temp', '2020-06-29'
SELECT * FROM #EmpType
I need to get a result that has 4 columns: EmpID, EmpType, StartDate, EndDate
| EmpID | EmpType | StartDate | EndDate |
|---|---|---|---|
| 1 | Contractor | 12/20/2019 | 1/22/2020 |
| 1 | Perm | 5/1/2020 | 5/10/2020 |
| 1 | Contractor | 6/20/2020 | 6/20/2020 |
| 55 | Temp | 12/20/2019 | 12/20/2019 |
| 55 | Perm | 2/5/2020 | 5/10/2020 |
| 55 | Temp | 6/10/2020 | 6/29/2020 |
I was using MIN and MAX date and ROW_NUMBER (PARTITION BY ), but it combines EmpType into same group even there's other EmpType entry in between
Thank you