SQL de Ay araliklari ile ugrasmak, relative month references

Raporlama demek tarih araligi demektir, zaman zaman bu tarih araliklarini dinamik bir sekilde vermek, verilen tarih referansi ile baska bir tarihe ulasmak, gerekebilir.

Subat 2009'dan yola cikip Gecen Yil Onceki Ay mantigi ile Ocak 2008'e ulasmak isteyebiliriz.

Iste bunun icin, Current Year This Month, Current Month Last Year, Previous Month Last Year gibi ise yarar birkac fonksiyon.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_GetCurrentMonthLastYear]
(@RefDate smalldatetime )
RETURNS smalldatetime
AS

/******************************************

Name :
fn_GetCurrentMonthLastYear

Description:
Returns the first day of the given month
of previous year of given year.

Use getdate() while calling this function
to get the current date references.

Used by:
Mostly used in dashboards' source queries.

Version Log
1. Created By EA on 29.09.2006
*******************************************/

BEGIN

declare @basedate smalldatetime
set @basedate = '20000101'





RETURN(dateadd(month,datediff(month,@basedate,@RefDate)-12,@basedate) )
END
GO
/****** Object: UserDefinedFunction [dbo].[fn_GetCurrentMonthThisYear] Script Date: 02/05/2009 09:20:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_GetCurrentMonthThisYear]
(@RefDate smalldatetime )
RETURNS smalldatetime
AS


/******************************************

Name :
fn_GetCurrentMonthThisYear

Description:
Returns the first day of the given month
given year.

Use getdate() while calling this function
to get the current date references.

Used by:
Mostly used in dashboards' source queries.

Version Log
1. Created By EA on 29.09.2006
*******************************************/

BEGIN

declare @basedate smalldatetime
set @basedate = '20000101'


RETURN(dateadd(month,datediff(month,@basedate,@RefDate),@basedate) )
END
GO
/****** Object: UserDefinedFunction [dbo].[fn_GetFirstMonthLastYear] Script Date: 02/05/2009 09:20:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_GetFirstMonthLastYear]
(@RefDate smalldatetime )
RETURNS smalldatetime
AS

/******************************************

Name :
fn_GetFirstMonthLastYear

Description:
Returns the January 1st of previous year of
given year.

Use getdate() while calling this function
to get the current date references.

Used by:
Mostly used in dashboards' source queries.

Version Log
1. Created By EA on 29.09.2006
*******************************************/


BEGIN

declare @basedate smalldatetime
set @basedate = '20000101'






RETURN(dateadd(year,datediff(year,@basedate,@RefDate)-1,@basedate) )
END
GO
/****** Object: UserDefinedFunction [dbo].[fn_GetFirstMonthThisYear] Script Date: 02/05/2009 09:20:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_GetFirstMonthThisYear]
(@RefDate smalldatetime )
RETURNS smalldatetime
AS


/******************************************

Name :
fn_GetFirstMonthLastYear

Description:
Returns the January 1st of
given year.

Use getdate() while calling this function
to get the current date references.

Used by:
Mostly used in dashboards' source queries.

Version Log
1. Created By EA on 29.09.2006
*******************************************/


BEGIN

declare @basedate smalldatetime
set @basedate = '20000101'






RETURN(dateadd(year,datediff(year,@basedate,@RefDate),@basedate) )
END
GO
/****** Object: UserDefinedFunction [dbo].[fn_GetPrevMonthLastYear] Script Date: 02/05/2009 09:20:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_GetPrevMonthLastYear]
(@RefDate smalldatetime )
RETURNS smalldatetime
AS
/******************************************

Name :
fn_GetPrevMonthLastYear

Description:
Returns the first day of Previous Month of the given month
and previous year of the given year.

Use getdate() while calling this function
to get the current date references.

Used by:
Mostly used in dashboards' source queries.

Version Log
1. Created By EA on 29.09.2006
*******************************************/

BEGIN

declare @basedate smalldatetime
set @basedate = '20000101'




RETURN(dateadd(month,datediff(month,@basedate,@RefDate)-13,@basedate) )
END
GO
/****** Object: UserDefinedFunction [dbo].[fn_GetPrevMonthThisYear] Script Date: 02/05/2009 09:20:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[fn_GetPrevMonthThisYear]
(@RefDate smalldatetime )
RETURNS smalldatetime
AS
/******************************************

Name :
fn_GetPrevMonthThisYear

Description:
Returns the first day of Previous Month
of the given year.

Use getdate() while calling this function
to get the current date references.

Used by:
Mostly used in dashboards' source queries.

Version Log
1. Created By EA on 29.09.2006
*******************************************/

BEGIN

declare @basedate smalldatetime
set @basedate = '20000101'


RETURN(dateadd(month,datediff(month,@basedate,@RefDate)-1,@basedate) )
END
GO
/****** Object: UserDefinedFunction [dbo].[fn_GetMonthName] Script Date: 02/05/2009 09:20:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- select dbo.fn_GetMonthName ('20071201',3)





CREATE FUNCTION [dbo].[fn_GetMonthName]
(@RefDate smalldatetime,
@ShortenTo int )
RETURNS varchar (20)
AS

/******************************************

Name :
fn_GetMonthName

Description:
Returns the name of the month of given date.
A short version can also be returned.

Use getdate() while calling this function
to get the current date references.

Used by:
Mostly used in dashboards' source queries.

Version Log
1. Created By EA on 02.10.2007
*******************************************/

BEGIN

IF @ShortenTo <=0 Set @ShortenTo = 20


RETURN Left(DATENAME(month, @RefDate),@ShortenTo)
END
GO