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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 flatten out a role change log

Author  Topic 

ethan1701
Starting Member

3 Posts

Posted - 2012-08-22 : 07:53:00
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

30421 Posts

Posted - 2012-08-22 : 08:12:23
[code]-- 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;[/code]


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

ethan1701
Starting Member

3 Posts

Posted - 2012-08-22 : 08:19:24
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

30421 Posts

Posted - 2012-08-22 : 08:25:58
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 - 2013-01-13 : 06:43:57
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
   

- Advertisement -