SQL 2019 -Group by Id and Date field

47 Views Asked by At

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

Expected result formated

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

0

There are 0 best solutions below