Sql Quries

ROW_NUMBER()
Select ROW_NUMBER()OVER( order by loginDate desc) As SrNo, UserLoginName, Firstname, Lastname, Email,Regdate, dbo.fnYesNo(isActive)UserActive, dbo.fnyesno(isDelete) UserDeleted,Count(isActive) as NoofLogins, Min(loginDate) FirstLoginTime, Max(loginDate)LastLoginTime from View_userLogging where customerId=1 group by UserLoginName, Firstname, Lastname, email, Regdate, isActive,CustomerID, isDelete,loginDate
Function Creation
CREATE FUNCTION [dbo].[fnAccessType] (@PassChar varchar(1))
RETURNS VARCHAR(30) as
BEGIN
Declare @Result varchar(30)
set @Result= Case @PassChar
When '0' then 'Public'
When '1' then 'Private'
When '2' then 'Protected'
End
RETURN (@Result)
END
Function Creation
Alter FUNCTION GetUserName (@UserLoginName varchar(100))
RETURNS VARCHAR(300) asBEGIN
Declare @Result varchar(300)
set @Result=(select FirstName +' '+ LastName+ ' ('+ UserLoginName+')' from UserLogin where UserLoginName=@UserLoginName)
RETURN (@Result)
End

Creating Function

ALTER FUNCTION [dbo].[fnUsage] (@UsgSec int)

RETURNS VARCHAR(10)

as

BEGIN
Declare @Result varchar(10),@min decimal,@mins int,@hrs int,@tmp varchar(10)
If (IsNumeric(@UsgSec)=1) Begin set @min=floor(@UsgSec/60)
If (@min>0 )

begin

set @hrs=@UsgSec/3600

set @mins=@min - floor(@min/60)*60
set @tmp=CAST(@hrs AS varchar(10)) + ':' +right(100+CAST(@mins as varchar(10)),2)
end

else

begin

set @tmp='0:00' end
set @Result = @tmp + ':' + right( 100+ Cast(convert(int,(@UsgSec % 60))as varchar (10)),2) EndElse Begin set @Result='0:00:00'

End
RETURN (@Result)
END