GetTabelDiagram

USE [IDES_HRDB]
GO
/****** Object:  StoredProcedure [dbo].[GetTabelDiagram]    Script Date: 12.01.2023 10:34:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[GetTabelDiagram]
AS
SELECT 
e.ID,
e.CODE,
dbo.fn_GetEmpFullName(e.ID) as 'EMP',
s.POSITION_NAME,
p.COMP_NAME,
e.WORK_BEGDATE,
DATEPART(YEAR,GETDATE()) as 'YEAR',
DATEPART(MONTH,GETDATE()) as 'MONTH',
dbo.GetMonthName(DATEPART(MONTH,GETDATE())) as 'MONTH_NAME'
	
FROM HR_EMPLOYEES e
inner JOIN HR_EMP_CONTRATS c
on e.ID = c.PARENT_ID
inner join HR_STAFFTABLE s
on s.ID = e.POSSITION_ID
inner join HR_COMPANY p
on p.ID = e.COMPANY_ID
left join HR_CONTRATS_FINISH f
on e.ID = f.EMP_ID
where GETDATE() >= e.WORK_BEGDATE OR (e.WORK_BEGDATE > GETDATE() and MONTH(e.WORK_BEGDATE) = MONTH(GETDATE()) and YEAR(e.WORK_BEGDATE) = YEAR(GETDATE()))
UNION ALL
SELECT 
e.ID,
e.CODE,
dbo.fn_GetEmpFullName(e.ID) as 'EMP',
s.POSITION_NAME,
p.COMP_NAME,
e.WORK_BEGDATE,
DATEPART(YEAR,GETDATE()) as 'YEAR',
DATEPART(MONTH,GETDATE()) as 'MONTH',
dbo.GetMonthName(DATEPART(MONTH,GETDATE())) as 'MONTH_NAME'
	
FROM HR_EMPLOYEES e
inner JOIN HR_CONTRATS_FINISH f
on e.ID = f.EMP_ID
inner join HR_STAFFTABLE s
on s.ID = e.POSSITION_ID
inner join HR_COMPANY p
on p.ID = e.COMPANY_ID
WHERE MONTH(f.DATE_) = MONTH(GETDATE()) AND YEAR(f.DATE_) = YEAR(GETDATE())