Hey guys, feeling kinda dumb I can't figure this one out on my own. 🙁 Hopefully you can advise. Here's some sample data:
IF OBJECT_ID('tempdb..#EMP_TEST') IS NOT NULL
DROP TABLE #EMP_TEST
CREATE TABLE #EMP_TEST
EID SMALLINT NOT NULL,
Stat CHAR(1) NOT NULL,
StartDate DATE NOT NULL,
EndDate DATE NOT NULL
INSERT INTO #EMP_TEST
SELECT 12345, 'A', '20111215', '20120101' UNION ALL
SELECT 12345, 'A', '20120101', '20120118' UNION ALL
SELECT 12345, 'A', '20120118', '20120201' UNION ALL
SELECT 12345, 'A', '20120201', '20120321' UNION ALL
SELECT 12345, 'A', '20120321', '20120401' UNION ALL
SELECT 12345, 'A', '20120401', '20121109' UNION ALL
SELECT 12345, 'L', '20121109', '20130101' UNION ALL
SELECT 12345, 'L', '20130101', '20130103' UNION ALL
SELECT 12345, 'A', '20130103', '20130203' UNION ALL
SELECT 12345, 'R', '20130203', '20141210'
This is a table that stores employee status data. A = Active, L = Leave, etc. What I need to do is consolidate these contiguous blocks into single rows. So, expected output would look like this:
The issue that's throwing me is that the person goes from A to L and back to A. So if I try to partition by EID, STAT in any type of windowing function, I get all screwd up. What is the sexy solution to this problem? Dazzle me please, SQL wizards!
"If I had been drinking out of that toilet, I might have been killed."