Untitled

mail@pastecode.io avatar
unknown
sqlserver
2 years ago
1.4 kB
2
Indexable
Never
ALTER proc [dbo].[GetSalaryMain]
AS
DECLARE @CompanyId nvarchar(100); -- Müəssisə Id
DECLARE @currentMonth int = DATEPART(MONTH,GETDATE()); -- Cari ay
DECLARE @currentYear int = DATEPART(YEAR,GETDATE()) -- Cari il
DECLARE @calendarDays int; -- Təqvim günləri
DECLARE @allWorkDays int; -- Ümumi iş günlərinin sayı
DECLARE @allWorkHours int; -- Ümumi iş saatı 


DECLARE SalaryMainCursor CURSOR
FOR             
SELECT ID FROM HR_COMPANY
OPEN SalaryMainCursor
FETCH NEXT FROM SalaryMainCursor INTO @CompanyId
WHILE @@FETCH_STATUS = 0
  BEGIN
    IF	NOT EXISTS(select *from HR_SALARY_MAIN where COMPANY_ID = @CompanyId and MONTH_ = @currentMonth and YEAR_=@currentYear)
		BEGIN
			INSERT INTO HR_SALARY_MAIN (MONTH_,YEAR_,COMPANY_ID) VALUES(@currentMonth,@currentYear,@CompanyId)
		END
    FETCH NEXT FROM SalaryMainCursor INTO @CompanyId
  END
CLOSE SalaryMainCursor
DEALLOCATE SalaryMainCursor


exec CalcSalaryMainWorkDays
@id = 1, -- Burada olan id s.Id olmalıdır ki, hər gələn sorğu nəticəsində olan id əsasən məlumatlar gəlsin
@calendarDays = @calendarDays output,
@allWorkDays= @allWorkDays output,
@allWorkHours =  @allWorkHours output

SELECT 
s.ID,
dbo.GetMonthName(s.MONTH_) as 'MONTH_',
s.YEAR_,
c.COMP_NAME,
@calendarDays,
@allWorkDays,
@allWorkHours
FROM HR_SALARY_MAIN s
inner join HR_COMPANY c
on c.ID = s.COMPANY_ID