min and max function tsql

/*
=============================================
Author: Bas van den Berg
Create date: 2014-02-25
Description: returns the minimum of two numbers
============================================
select dbo.udf_max(1,2)
select dbo.udf_max(null,2)
select dbo.udf_max(2,null)
select dbo.udf_max(2,3)
*/
ALTER FUNCTION [dbo].[udf_max]
(
@a sql_variant,
@b sql_variant

)
RETURNS sql_variant
AS
BEGIN
if @a is null or @b > @a
return @b
else
if @b is null or @a > @b
return @a
return null
END

/*
=============================================
uthor: Bas van den Berg
Create date: 2014-02-25
Description: returns the minimum of two numbers
============================================
select dbo.min(1,2)
select dbo.min(null,2)
select dbo.min(2,null)
select dbo.min(2,3)
*/
ALTER FUNCTION [dbo].[udf_min]
(
@a sql_variant,
@b sql_variant

)
RETURNS sql_variant
AS
BEGIN
if @a is null or @b < @a
return @b
else
if @b is null or @a < @b
return @a
return null
END

 

reading xls from ssis

Problem: Reading XLS or XLSX in SSIS only works fine when the excel file is opened during the time of the extraction.

Solution:Use ACE driver instead of standard Jet.OLEDB driver. For example using an expression like this:

“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=”+  @[User::Filename_complete]+
“;Extended Properties=\”Excel 8.0;HDR=Yes;IMEX=1\”;”

lowest function

— =============================================
— Author:  Bas van den Berg
— Create date: 2013-02-19
— Description: return the lowest of two numbers
— =============================================
ALTER FUNCTION [dbo].[lowest](@a Decimal(10,2) , @b Decimal(10,2) )
RETURNS Decimal(10,2)
AS
BEGIN
declare @result as Decimal(10,2)

if  (@a <= @b and not @a is null) or @b is null
set @result = @a
ELSE
set @result = @b

return @result
END

generate a Calender / Date dimension in TSQL