All geek questions in one place

Create view based on hierarchy / used by CTE

Can I create a view based on the / cte hierarchy?

I saw an example of how to generate a result set based on a recursive query link .
I attached ddl and instructions.

Thanks
Elmer

CREATE TABLE [dbo].[XHR_PERSON](
[PERSON_ID] [bigint] NOT NULL,
[LAST_NAME] [varchar](100) NOT NULL,
[FIRST_NAME] [varchar](100)
,EFFECTIVE_START_DATE Date
,EFFECTIVE_END_DATE Date)

CREATE TABLE [dbo].[XHR_EMPLOYMENT](
[PERSON_ID] [bigint] NOT NULL,
[EMPLOYEE_NUMBER] [varchar](100) NULL,
[SUPERVISOR_ID] [bigint] NULL
,EFFECTIVE_START_DATE Date
,EFFECTIVE_END_DATE Date)

insert into XHR_PERSON 
select 1, 'SMY', null, '1990-01-01','9999-12-31' UNION ALL 
select 2, 'JSB',null, '1990-01-01','9999-12-31' union all
select 3, 'LFG',null, '1990-01-01','9999-12-31' union all
select 4, 'Elmer',null, '1990-01-01','9999-12-31' union all
select 5, 'Jon',null, '1990-01-01','9999-12-31' union all
select 6, 'Anne',null, '1990-01-01','9999-12-31' union all
select 7, 'Teddy',null, '1990-01-01','9999-12-31' union all
select 8, 'Alex',null , '1990-01-01','9999-12-31'union all
select 9, 'Jeff',null, '1990-01-01','9999-12-31'
update XHR_PERSON set first_name = 'A'

insert into XHR_EMPLOYMENT
select 1, '111',null, '1990-01-01','9999-12-31' UNION ALL 
select 2, '222',1, '1990-01-01','9999-12-31' union all
select 3, '333',1, '1990-01-01','9999-12-31' union all
select 4, '444',2, '1990-01-01','9999-12-31' union all
select 5, '555',2, '1990-01-01','9999-12-31' union all
select 6, '666',4, '1990-01-01','9999-12-31' union all
select 7, '777',3, '1990-01-01','9999-12-31' union all
select 8, '888',3, '1990-01-01','9999-12-31' union all
select 9, '999',8, '1990-01-01','9999-12-31'


CREATE VIEW dbo.HR_DIRECTREPORTSV as 
WITH xxDirectReports (Supervisor_id, Person_id, Employee_number, Employee_name, Supervisor_Empno, Supervisor_Name, Level1)

AS
(
SELECT hre.Supervisor_id
    ,hre.Person_id
    ,hre.Employee_number
    ,hrp.last_name+', '+hrp.first_name  Employee_Name
    ,hrpx.employee_number       Supervisor_Empno
    ,hrpx.fullname          Supervisor_Name
    ,0 AS Level1
FROM dbo.xhr_employment AS hre left join (select hrp1.person_id,hre1.employee_number                                                    ,(hrp1.last_name+', '+hrp1.first_name) as fullname
                                           from dbo.xHR_PERSON hrp1
                                                ,dbo.xhr_employment hre1
                                           where hrp1.person_id = hre1.person_id
                                           AND   getdate() between hrp1.effective_start_date 
                                                            and    hrp1.effective_end_date
                                        ) hrpx on hre.supervisor_id = hrpx.person_id
    ,dbo.xHR_PERSON     AS hrp 
WHERE hre.person_id = hrp.person_id 
AND   GETDATE() between hrp.effective_start_date and hrp.effective_end_date
AND   GETDATE() between hrp.effective_start_date and hrp.effective_end_date
--AND   hrpx.person_id = 1
UNION ALL
SELECT   hre.Supervisor_id
        ,hre.Person_id
        ,hre.Employee_number
        ,hrp.last_name+', '+hrp.first_name  Employee_Name
        ,hrpx.employee_number               Supervisor_Empno
        ,hrpx.fullname                      Supervisor_Name
        ,Level1+1
FROM dbo.xhr_employment AS hre inner join (select hrp1.person_id    
                                                ,hre1.employee_number
                                                ,(hrp1.last_name+', '+hrp1.first_name) as fullname
                                           from dbo.xHR_PERSON hrp1
                                                ,dbo.xhr_employment hre1
                                           where hrp1.person_id = hre1.person_id
                                           AND   getdate() between hrp1.effective_start_date 
                                                            and    hrp1.effective_end_date
                                        ) hrpx on hre.supervisor_id = hrpx.person_id
                                INNER JOIN xxDirectReports AS xx ON hre.Supervisor_id = xx.Person_id
    ,dbo.xHR_PERSON     AS hrp 
WHERE hre.person_id = hrp.person_id 
AND   GETDATE() between hrp.effective_start_date and hrp.effective_end_date
AND   GETDATE() between hrp.effective_start_date and hrp.effective_end_date)
+3
source share
2 answers

After specifying WITH(behind the closing bracket) you need to select all the values ​​from the CTE:

select * from xxDirectReports

This is the actual selection request, which is a view.

GO, SQL Server Management Studio:

if not OBJECT_ID('XHR_PERSON', 'Table') is null drop table XHR_PERSON
if not OBJECT_ID('XHR_EMPLOYMENT', 'Table') is null drop table XHR_EMPLOYMENT
if not OBJECT_ID('HR_DIRECTREPORTSV', 'View') is null drop view HR_DIRECTREPORTSV

CREATE TABLE [dbo].[XHR_PERSON](
[PERSON_ID] [bigint] NOT NULL,
[LAST_NAME] [varchar](100) NOT NULL,
[FIRST_NAME] [varchar](100)
,EFFECTIVE_START_DATE Date
,EFFECTIVE_END_DATE Date)

CREATE TABLE [dbo].[XHR_EMPLOYMENT](
[PERSON_ID] [bigint] NOT NULL,
[EMPLOYEE_NUMBER] [varchar](100) NULL,
[SUPERVISOR_ID] [bigint] NULL
,EFFECTIVE_START_DATE Date
,EFFECTIVE_END_DATE Date)

insert into XHR_PERSON 
select 1, 'SMY', null, '1990-01-01','9999-12-31' UNION ALL 
select 2, 'JSB',null, '1990-01-01','9999-12-31' union all
select 3, 'LFG',null, '1990-01-01','9999-12-31' union all
select 4, 'Elmer',null, '1990-01-01','9999-12-31' union all
select 5, 'Jon',null, '1990-01-01','9999-12-31' union all
select 6, 'Anne',null, '1990-01-01','9999-12-31' union all
select 7, 'Teddy',null, '1990-01-01','9999-12-31' union all
select 8, 'Alex',null , '1990-01-01','9999-12-31'union all
select 9, 'Jeff',null, '1990-01-01','9999-12-31'
update XHR_PERSON set first_name = 'A'

insert into XHR_EMPLOYMENT
select 1, '111',null, '1990-01-01','9999-12-31' UNION ALL 
select 2, '222',1, '1990-01-01','9999-12-31' union all
select 3, '333',1, '1990-01-01','9999-12-31' union all
select 4, '444',2, '1990-01-01','9999-12-31' union all
select 5, '555',2, '1990-01-01','9999-12-31' union all
select 6, '666',4, '1990-01-01','9999-12-31' union all
select 7, '777',3, '1990-01-01','9999-12-31' union all
select 8, '888',3, '1990-01-01','9999-12-31' union all
select 9, '999',8, '1990-01-01','9999-12-31';

GO
CREATE VIEW dbo.HR_DIRECTREPORTSV as
WITH xxDirectReports (Supervisor_id, Person_id, Employee_number, Employee_name, Supervisor_Empno, Supervisor_Name, Level1)
AS
(
SELECT hre.Supervisor_id
    ,hre.Person_id
    ,hre.Employee_number
    ,hrp.last_name+', '+hrp.first_name  Employee_Name
    ,hrpx.employee_number       Supervisor_Empno
    ,hrpx.fullname          Supervisor_Name
    ,0 AS Level1
FROM dbo.xhr_employment AS hre left join (select hrp1.person_id,hre1.employee_number                                                    ,(hrp1.last_name+', '+hrp1.first_name) as fullname
                                           from dbo.xHR_PERSON hrp1
                                                ,dbo.xhr_employment hre1
                                           where hrp1.person_id = hre1.person_id
                                           AND   getdate() between hrp1.effective_start_date 
                                                            and    hrp1.effective_end_date
                                        ) hrpx on hre.supervisor_id = hrpx.person_id
    ,dbo.xHR_PERSON     AS hrp 
WHERE hre.person_id = hrp.person_id 
AND   GETDATE() between hrp.effective_start_date and hrp.effective_end_date
AND   GETDATE() between hrp.effective_start_date and hrp.effective_end_date
--AND   hrpx.person_id = 1
UNION ALL
SELECT   hre.Supervisor_id
        ,hre.Person_id
        ,hre.Employee_number
        ,hrp.last_name+', '+hrp.first_name  Employee_Name
        ,hrpx.employee_number               Supervisor_Empno
        ,hrpx.fullname                      Supervisor_Name
        ,Level1+1
FROM dbo.xhr_employment AS hre inner join (select hrp1.person_id    
                                                ,hre1.employee_number
                                                ,(hrp1.last_name+', '+hrp1.first_name) as fullname
                                           from dbo.xHR_PERSON hrp1
                                                ,dbo.xhr_employment hre1
                                           where hrp1.person_id = hre1.person_id
                                           AND   getdate() between hrp1.effective_start_date 
                                                            and    hrp1.effective_end_date
                                        ) hrpx on hre.supervisor_id = hrpx.person_id
                                INNER JOIN xxDirectReports AS xx ON hre.Supervisor_id = xx.Person_id
    ,dbo.xHR_PERSON     AS hrp 

WHERE hre.person_id = hrp.person_id 
AND   GETDATE() between hrp.effective_start_date and hrp.effective_end_date
AND   GETDATE() between hrp.effective_start_date and hrp.effective_end_date)
select * from xxDirectReports;

GO
select * from HR_DIRECTREPORTSV;

Update:

, , , , LFG, SMY?

! . CTE, , , , . , , ... .

, Path WITH , -, :

convert(nvarchar(256), RTRIM(convert(nvarchar(12), hre.PERSON_ID))) Path

-, :

convert(nvarchar(256), rtrim(Path) + '.' + RTRIM(convert(nvarchar(12), hre.PERSON_ID))) Path

, (LFG) , , :

select * from HR_DIRECTREPORTSV
where Path = '3' or Path like '3.%'

:

Path   Supervisor_id   Person_id   Employee_number   Employee_name   Supervisor_Empno   Supervisor_Name   Level1
3   1   3   333 LFG, A  111 SMY, A  0
3.7 3   7   777 Teddy, A    333 LFG, A  1
3.8 3   8   888 Alex, A 333 LFG, A  1
3.8.9   8   9   999 Jeff, A 888 Alex, A 2
+3

, - -

CREATE VIEW [mySchema].vwItem
AS
    WITH cteItem     
    AS     
    (    
        SELECT      
        item_id     AS 'ItemID',    
        item_level    AS 'ItemLevel',     
        item_number_type  AS 'ItemNumberType',    
        CASE item_level     
            WHEN 1 THEN item_id     
            WHEN 2 THEN item_parent_id     
            WHEN 3 THEN item_grandparent_id
        ELSE item_id     
        END      AS 'ItemParent',    
        FROM     
        [mySchema].[ITEM]
    )    

    SELECT     
        child.*,    
        parent.item_id   AS 'ParentId',    
        parent.desc  AS 'ParentDescription'    
    FROM     
        cteItem child  
    LEFT OUTER JOIN [mySchema].[ITEM] parent ON     
        child.ItemParent = parent.item_id
+2

Source: https://habr.com/ru/post/1757952/

More articles:


All Articles