Selectively Group - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Selectively Group

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-11-28 : 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

39 Posts

Posted - 2012-11-28 : 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

384 Posts

Posted - 2012-11-28 : 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

39 Posts

Posted - 2012-11-29 : 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

384 Posts

Posted - 2012-11-29 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources