SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Selectively Group
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

flamblaster
Constraint Violating Yak Guru

380 Posts

Posted - 11/28/2012 :  15:11:21  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
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.
Go to Top of Page

flamblaster
Constraint Violating Yak Guru

380 Posts

Posted - 11/28/2012 :  19:34:08  Show Profile  Reply with Quote
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.
Go to Top of Page

Elizabeth B. Darcy
Starting Member

United Kingdom
39 Posts

Posted - 11/29/2012 :  19:27:19  Show Profile  Reply with Quote
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.
Go to Top of Page

flamblaster
Constraint Violating Yak Guru

380 Posts

Posted - 11/29/2012 :  20:01:29  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000