Feeds:
Posts
Comments

Posts Tagged ‘function’

-- Please note that this is not my code and I have found it somewhere on the net

CREATE FUNCTION [dbo].[udf_Split] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(data NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1
       
        INSERT INTO @output (data)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)
        
    END 
    RETURN 
END

Read Full Post »


/*
    There are many uses of date tables and it could becomes essential at other times.
    The below script creates a function that produces a daily date table with the passed start and end dates.

    There are also examples at ehe ver bottom
*/

create function [dbo].[udf_DailyDateTable]
      (@StartDate datetime, @EndDate datetime)         
returns @DailyDates table 
(
    DailyDatesID int identity,
    DailyDate date, 
    DayNumber int,
    DayName varchar(50),
    WeekNumber int,
    QuaterNumber int,
    MonthNumber int,
    MonthCalled varchar(50),
    YearNumber int
)
AS 
BEGIN
 --   ------------ Test -------------

	---- select * from [dbo].[udf_DailyDateTable] ('1 jan 2016', '5 jan 2016')

 --   declare @DailyDates table
 --   (
 --       DailyDatesID int identity,
	--   DailyDate date, 
	--   DayNumber int,
 --       DayName varchar(50),
 --       WeekNumber int,
	--   QuaterNumber int,
	--   MonthNumber int,
	--   MonthCalled varchar(50),
 --       YearNumber int
 --   )
     
 --   declare @StartDate date, @EndDate date
 --   set @StartDate = '15 sep 2010' -- GETDATE()
 --   set @EndDate = '19 nov 2011'
	
 --  ------------------------ 
 
    while (@StartDate <= @EndDate)
    begin
        insert into @DailyDates
        select	@StartDate DailyDate
			 , datename(day, @StartDate) DayName 
				, datename(dw, @StartDate) DayName 
                , datepart(week, @StartDate) WeekNumber
			 , DATEPART(qq , @StartDate) as QuarterValue 
                , datepart(month,@StartDate) MonthNumber
				, datename(month,@StartDate) MonthCalled
				, YEAR(@StartDate) YearNumber
         
        set @StartDate = dateadd(day, 1 ,@StartDate)
        --print @StartDate -- test ` 
    end
     
    --select * from @DailyDates -- test ` 
    return 
END


------------------------ Examples below ------------------------------ 

DECLARE @DateToProcess date = '28 jul 2016' -- getdate() -- this is generally todays date 

-- it is important to initialse @EndOfLastMonth first because that in turn is used in @YTDDateFrom. 
-- if this is run in january then the last year's dates will be used  
DECLARE @EndOfLastMonth date = DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0,@DateToProcess) , 0)) -- End of previous month. All results should be upto this date not yesterday
DECLARE @YTDDateFrom date = cast('1 jan ' + str(year(@EndOfLastMonth)) as date) -- YTD stars from 1 Jan. Just add the requested year


-- MTD
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.MonthNumber = month(@EndOfLastMonth)
						  

-- QTD
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.QuaterNumber = datepart(q,@EndOfLastMonth) 

-- YTD
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.YearNumber = datepart(yy,@DateToProcess) 

-- Yesterday 
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.DailyDate = dateadd(day, -1, @DateToProcess)

-- Month Of Yesterday
select * 
from [dbo].[udf_DailyDateTable](@YTDDateFrom, @DateToProcess) ddt
WHERE ddt.MonthNumber = month(dateadd(day, -1, @DateToProcess))


---- e.g. ------
/*
    ---------- Filter for dates ----------------
    and (CASE 
		  WHEN ddt.DailyDate = dateadd(day, -1, @DateToProcess) THEN 'Yes'  --- this and the next ones need to be before the otheres 
		  WHEN ddt.MonthNumber = month(dateadd(day, -1, @DateToProcess)) THEN 'MoY' -- this may be becasue thiss is a derived table and @DateToProcess > @EndOfLastMonth
		  WHEN ddt.MonthNumber = month(@EndOfLastMonth) THEN 'MTD' 
		  WHEN ddt.QuaterNumber = datepart(q,@EndOfLastMonth) THEN 'QTD' 
		  WHEN ddt.YearNumber = datepart(yy,@DateToProcess) THEN 'YTD'   		  
    END ) = @XTD 
    --------------------------------------------
*/
------------ 


Read Full Post »

ALTER function [csrt].[udf_FormatNumber] 
	(@PassedValue float, @CurrencySymbol varchar(5), @RoundUpTo int = 0, @Prefix bit = 'true', @TruncateDecimals bit = 'false')
returns varchar(max)
as 
begin

--	----------- Test ----------------------------
--	declare @PassedValue float, @CurrencySymbol varchar(5), @RoundUpTo int, @Prefix bit, @TruncateDecimals bit  
--	select @PassedValue = '1234.12341', @CurrencySymbol = '£' , @RoundUpTo = 2, @Prefix = 'true', @TruncateDecimals = 'true' 
--	-------------------------------------------

	declare @FormattedNumber varchar(max)
	
	-- Format the number to currency
	--select @FormattedNumber = convert (varchar,round(cast(isnull(@PassedValue ,0)as money),@RoundUpTo), 1) 
	select @FormattedNumber = convert (varchar,round(cast(@PassedValue as money),@RoundUpTo), 1) -- UMA (9 Jul 2012) 
	
	-- Get rid of the decimals if requested
	if @TruncateDecimals = 'true' 
		set @FormattedNumber = substring(@FormattedNumber, 1, len(@FormattedNumber) - 3)
	
	-- Place the currency sign 
	if @Prefix = 1
		set @FormattedNumber =  @CurrencySymbol + @FormattedNumber  
	else 
		set @FormattedNumber = @FormattedNumber + @CurrencySymbol
	
--	select @FormattedNumber -- test 

	return @FormattedNumber
	
end


/*----------- Test ----------------------------

Select csrt.udf_FormatNumber 
	('1234.12341', '%', 2, 'false', 'false')

-------------------------------------------*/


Read Full Post »

-- Returns the Week Day Names and its correponding Week Number (taking Sunday as 1)
--==================================================
--alter proc dbo.usp_getWeekDayNames
--as
SET DATEFIRST 1 -- Monday is set to be the first day of the week

DECLARE @c INT

SET @c = 1

DECLARE @WeekDays TABLE (
	WeekDayNumber INT
	,WeekDayName VARCHAR(12)
	)

WHILE (@c & lt;= 7)
BEGIN
	INSERT @WeekDays
	SELECT datepart(weekday, dateadd(day, @c, getdate())) WeekDayNumber
		,datename(weekday, dateadd(day, @c, getdate())) WeekDayName

	SET @c = @c + 1
END

SELECT *
FROM @WeekDays
ORDER BY 1

Read Full Post »

--Description:    Returns the Start Time and Stop Time columns from udf_EvenlySlice24hours in minutes (this udf can also be found in my posts)
--alter proc dbo.usp_getTimeSlicedInMin (@Interval int)
--as
---- ==== Test Data =====
DECLARE @Interval INT

SET @Interval = 60

---- ========================
SELECT left(StartTime, len(StartTime) - 3) StartTime
	,left(StopTime, len(StopTime) - 3) StopTime
	,LEFT(StartTime, 2) * 60 + RIGHT(LEFT(StartTime, 5), 2) StartTimeInMin
	,LEFT(StopTime, 2) * 60 + RIGHT(LEFT(StopTime, 5), 2) + 1 StopTimeInMin -- one has to be added is added (see notes below)
FROM udf_EvenlySlice24hours(@Interval)
	/*  ====== Notes

1 is added to StopTimeInMin so that in the calling SP  the parameter  passed by the user need can include
the last minute till which he wants to end time this is done for user friedliness, so for example  if the user wants to end the timer at 5pm then
he shoud put in 1020 = (17 * 60) later on in the code 3 mc should deducted like below
@StopTime = DATEADD(ms,-3, dateadd(minute, [last minute till which he wants to end time], @StopTime)) -- Subtract -3 ms to get the last time

*/

Read Full Post »

--WARNING! ERRORS ENCOUNTERED DURING SQL PARSING! -- There are two SPs here
--First option:    Returns all month name and number
--==================================================
--create proc dbo.usp_getMonthNameAndNumber
--as
DECLARE @allMonths TABLE (
	MonthNumber INT identity
	,MonthName VARCHAR(10)
	)
DECLARE @count INT

SET @count = 0

WHILE (@count & lt;12)
BEGIN
	INSERT @allMonths
	SELECT dateName(month, dateadd(month, @count, '1 Jan 2010'))

	SET @count = @count + 1
END

SELECT *
FROM @allMonths
ORDER BY 1

--Second option:    Returns Month name and number upto the passed month number
--    (rows are restricted to the passed month)
--==================================================
--create proc dbo.usp_getMonth (@RestrictToMonthNumber int = null)
--as
---------====== Test Values ======
DECLARE @RestrictToMonthNumber INT

SET @RestrictToMonthNumber = 6

-------=============================
-- Note: If null is sent then all months are returned
DECLARE @allMonths TABLE (
	MonthNumber INT identity
	,MonthName VARCHAR(10)
	)

IF @RestrictToMonthNumber IS NULL
	SET @RestrictToMonthNumber = 12

DECLARE @count INT

SET @count = 0

WHILE (@count & lt;@RestrictToMonthNumber)
BEGIN
	INSERT @allMonths
	SELECT dateName(month, dateadd(month, @count, '1 Jan 2010'))

	SET @count = @count + 1
END

SELECT *
FROM @allMonths
ORDER BY 1 

Related post ( used IN SSRS ) :
http: / / wp.me / pAchH – 4 A

Read Full Post »

--create function dbo.udf_ExtractNumbersToFloat (@String varchar(max))
--returns float
--begin
------ ==== Test =======
DECLARE @String VARCHAR(1000)

SET @String = 'I can count 1 and 2 and 3 even 4.5';

------ ==== Test =======
WHILE PATINDEX('%[^0-9]%.%', @String) & gt;0
	SET @String = REPLACE(@String, SUBSTRING(@String, PATINDEX('%[^0-9]%', @String), 1), '')

DECLARE @Float FLOAT

SET @Float = convert(FLOAT, @String)

PRINT @Float -- test
	--    return  @Float
	--end

Read Full Post »

Older Posts »