View: project_open_access_view

Columns
	Column Name	Data Type
1.	project_key	decimal(15)
2.	project_manager_open	varchar(1)
3.	project_viewer_open	varchar(1)
4.	resource_planner_open	varchar(1)
5.	resource_requestor_open	varchar(1)
6.	resource_assigner_open	varchar(1)
7.	billing_manager_open	varchar(1)
8.	billing_viewer_open	varchar(1)
9.	project_pr_viewer_open	varchar(1)
10.	project_po_viewer_open	varchar(1)
11.	project_document_viewer_open	varchar(1)

Table/Column Dependencies
Table	Column
project	project_key
project_restrict_access	project_key
	role_key

SQL Server Create Statement
create view project_open_access_view as select project_key, coalesce(max(case when role_key = 3 then 'N' end), 'Y') as project_manager_open, coalesce(max(case when role_key = 14 then 'N' end), 'Y') as project_viewer_open, coalesce(max(case when role_key = 15 then 'N' end), 'Y') as resource_planner_open, coalesce(max(case when role_key = 17 then 'N' end), 'Y') as resource_requestor_open, coalesce(max(case when role_key = 18 then 'N' end), 'Y') as resource_assigner_open, coalesce(max(case when role_key = 19 then 'N' end), 'Y') as billing_manager_open, coalesce(max(case when role_key = 20 then 'N' end), 'Y') as billing_viewer_open, coalesce(max(case when role_key = 44 then 'N' end), 'Y') as project_pr_viewer_open, coalesce(max(case when role_key = 48 then 'N' end), 'Y') as project_po_viewer_open, coalesce(max(case when role_key = 53 then 'N' end), 'Y') as project_document_viewer_open from project_restrict_access group by project_key union all select project_key, 'Y' as project_manager_open, 'Y' as project_viewer_open, 'Y' as resource_planner_open, 'Y' as resource_requestor_open, 'Y' as resource_assigner_open, 'Y' as billing_manager_open, 'Y' as billing_viewer_open, 'Y' as project_pr_viewer_open, 'Y' as project_po_viewer_open, 'Y' as project_document_viewer_open from project where project_key not in (select project_key from project_restrict_access)

SQL Server Create Statement

create view project_open_access_view as
select
    project_key,
    coalesce(max(case when role_key = 3 then 'N' end), 'Y') as project_manager_open,
    coalesce(max(case when role_key = 14 then 'N' end), 'Y') as project_viewer_open,
    coalesce(max(case when role_key = 15 then 'N' end), 'Y') as resource_planner_open,
    coalesce(max(case when role_key = 17 then 'N' end), 'Y') as resource_requestor_open,
    coalesce(max(case when role_key = 18 then 'N' end), 'Y') as resource_assigner_open,
    coalesce(max(case when role_key = 19 then 'N' end), 'Y') as billing_manager_open,
    coalesce(max(case when role_key = 20 then 'N' end), 'Y') as billing_viewer_open,
    coalesce(max(case when role_key = 44 then 'N' end), 'Y') as project_pr_viewer_open,
    coalesce(max(case when role_key = 48 then 'N' end), 'Y') as project_po_viewer_open,
    coalesce(max(case when role_key = 53 then 'N' end), 'Y') as project_document_viewer_open
from project_restrict_access
group by project_key
union all
select
   project_key,
   'Y' as project_manager_open,
   'Y' as project_viewer_open,
   'Y' as resource_planner_open,
   'Y' as resource_requestor_open,
   'Y' as resource_assigner_open,
   'Y' as billing_manager_open,
   'Y' as billing_viewer_open,
   'Y' as project_pr_viewer_open,
   'Y' as project_po_viewer_open,
   'Y' as project_document_viewer_open
from project where project_key not in (select project_key from project_restrict_access)