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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
30421 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
30421 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  
 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.17 seconds. Powered By: Snitz Forums 2000