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.
Author |
Topic |
ethan1701
Starting Member
3 Posts |
Posted - 2012-08-22 : 07:53:00
|
Hi,I have a table with the following fields:USERIDROLEFROM_DATETO_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 TO1 1/1/2012 8/8/20122 2/2/2012 5/5/20123 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 TO1 1/1/2012 2/2/20121, 2 2/2/2012 5/5/20121 5/5/2012 6/6/20121, 3 6/6/2012 8/8/20123 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 dataDECLARE @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 sOUTER 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" |
|
|
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 |
|
|
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.vwWhateverASWITH 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 sOUTER 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" |
|
|
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 |
|
|
|
|
|
|
|