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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 flatten out a role change log
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ethan1701
Starting Member

3 Posts

Posted - 08/22/2012 :  07:53:00  Show Profile  Reply with Quote
Hi,
I have a table with the following fields:
USERID
ROLE
FROM_DATE
TO_DATE (could be null)

A user can have more than one role at a time, and that's the cause of my troubles.

For simplicity's sake, let's assume I have a single user, and these records:
ROLE	FROM		TO
1	1/1/2012	8/8/2012
2	2/2/2012	5/5/2012
3	6/6/2012	NULL


I need to create a derived table that will store the roles the user played at any given time. So from the data above, I need to get to:
ROLES	FROM		TO
1	1/1/2012	2/2/2012
1, 2	2/2/2012	5/5/2012
1	5/5/2012	6/6/2012
1, 3	6/6/2012	8/8/2012
3	8/8/2012	NULL


I've tried every type of join and common table expression that I could think of, but I just can't get this to work.

For the sake of the matter, assume I can create views , add any column I need and add temporary tables in the SQL Server database.

Thanks,
-Ethan

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/22/2012 :  08:12:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- Prepare sample data
DECLARE	@Sample TABLE
	(
		[Role] INT NOT NULL,
		[From] DATE NOT NULL,
		[To] DATE NULL
	);

INSERT	@Sample
	(
		[Role],
		[From],
		[To]
	)
VALUES	(1, '20120101', '20120808'),
	(2, '20120202', '20120505'),
	(3, '20120606', NULL);

-- Solution by SwePeso
;WITH cteSource(theDate)
AS (
	SELECT	u.theDate
	FROM	@Sample AS s
	UNPIVOT	(
			theDate
			FOR theColumn IN (s.[From], s.[To])
		) AS u
)
SELECT		STUFF(w.Data, 1, 2, '') AS Roles,
		s.theDate AS [From],
		f.theDate AS [To]
FROM		cteSource AS s
OUTER APPLY	(
			SELECT TOP(1)	x.theDate
			FROM		cteSource AS x
			WHERE		x.theDate > s.theDate
			ORDER BY	x.theDate
		) AS f(theDate)
OUTER APPLY	(
			SELECT DISTINCT	', ' + CAST(q.[Role] AS VARCHAR(12))
			FROM		@Sample AS q
			WHERE		s.theDate >= q.[From]
					AND (s.theDate < q.[To] OR q.[To] IS NULL)
			ORDER BY	', ' + CAST(q.[Role] AS VARCHAR(12))
			FOR XML		PATH('')
		) AS w(Data)
ORDER BY	s.theDate;



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

ethan1701
Starting Member

3 Posts

Posted - 08/22/2012 :  08:19:24  Show Profile  Reply with Quote
wow!
That sure does the trick!
Thanks for the quick response. I'll need an hour or two to understand this...

-Ethan
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/22/2012 :  08:25:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Sure.
I wrote it as a one-query solution so that you can convert it to a view if you want to.

CREATE VIEW dbo.vwWhatever
AS

WITH cteSource(theDate)
AS (
	SELECT	u.theDate
	FROM	@Sample AS s
	UNPIVOT	(
			theDate
			FOR theColumn IN (s.[From], s.[To])
		) AS u
)
SELECT		STUFF(w.Data, 1, 2, '') AS Roles,
		s.theDate AS [From],
		f.theDate AS [To]
FROM		cteSource AS s
OUTER APPLY	(
			SELECT TOP(1)	x.theDate
			FROM		cteSource AS x
			WHERE		x.theDate > s.theDate
			ORDER BY	x.theDate
		) AS f(theDate)
OUTER APPLY	(
			SELECT DISTINCT	', ' + CAST(q.[Role] AS VARCHAR(12))
			FROM		@Sample AS q
			WHERE		s.theDate >= q.[From]
					AND (s.theDate < q.[To] OR q.[To] IS NULL)
			ORDER BY	', ' + CAST(q.[Role] AS VARCHAR(12))
			FOR XML		PATH('')
		) AS w(Data);



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

ethan1701
Starting Member

3 Posts

Posted - 01/13/2013 :  06:43:57  Show Profile  Reply with Quote
Hi SwePeso,
I have to admit that the query you wrote here is a few leagues above my head!
Can you explain how you would do this if you had several users in the table, and you needed to create a role-log for each user?

Thanks!
-Ethan
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.06 seconds. Powered By: Snitz Forums 2000