UDF to summarize data by the week

There have been a few times I wanted to summarize MS SQL Server records by the week. There is not any built in function in T-SQL that I know that does that. The code here is a simple example of how you could create a user defined function that will encapsulate this logic so you can more readably use it in a SQL query.


/*BEGIN Proof of concept script of a user defined function
This script will clean up after itself

Written by Rich Alger 2009-07-22
*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MyTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DateCreated] [datetime] NOT NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_WeekOf]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_WeekOf]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_WeekOf]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[udf_WeekOf](@InDate DATETIME)
RETURNS DATETIME
AS
/* Return the 12:00 am of Sunday before the date passed in */
BEGIN
DECLARE @DatePartOfInDate DATETIME
SET @DatePartOfInDate = CAST(CONVERT(VARCHAR(10), @InDate, 120) AS DATETIME)

DECLARE @WeekdayOfInDate INT
SET @WeekdayOfInDate = DATEPART(weekday, @InDate)

RETURN DATEADD(DAY, (-1 * (@WeekdayOfInDate-1) ), @DatePartOfInDate)
END
'
END
GO

INSERT INTO MyTable (DateCreated) VALUES ('2008-12-30 08:32:27')
INSERT INTO MyTable (DateCreated) VALUES ('2009-01-02 12:17:16')
INSERT INTO MyTable (DateCreated) VALUES ('2009-01-06 09:02:43')
INSERT INTO MyTable (DateCreated) VALUES ('2009-01-13 16:53:02')
INSERT INTO MyTable (DateCreated) VALUES ('2009-01-14 11:12:34')
INSERT INTO MyTable (DateCreated) VALUES ('2009-01-15 13:38:23')


SELECT
Id
,DateCreated
, dbo.udf_WeekOf(DateCreated) AS WeekOf
FROM MyTable

SELECT
COUNT(*) AS RecordInWeekOf
, dbo.udf_WeekOf(DateCreated) AS WeekOf
FROM MyTable
GROUP BY dbo.udf_WeekOf(DateCreated)

GO

/* This will drop the objects*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_WeekOf]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_WeekOf]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
GO

/*END Proof of concept */

The following script will only add the user defined function


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_WeekOf]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_WeekOf]
GO

CREATE FUNCTION [dbo].[udf_WeekOf](@InDate DATETIME)
RETURNS DATETIME
AS
/* Return the 12:00 am of Sunday before the date passed in */
BEGIN
DECLARE @DatePartOfInDate DATETIME
SET @DatePartOfInDate = CAST(CONVERT(VARCHAR(10), @InDate, 120) AS DATETIME)

DECLARE @WeekdayOfInDate INT
SET @WeekdayOfInDate = DATEPART(weekday, @InDate)

RETURN DATEADD(DAY, (-1 * (@WeekdayOfInDate-1) ), @DatePartOfInDate)
END