Hello,
As a beginner, I allow myself to ask you for advice because I cannot manage to make a total following a request that I made. This query executes a "Scalar Valued Function" which lists in a "Salarie" table the people who have a job seniority of more than 10 years according to the calendar year that we specify. Here is the function and the result below.
I don’t know how to do the calculation to get the total, can you advise me please
Thanking you in advance
BaV
USE [SYGECO2]
GO
/****** Object: UserDefinedFunction [dbo].[SalarieAnciennetePlus10] Script Date: 9/1/2022 12:40:24 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[SalarieAnciennetePlus10]
(
@IdSalarie char(36),
@AnneeCivile int
)
returns bit
as
begin
-- ************************************************************************************************************
-- Indique si un salarié a un ancieneté > 10 ans
-- Usage:
-- select Nom, Prenom, DateEntree, Diplome, dbo.SalarieAnciennetePlus10(IdSalarie, 2021) as [Salarié anciennete plus de 10 ans] from Salarie
-- ************************************************************************************************************
declare @RetValue as bit = 0
declare @anciennete as int
declare @diplome as bit
select
@anciennete = datediff(yy,DateEntree,datefromparts(@AnneeCivile, 1, 1)),
@diplome = Diplome
from
Salarie
where
IdSalarie = @IdSalarie
if @anciennete>10
begin
set @retValue = 1 -- Salarié sans qualification ou ancienneté > 10 ans
end
return @retValue
