-- Compute average value to two decimal
values and avoid divide by zero error
Create FUNCTION [dbo].[getPercent]
(
-- Add the parameters for the function here
@valueToCompute
decimal,
@TotalValue decimal
)
RETURNS numeric(10,2)
AS
BEGIN
Declare @result numeric(10,2);
if (@valueToCompute =0 or @TotalValue = 0)
begin
set @result = 0.00
end
else
begin
set @result =CAST(@valueToCompute
*100 /@TotalValue
AS numeric(10,2))
end
return @result
END
-----------------------------------------------------------------------------------------------------------------------------------------------------------
--Function to convert comma separated
list to integer table values to select from it
-- call example SELECT * FROM
fnNTextToIntTable ('150,220,560,950')
Create FUNCTION [dbo].[fnNTextToIntTable] (@Data NTEXT)
RETURNS
@IntTable
TABLE ([Value] INT NULL)
AS
BEGIN
DECLARE @Ptr int, @Length int, @v nchar, @vv nvarchar(10)
SELECT @Length = (DATALENGTH(@Data) / 2) + 1, @Ptr = 1
WHILE (@Ptr < @Length)
BEGIN
SET @v = SUBSTRING(@Data, @Ptr, 1)
IF @v = ','
BEGIN
INSERT INTO @IntTable (Value) VALUES (CAST(@vv AS int))
SET @vv = NULL
END
ELSE
BEGIN
SET @vv = ISNULL(@vv, '') + @v
END
SET @Ptr = @Ptr + 1
END
-- If the last number was not followed by a comma, add it
to the result set
IF @vv IS NOT NULL
INSERT INTO @IntTable (Value) VALUES (CAST(@vv AS int))
RETURN
END
---------------------------------------------------------------------------------------------------------------------------------------
--
=============================================
-- the function get weed end days count
for period between start date and end date ( friday and saturday are days of
weekend in my case )
/*Call Example
SELECT [dbo].[weekEndDaysCount] (
'2016-01-01'
,'2016-01-30')
GO
*/
-- =============================================
CREATE FUNCTION [dbo].[weekEndDaysCount]
(
-- Add the parameters for the function here
@StartDate
date,
@EndDate
date
)
RETURNS int
AS
BEGIN
DECLARE @WDCount int
;WITH cteSequence (SeqNo) as
(
SELECT 1 UNION ALL SELECT SeqNo + 1
FROM cteSequence
WHERE SeqNo < DATEDIFF(DD, @StartDate,
@EndDate)+1
)
SELECT @WDCount = count(* )
FROM
(SELECT TOP ( datediff(DAY,@StartDate,@EndDate) + 1 )
[Date] = dateadd(DAY,ROW_NUMBER()
OVER(ORDER BY c1.SeqNo),
DATEADD(DD,-1,@StartDate))
FROM
cteSequence c1 ) x
WHERE datepart(dw,[Date]) in(5,6)
OPTION ( MAXRECURSION 0);
RETURN @WDCount
END
GO
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
--
=============================================
-- the function used to get working days
count in period passed to the function start and end dates ( weekend days in my
case is Friday and saturday)
/*
calling example :
SELECT [dbo].[WorkingDaysGetCount] (
'2016-01-01'
,'2016-01-30')
GO
*/
--
=============================================
CREATE FUNCTION [dbo].[WorkingDaysGetCount]
(
-- Add the parameters for the function here
@StartDate
date,
@EndDate
date
)
RETURNS int
AS
BEGIN
DECLARE @WDCount int
SELECT @WDCount=
(DATEDIFF(dd,
@StartDate, @EndDate)
+ 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Saturday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Friday' THEN 1 ELSE 0 END)
RETURN @WDCount
END
---------------------------------------------------------------------------------------------------------------------------------------------------
--
=============================================
-- Author: اØتساب وقت ارسال التنبيهات بالدقائق والساعات والأيام لإظهارها للمستخدم
-- Create date:
-- Description:
--
=============================================
Create FUNCTION [dbo].[ElapsedTimeCalculate]
(
-- Add the parameters for the function here
@ToConvetDateTime
DateTime
)
RETURNS nvarchar(50)
AS
BEGIN
Declare
@ReturnElapsedTime nvarchar(50),
@CurrDate datetime = getdate() ,
@tempHours int,
@tempMinutes int,
@tempDays int
select
@tempDays= DATEDIFF(DD, @ToConvetDateTime,
@CurrDate) ,
@tempHours= DATEDIFF(hh, @ToConvetDateTime,
@CurrDate) ,
@tempMinutes= DATEDIFF(mi,DATEADD(hh,DATEDIFF(hh,
@ToConvetDateTime, @CurrDate),@ToConvetDateTime),@CurrDate)
if(@tempDays>0)
set @ReturnElapsedTime=
N'منذ '+ convert(nvarchar,@tempDays)+N' يوم سابق '
else
if(@tempHours>1)
set @ReturnElapsedTime=
N'منذ '+ convert(nvarchar,@tempHours)+N' ساعة سابقة '
else
if(@tempMinutes >2)
set @ReturnElapsedTime=
N'منذ '+ convert(nvarchar,@tempMinutes)+N' دقيقة سابقة '
else
begin
set @ReturnElapsedTime=
N'مرسل الآن '
end
return
@ReturnElapsedTime
END
-------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
