November 19, 2012 at 3:21 pm
Hi,
I have a table that looks like the following:
CREATE TABLE [dbo].[EmployeePerms](
[EmployeeGroup] [varchar](50) NULL,
[System1] [varchar](1) NULL,
[System2] [varchar](1) NULL,
[System3] [varchar](1) NULL,
[status] [varchar](50) NULL
) ON [PRIMARY]
INSERT [dbo].[EmployeePerms] ([EmployeeGroup], [System1], [System2], [System3], [status]) VALUES (N'Sales', N'X', N'X', NULL, N'Bad')
INSERT [dbo].[EmployeePerms] ([EmployeeGroup], [System1], [System2], [System3], [status]) VALUES (N'Sales', N'X', NULL, N'X', N'Good')
INSERT [dbo].[EmployeePerms] ([EmployeeGroup], [System1], [System2], [System3], [status]) VALUES (N'Ssales', N'X', N'X', N'X', N'Bad')
[/code="sql"]
I'll eventually join it to a table that includes sales employees and it will indicate whether or not their permission set is correct. If it's not correct, I'd like to have a column that indicates what they need to have added or deleted relative to the "Good" permission record. In the example above, the query would return something like the following:
CREATE TABLE [dbo].[EmployeePermsResults](
[EmployeeGroup] [varchar](50) NULL,
[System1] [varchar](1) NULL,
[System2] [varchar](1) NULL,
[System3] [varchar](1) NULL,
[status] [varchar](50) NULL,
[action] [varchar](50) NULL
) ON [PRIMARY]
INSERT [dbo].[EmployeePermsResults] ([EmployeeGroup], [System1], [System2], [System3], [status], [action]) VALUES (N'Sales', N'X', N'X', NULL, N'Bad', N'Delete System2, Add System3')
INSERT [dbo].[EmployeePermsResults] ([EmployeeGroup], [System1], [System2], [System3], [status], [action]) VALUES (N'Sales', N'X', NULL, N'X', N'Good', N'None')
INSERT [dbo].[EmployeePermsResults] ([EmployeeGroup], [System1], [System2], [System3], [status], [action]) VALUES (N'Ssales', N'X', N'X', N'X', N'Bad', N'Delete System2')
[/code="sql"]
I know there are other ways to do this, but I already have the data in this form, so I wanted to see if anyone had any clever ways to accomplish this. Any ideas?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
November 19, 2012 at 4:06 pm
There is no explanation of the business rules so this a shot in the dark. It does produce the same results as you stated based on the sample data provided.
select *, case when status = 'Bad' then 'Delete System 2' + case when System3 IS null then ', Add System 3' else '' end else 'None' end as Action from EmployeePerms
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 19, 2012 at 4:25 pm
Assuming that there are rules for each of the Employee groups
You create a rule table
CREATE TABLE [dbo].[EmployeePermsRules] (
[EmployeeGroup] [varchar](50) NOT NULL,
[System1] [varchar](1) NULL,
[System2] [varchar](1) NULL,
[System3] [varchar](1) NULL
)
INSERT [dbo].[EmployeePermsRules] ([EmployeeGroup], [System1], [System2], [System3]) VALUES (N'Sales', N'X', NULL, N'X')
You can then construct a query similar to the following to get the actions
select substring(
case
when r.System1 is not null and e.System1 is null then ', Add System1'
when r.System1 is null and e.System1 is not null then ', Delete System1'
else ''
end +
case
when r.System2 is not null and e.System2 is null then ', Add System2'
when r.System2 is null and e.System2 is not null then ', Delete System2'
else ''
end +
case
when r.System3 is not null and e.System3 is null then ', Add System3'
when r.System3 is null and e.System3 is not null then ', Delete System3'
else ''
end,3,100) actions
from [EmployeePerms] e
inner join [EmployeePermsRules] r on e.[EmployeeGroup] = r.[EmployeeGroup]
There are probably better ways to do this if you use BIT columns for the System1 - 3 columns.
November 19, 2012 at 5:26 pm
Celko,
You kill me with your responses...
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply