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

Enabling Concurrent Remote Desktop Sessions on Windows XP SP3

Very useful from The Boiling Mind:

If you have multiple users on your Windows XP machine, you might have heard it is possible to patch the terminal services service, to support multiple concurrent remote desktop connection (via RDP) to your computer.
...

Telecommuting

Jonathan Weber said

I firmly believe that you should expect employees to show up for work, whenever possible, no matter what kind of company.

The reasons for this have nothing to do with checking that people are actually working. It's about efficient communications, building company culture and camaraderie, and sharing the daily bits of work and personal experiences that create a shared sense of purpose.

For over 9 months, I have been telecommuting 2 days a week. 3 other people in my team have also been telecommuting 2 days a week. One other telecommutes full time.

There are things that have not been communicated, or grown in the last while for us. That being said, I do love the freedom it brings me. It allows for more time with my family. I have less driving and lots of other benefits. I have more loyalty for my employer. Why would I want to leave and not have the benefit of working from home two days? I think my employer has made a good decision by allowing us to work from home.

We have been thinking about what we can do as a group to improve our collaboration. This is much more natural when you are all sitting in the same room. Conversations start. You show each other what you are working on. Expressing frustrations are an easy open into offering assistance. Input into design and best practices are shared.

We are moving towards a more formal software development methodology. A little more structured. Three goals so far are to design in pairs, do side by side code reviews, and have quicker iterations in our cycle. I hope that by following some structured guidelines, we will foster collaboration that happened easier when we all sat next to each other 5 days a week.

The Development Abstraction Layer

I just heard my manager talk about some "business stuff" I usually don't hear about. It reminded me of this story in a Joel Spolsky article.

Programmers need a Subversion repository. Getting a Subversion repository means you need a network, and a server, which has to be bought, installed, backed up, and provisioned with uninterruptible power, and that server generates a lot of heat, which means it need to be in a room with an extra air conditioner, and that air conditioner needs access to the outside of the building, which means installing an 80 pound fan unit on the wall outside the building, which makes the building owners nervous, so they need to bring their engineer around, to negotiate where the air conditioner unit will go (decision: on the outside wall, up here on the 18th floor, at the most inconvenient place possible), and the building gets their lawyers involved, because we're going to have to sign away our firstborn to be allowed to do this, and then the air conditioning installer guys show up with rigging gear that wouldn't be out of place in a Barbie play-set, which makes our construction foreman nervous, and he doesn't allow them to climb out of the 18th floor window in a Mattel harness made out of 1/2" pink plastic, I [swear] it could be Disco Barbie's belt, and somebody has to call the building agent again and see [why] they suddenly realized, 12 weeks into a construction project, that another contract amendment is going to be needed for [this] air conditioner that they knew about before Christmas and they only just figured it out, and if your programmers even spend one minute thinking about this that's one minute too many.

To the software developers on your team, this all needs to be abstracted away as typing svn commit on the command line.

That's why you have management.

I am thankful for the development abstraction layer I have at work.

Folder Filters in Beyond Compare

We use subversion and ReSharper at work. Using wildcards on folder names in Beyond Compare lets me exclude the svn and ReSharper folders when comparing two working directories.

This is how my filter shows up in the text box: "-*_svn\;-*_ReSharper*\"

Pidgin: Cannot connect to Yahoo - "connection refused" error

Recently my Pidgin, version 2.5.5, stopped connecting to the yahoo im service with a "connection refused message" I found that this was a recent issue with a work around.

I created a batch file to do this work-around if this happens again.

REM flush dns so pidgin will work with yahoo
REM see http://developer.pidgin.im/ticket/8853#comment:5
ping scs.msg.yahoo.com
ipconfig /flushdns
ping scs.msg.yahoo.com
pause

UPDATE 2009-06-21

There are further issues with this that showed up last week, or maybe just more yahoo servers are upgraded. Pidgin 2.5.7 works for me so far and I flushed my dns a couple of times.

See http://developer.pidgin.im/ticket/8853#comment:77

SQL Server Reporting Services 2008 Custom Assembly Error

Error while loading code module: ‘RS_funcs, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null’. Details: Could not load file or assembly 'RS_funcs, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.

Last fall I was getting this error when trying to get our reporting projects created in VS 2005 to work in VS 2008. I found this post showing where to put your custom assembly in order for the VS designed to work. I post it here so as not to forget again. In VS 2008, the path is "C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies"