SQLTeam.com | Weblogs | Forums

SQL Request Total

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

end


You can simply add SUM to get the total.

SELECT SUM(dbo.[SalarieAnciennetePlus10]) AS [Salarie Anciennete Plus10 ans]
FROM Salarie.

Hi RogierPronk,

Thanks a lot for your advice and link, I will give a try