| Author |
 Topic  |
|
|
flamblaster
Constraint Violating Yak Guru
USA
355 Posts |
 Posted - 11/28/2012 : 15:11:21
|
I'm trying to cull down records from a production database that are moving to a reporting database. Essentially, I'd like to reduce the number of repeatedly auto-generated employee status records saves down to what the true employee status should represent (what happened in reality, not just due to a design in the application that produces the data).
I have an example that shows a basic EmployeeStatus table variable below, the results, then the desired results. Hopefully this makes sense!
declare @EmployeeStatus table
( EmployeeStatusId int primary key identity not null
,EmployeeId int
,StatusType char(3)
,FromDate date
,ToDate date
)
insert into @EmployeeStatus(EmployeeId, StatusType, FromDate, ToDate)
values
(1,'NEW', '2012-01-01', '2012-01-31')
,(1,'ACT', '2012-02-01', '2012-02-29')
,(1,'ACT', '2012-03-01', '2012-03-31')
,(1,'ACT', '2012-04-01', '2012-04-30')
,(1,'LOA', '2012-05-01', '2012-05-31')
,(1,'ACT', '2012-06-01', '2012-06-30')
,(1,'ACT', '2012-07-01', '2012-07-31')
,(1,'ACT', '2012-08-01', NULL)
select EmployeeStatusId, Employeeid, StatusType, FromDate, ToDate, row_number() over (partition by Employeeid, StatusType order by FromDate) as Seq
from @EmployeeStatus
order by FromDate
(8 row(s) affected)
EmployeeStatusId Employeeid StatusType FromDate ToDate Seq
---------------- ----------- ---------- ---------- ---------- --------------------
1 1 NEW 2012-01-01 2012-01-31 1
2 1 ACT 2012-02-01 2012-02-29 1
3 1 ACT 2012-03-01 2012-03-31 2
4 1 ACT 2012-04-01 2012-04-30 3
5 1 LOA 2012-05-01 2012-05-31 1
6 1 ACT 2012-06-01 2012-06-30 4
7 1 ACT 2012-07-01 2012-07-31 5
8 1 ACT 2012-08-01 NULL 6
(8 row(s) affected)
--Would like to be able to show the results in the following format (grouping selectively)
(4 row(s) affected)
EmployeeStatusId Employeeid StatusType FromDate ToDate
---------------- ----------- ---------- ---------- ----------
1 1 NEW 2012-01-01 2012-01-31
2 1 ACT 2012-02-01 2012-04-30
3 1 LOA 2012-05-01 2012-05-31
4 1 ACT 2012-06-01 NULL
(4 row(s) affected)
(row_number just there as example...I was trying to see if I could row=row+1 join to be able to show whether the statustype changed from one record to another...but no dice so far!)
|
|
|
Elizabeth B. Darcy
Starting Member
United Kingdom
39 Posts |
Posted - 11/28/2012 : 17:44:44
|
Using your sample data, the following:;with cte as
(
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY Employeeid ORDER BY EmployeeStatusId)-
ROW_NUMBER() OVER(PARTITION BY Employeeid,StatusType ORDER BY EmployeeStatusId) AS Grp
FROM
@EmployeeStatus
)
SELECT
Employeeid,
StatusType,
MIN(FromDate) FromDate,
NULLIF(MAX(COALESCE(ToDate,'20990101')),'20990101') ToDate
FROM
cte
GROUP BY
Employeeid,
Grp,
StatusType
ORDER BY
FromDate;
________________________________________
-- Yes, I am indeed a fictional character. |
 |
|
|
flamblaster
Constraint Violating Yak Guru
USA
355 Posts |
Posted - 11/28/2012 : 19:34:08
|
| Genius! Thank you so much! That completely makes sense. I had used the row_number function and did a self join with the B.Seq=A.Seq+1 method to try to get to where you ended up. This is great...thanks again. |
|
|
|
Elizabeth B. Darcy
Starting Member
United Kingdom
39 Posts |
Posted - 11/29/2012 : 19:27:19
|
You are quite welcome!
That method of grouping is a fairly well-known pattern - you will find descriptions and examples if you Google for "islands and gaps"
________________________________________
-- Yes, I am indeed a fictional character. |
|
|
|
flamblaster
Constraint Violating Yak Guru
USA
355 Posts |
Posted - 11/29/2012 : 20:01:29
|
Oh, ok...I wasn't familiar with the term before. I had done a ton of research in the wrong place I guess.
Thanks again |
|
|
| |
Topic |
|
|
|
Selectively Group
