pushbomb

All,

I’ve been away on vacation for a few months but I’m back, well rested and writing again.

I recently had to solve an aggregate problem for transactions, displaying the transactions by a week’s range. Considering a number of alternatives, I finally decided to see if a SQL Server Transact SQL function could do the job. As to my knowledge there are no built in functions in SQL Server which will produce groupings with week display as such: JAN 8 06 – JAN 14 06. For example:

Week Range Transaction Count
————————————————–
Jan 01 06 – Jan 07 06 1453
Jan 08 06 – Jan 14 06 2345
Jan 14 06 – Jan 21 06 1235
etc…..
May 01 06 – May 07 06 8423
May 08 06 – May 14 06 6423

I realized this could be derived as I can easily get the week number in the year using the datepart function.

So if for example, if we start Jan 1 2006 we can say:

Week 1 = 1/1/2006-1/7/2006
Week 2 = 1/8/2006-1/14/2006

So:

Start Day in the Year for Week = (Week Number * 7) -6
End Day in the Year for Week = Start Day in the Year for Week + 6

Using this we can get the current week, then calculate the difference in days between the current day of the year and the current week’s start day in the year, then add that (times -1) to the current date, then add six to that date and we have our week range. Perhaps code will show this better:

CREATE FUNCTION dbo.GetWeekDisplay (@inDateTime smalldatetime)
RETURNS varchar(50) AS

BEGIN

declare @weeknumber int
declare @weekstartday int
declare @currentday int
declare @weekdatediff int
declare @enddate smalldatetime
declare @startdate smalldatetime

/* Calculate the Week Number of the Current Date */
set @weeknumber = datepart(week, @inDateTime)

set @weekstartday = (@weeknumber * 7) -6

set @currentday = datepart(dayofyear, @inDateTime)

set @weekdatediff = @currentday – @weekstartday

set @startdate = dateadd(day, @weekdatediff * -1, @inDateTime)

set @enddate = dateadd(day, 6, @startdate)

return convert(varchar, @startdate, 102) + ‘ – ‘ + convert(varchar, @enddate, 102)

END

So when invoking this, I use the following code segement in constructing dynamic SQL in C#:

switch (cmbDateShowBy.SelectedValue)

{

case “Day”:

strAggregateClause = “CONVERT (varchar, ” + strDateField + “, 102)”;
break;

case “Week”:

strAggregateClause = “dbo.GetWeekDisplay(” + strDateField + “)”;
break;

case “Month”:

strAggregateClause = “CONVERT(char(7), cast(cast(datepart(month, ” + strDateField + “) as varchar) + ‘/1/’ + cast(datepart(year, ” + strDateField + “) as varchar) as smalldatetime), 102)”;
break;

case “Year”:

strAggregateClause = “datepart(year, ” + strDateField + “)”;
break;

default:

throw new Exception(“Invalid Date Specified for Date Aggregate”);

}

strSelect += strAggregateClause;
strGroupBy += strAggregateClause;
strOrderBy += strAggregateClause + ” DESC”;

strSelect += ” as DateRangeViewed “;
strFrom = “from ” + strTableName;

I am sure others have created similar solutions, some perhaps better. I considered creating a table which would serve as a lookup table with stored values for week number and year, allowing for a lookup inside the function to retrieve the correct lablel. I considered creating this via a program which would simply increment through time, adding rows as needed. Perhaps this would perform faster, but this function does not require maintenance. Of course I could create the lookup table going out a number of years, but then the question is which would be faster? A lookup against a table or this in-memory function? I would appreciate any and all feedback on this question and suggestions/comments on this solution.

Kind Regards,

Damon Carr