Hi
I have an error in Stored Procedure MSSQL 2017
"
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or
when the subquery is used as an expression.
when I am using below Code
Pls advice me
Thank you
Maideen
What I have tried:
Stored Procedure
UPDATE [dbo].[AD_Staff_Attandance] SET TotHours =
(SELECT [dbo].[GetTotalWorkingHours] (DateOut,DateIn) AS TIMEWORKED_OUT_IN
FROM [dbo].[AD_Staff_Attandance] )
Function
ALTER FUNCTION [dbo].[GetTotalWorkingHours]
(
@DateFrom Datetime,
@DateTo Datetime
)
RETURNS DECIMAL(18,2)
AS
BEGIN
DECLARE @TotalWorkDays INT, @TotalTimeDiff DECIMAL(18, 2)
SET @TotalWorkDays = DATEDIFF(DAY, @DateFrom, @DateTo)
-(DATEDIFF(WEEK, @DateFrom, @DateTo) * 2)
-CASE
WHEN DATENAME(WEEKDAY, @DateFrom) = 'Sunday'
THEN 1
ELSE 0
END+CASE
WHEN DATENAME(WEEKDAY, @DateTo) = 'Saturday'
THEN 1
ELSE 0
END;
SET @TotalTimeDiff =
(
SELECT DATEDIFF(SECOND,
(
SELECT CONVERT(TIME, @DateFrom)
),
(
SELECT CONVERT(TIME, @DateTo)
)) / 3600.0
);
RETURN (SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff)
END