USE [Nobolo]
GO
/****** Object: UserDefinedFunction [dbo].[CalculateDistance] Script Date: 7/8/2020 1:23:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[CalculateDistance]
(@Longitude1 Decimal(18,6),
@Latitude1 Decimal(18,6),
@Longitude2 Decimal(18,6),
@Latitude2 Decimal(18,6))
Returns Float
As
Begin
Declare @ret float
set @ret = 0.00
if(@Longitude1 = 0 or @Latitude1 = 0 or @Longitude2 = 0 or @Latitude2 = 0)
begin
set @ret = -1
end
else
begin
Declare @Temp Float
Set @Temp = sin(@Latitude1/57.2957795130823) * sin(@Latitude2/57.2957795130823) + cos(@Latitude1/57.2957795130823) * cos(@Latitude2/57.2957795130823) * cos(@Longitude2/57.2957795130823 - @Longitude1/57.2957795130823)
if @Temp > 1
Set @Temp = 1
Else If @Temp < -1
Set @Temp = -1
set @ret = 3958.75586574 * acos(@Temp)
end
--set @ret = CEILING(@ret)
Return @ret
End
*************************************************************
**************** Calculate Distance Geography **********************
USE [Nobolo]
GO
/****** Object: UserDefinedFunction [dbo].[CalculateDistanceByGeography] Script Date: 7/8/2020 1:23:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[CalculateDistanceByGeography]
(@Longitude1 Decimal(18,6),
@Latitude1 Decimal(18,6),
@Longitude2 Decimal(18,6),
@Latitude2 Decimal(18,6))
Returns Float
As
Begin
Declare @ret Float
Declare @source geography = geography::Point(@Latitude2, @Longitude2, 4326)
Declare @destination geography= geography::Point(@Latitude1, @Longitude1, 4326)
set @ret = (@source.STDistance(@destination) * 3.28084);
--set @ret = CEILING(@ret)
Return @ret
End
*************************************************************
************** Convert Date *****************
USE [Nobolo]
GO
/****** Object: UserDefinedFunction [dbo].[FConvertDate] Script Date: 7/8/2020 1:24:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[FConvertDate](
@Datime datetime
)
Returns Varchar(400)
As
Begin
Return CONVERT(VARCHAR(10), @Datime, 101)
End
************************************************
********** Get Last Active Date time(**********************
USE [Nobolo]
GO
/****** Object: UserDefinedFunction [dbo].[FnGetLastActiveDateTime] Script Date: 7/8/2020 1:24:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[FnGetLastActiveDateTime]
(
-- Add the parameters for the function here
@lastActiveDate Datetime,
@UserLanguage as VARCHAR(50) = 'English'
)
RETURNS varchar(30)
AS
BEGIN
DECLARE @LastActivity varchar(100)
SET @LastActivity = '';
-- Add the T-SQL statements to compute the return value here
DECLARE @today datetime, @nowLastActiveDate datetime
DECLARE @years int, @months int, @days int, @hours int, @minutes int, @seconds int, @h int, @m int, @s int
SELECT @today = GETUTCDATE()
SELECT @nowLastActiveDate = DATEADD(year, DATEDIFF(year, @lastActiveDate, @today), @lastActiveDate)
SELECT @years = DATEDIFF(year, @lastActiveDate, @today) - (CASE WHEN @nowLastActiveDate > @today THEN 1 ELSE 0 END)
SELECT @months = MONTH(@today - @nowLastActiveDate) - 1
SELECT @days = DAY(@today - @nowLastActiveDate) - 1
SELECT @h = DATEDIFF(HOUR, @lastActiveDate, @today)
SELECT @m = DATEDIFF(MINUTE, @lastActiveDate, @today)
SELECT @s = DATEDIFF(SECOND, @lastActiveDate, @today)
SET @hours = (@h%24)
SET @minutes = (@m%60)
SET @seconds = (@s%60)
--SET @LastActivity =
-- (CASE
-- WHEN @years = 1 THEN ' year ago (' + convert(varchar, @lastActiveDate, 105) + ')'
-- WHEN @years > 1 THEN convert(varchar(3),@years) + ' years ago (' + convert(varchar, @lastActiveDate, 105) + ')'
-- WHEN @months > 1 THEN ' month ago (' + convert(varchar, @lastActiveDate, 105) + ')'
-- WHEN @months = 1 THEN convert(varchar(3),@months) + ' months ago (' + convert(varchar, @lastActiveDate, 105) + ')'
-- WHEN @days = 1 THEN ' day ago (' + convert(varchar, @lastActiveDate, 105) + ')'
-- WHEN @days > 1 THEN convert(varchar(3),@days) + ' days ago (' + convert(varchar, @lastActiveDate, 105) + ')'
-- WHEN @hours = 1 THEN ' hour ago'
-- WHEN @hours > 1 THEN convert(varchar(3),@hours) + ' hours ago'
-- WHEN @minutes = 1 THEN ' minute ago'
-- WHEN @minutes > 1 THEN convert(varchar(3),@minutes) + ' minutes ago'
-- WHEN @seconds = 1 THEN ' second ago'
-- WHEN @seconds > 1 THEN convert(varchar(3),@seconds) + ' seconds ago'
-- ELSE convert(varchar, @lastActiveDate, 105)
-- END)
IF (@UserLanguage = 'English')
BEGIN
SET @LastActivity =
(CASE
WHEN @years = 1 THEN ' year ago '
WHEN @years > 1 THEN convert(varchar(3),@years) + ' years ago '
WHEN @months = 1 THEN ' month ago '
WHEN @months > 1 THEN convert(varchar(3),@months) + ' months ago '
WHEN @days = 1 THEN ' day ago '
WHEN @days > 1 THEN convert(varchar(3),@days) + ' days ago '
WHEN @hours = 1 THEN ' hour ago '
WHEN @hours > 1 THEN convert(varchar(3),@hours) + ' hours ago '
WHEN @minutes = 1 THEN ' minute ago '
WHEN @minutes > 1 THEN convert(varchar(3),@minutes) + ' minutes ago '
WHEN @seconds = 1 THEN ' Just now '
WHEN @seconds > 1 THEN ' Just now '
ELSE convert(varchar, @lastActiveDate, 105)
END)
END
ELSE IF (@UserLanguage = 'Creole')
BEGIN
SET @LastActivity =
(CASE
WHEN @years = 1 THEN 'Il y a 1 an' --' year ago '
WHEN @years > 1 THEN 'Il y a '+convert(varchar(3),@years)+' années' --' years ago '
WHEN @months = 1 THEN 'Sa gen 1 mwa' --' month ago '
WHEN @months > 1 THEN 'Sa gen '+ convert(varchar(3),@months)+' mwa' -- ' months ago '
WHEN @days = 1 THEN 'Sa gen 1 jou' --' day ago '
WHEN @days > 1 THEN 'Sa gen '+convert(varchar(3),@days)+' jou' -- ' days ago '
WHEN @hours = 1 THEN 'Il y a 1 heure' --' hour ago '
WHEN @hours > 1 THEN 'Il y a '+convert(varchar(3),@hours)+' heures' --' hours ago '
WHEN @minutes = 1 THEN 'Sa gen 1 minit' --' minute ago '
WHEN @minutes > 1 THEN 'Sa gen '+convert(varchar(3),@minutes)+' minit' --' minutes ago '
WHEN @seconds = 1 THEN 'Kounye a'--' Just now '
WHEN @seconds > 1 THEN 'Kounye a'--' Just now '
ELSE convert(varchar, @lastActiveDate, 105)
END)
END
ELSE IF (@UserLanguage = 'French')
BEGIN
SET @LastActivity =
(CASE
WHEN @years = 1 THEN 'Sa gen 1 an' --' year ago '
WHEN @years > 1 THEN 'Sa gen '+convert(varchar(3),@years)+' ane'-- ' years ago '
WHEN @months = 1 THEN 'Il y a 1 mois' --' month ago '
WHEN @months > 1 THEN 'Il y a '+convert(varchar(3),@months)+' mois' --' months ago '
WHEN @days = 1 THEN 'Il y a 1 jour' --' day ago '
WHEN @days > 1 THEN 'Il y a '+convert(varchar(3),@days)+' jours'--' days ago '
WHEN @hours = 1 THEN 'Sa gen 1 èdtan' --' hour ago '
WHEN @hours > 1 THEN 'Sa gen '+convert(varchar(3),@hours)+' èdtan'--' hours ago '
WHEN @minutes = 1 THEN 'Il y a 1 minute' --' minute ago '
WHEN @minutes > 1 THEN 'Il y a '+convert(varchar(3),@minutes)+' minutes' -- ' minutes ago '
WHEN @seconds = 1 THEN 'À l''instant' --' Just now '
WHEN @seconds > 1 THEN 'À l''instant' --' Just now '
ELSE convert(varchar, @lastActiveDate, 105)
END)
END
ELSE IF (@UserLanguage = 'Spanish')
BEGIN
SET @LastActivity =
(CASE
WHEN @years = 1 THEN 'hace 1 ano' --' year ago '
WHEN @years > 1 THEN 'hace '+convert(varchar(3),@years)+' anos' --' years ago '
WHEN @months = 1 THEN 'hace 1 mes' --' month ago '
WHEN @months > 1 THEN 'hace '+convert(varchar(3),@months)+' meses'-- ' months ago '
WHEN @days = 1 THEN 'hace 1 dia'--' day ago '
WHEN @days > 1 THEN 'hace '+convert(varchar(3),@days)+' dias' --' days ago '
WHEN @hours = 1 THEN 'hace 1 hora' --' hour ago '
WHEN @hours > 1 THEN 'hace '+convert(varchar(3),@hours)+' horas' -- ' hours ago '
WHEN @minutes = 1 THEN 'hace 1 minuto' --' minute ago '
WHEN @minutes > 1 THEN 'hace '+convert(varchar(3),@minutes)+' minutos' --' minutes ago '
WHEN @seconds = 1 THEN 'Justo ahora' --' Just now '
WHEN @seconds > 1 THEN 'Justo ahora' --' Just now '
ELSE convert(varchar, @lastActiveDate, 105)
END)
END
RETURN @LastActivity;
END
****************************************************
***************** Get EST from UTC *********************
USE [Nobolo]
GO
/****** Object: UserDefinedFunction [dbo].[GetESTfromUTC] Script Date: 7/8/2020 1:24:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[GetESTfromUTC]
(
@CurrentUTCDateTime datetimeoffset
)
Returns datetime
As
Begin
Declare @ret datetime
SELECT @ret = CONVERT(datetime,
SWITCHOFFSET(CONVERT(datetimeoffset,
@CurrentUTCDateTime),
DATENAME(TzOffset, SYSDATETIMEOFFSET())))
--set @ret = CEILING(@ret)
Return @ret
End
*********************************************
***************** Get Http ***********************
USE [Nobolo]
GO
/****** Object: UserDefinedFunction [dbo].[GetHttp] Script Date: 7/8/2020 1:26:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[GetHttp] ( @url varchar(8000) )
returns varchar(8000)
as
BEGIN
DECLARE @win int
DECLARE @hr int
DECLARE @text varchar(8000)
EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1'
, @win OUT
IF @hr <> 0
EXEC sp_OAGetErrorInfo @win
EXEC @hr=sp_OAMethod @win
, 'Open'
, NULL
, 'GET'
, @url
, 'false'
IF @hr <> 0
EXEC sp_OAGetErrorInfo @win
EXEC @hr=sp_OAMethod @win
, 'Send'
IF @hr <> 0
EXEC sp_OAGetErrorInfo @win
EXEC @hr=sp_OAGetProperty @win
, 'ResponseText'
, @text OUTPUT
IF @hr <> 0
EXEC sp_OAGetErrorInfo @win
EXEC @hr=sp_OADestroy @win
IF @hr <> 0
EXEC sp_OAGetErrorInfo @win
RETURN @text
END
*************************************************
************* Get ago function *****************
USE [CastarV2]
GO
/****** Object: UserDefinedFunction [dbo].[GetDateFormat] Script Date: 7/8/2020 1:46:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetDateFormat]
(
@FROM_DATE DATETIME ,
@TO_DATE DATETIME
)
RETURNS
VARCHAR(100)
AS
BEGIN
DECLARE @Date
AS
VARCHAR(100)
SELECT @Date = CASE WHEN DATEDIFF(mi, @FROM_DATE, @TO_DATE) <= 1
THEN'1m'
WHEN DATEDIFF(mi, @FROM_DATE, @TO_DATE) > 1 AND DATEDIFF(mi, @FROM_DATE, @TO_DATE) <= 60 THEN
CONVERT(VARCHAR, DATEDIFF(mi, @FROM_DATE, @TO_DATE)) + 'm'WHEN DATEDIFF(hh, @FROM_DATE, @TO_DATE) <= 1 THEN
CONVERT(VARCHAR, DATEDIFF(hh, @FROM_DATE, @TO_DATE)) + 'h'WHEN DATEDIFF(hh, @FROM_DATE, @TO_DATE) > 1 AND DATEDIFF(hh, @FROM_DATE, @TO_DATE) <= 24 THEN
CONVERT(VARCHAR, DATEDIFF(hh, @FROM_DATE, @TO_DATE)) + 'h'WHEN DATEDIFF(dd, @FROM_DATE, @TO_DATE) <= 1 THEN
CONVERT(VARCHAR, DATEDIFF(dd, @FROM_DATE, @TO_DATE)) + 'd'WHEN DATEDIFF(dd, @FROM_DATE, @TO_DATE) > 1 AND DATEDIFF(dd, @FROM_DATE, @TO_DATE) <= 7 THEN
CONVERT(VARCHAR, DATEDIFF(dd, @FROM_DATE, @TO_DATE)) + 'd'WHEN DATEDIFF(ww, @FROM_DATE, @TO_DATE) <= 1 THEN
CONVERT(VARCHAR, DATEDIFF(ww, @FROM_DATE, @TO_DATE)) + 'w'WHEN DATEDIFF(ww, @FROM_DATE, @TO_DATE) > 1 AND DATEDIFF(ww, @FROM_DATE, @TO_DATE) <= 4 THEN
CONVERT(VARCHAR, DATEDIFF(ww, @FROM_DATE, @TO_DATE)) + 'w'WHEN DATEDIFF(mm, @FROM_DATE, @TO_DATE) <= 1 THEN
CONVERT(VARCHAR, DATEDIFF(mm, @FROM_DATE, @TO_DATE)) + 'month'WHEN DATEDIFF(mm, @FROM_DATE, @TO_DATE) > 1 AND DATEDIFF(mm, @FROM_DATE, @TO_DATE) <= 12 THEN
CONVERT(VARCHAR, DATEDIFF(mm, @FROM_DATE, @TO_DATE)) + 'mnths'WHEN DATEDIFF(yy, @FROM_DATE, @TO_DATE) <= 1 THEN
CONVERT(VARCHAR, DATEDIFF(yy, @FROM_DATE, @TO_DATE)) + 'year'WHEN DATEDIFF(yy, @FROM_DATE, @TO_DATE) > 1 THEN
CONVERT(VARCHAR, DATEDIFF(yy, @FROM_DATE, @TO_DATE)) + 'yrs'END
RETURN @Date
END
*******************************************
***************** GEtAge ****************************************
USE [CastarV2]
GO
/****** Object: UserDefinedFunction [dbo].[GetAge] Script Date: 7/8/2020 1:45:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[GetAge]
(
@dob datetime
)
Returns bigint
As
Begin
Declare @ret bigint
set @ret = null
SELECT @ret =CONVERT(int,ROUND(DATEDIFF(hour,@dob,GETDATE())/8766.0,0))
Return @ret
End
****************** END *************************************
No comments:
Post a Comment