Handy SQL Functions: Number List

Sometimes, in SQL group queries, you need totals over a series(e.g. Month numbers 1 to 12). What do you do if some parts of that range have no data but you still want the number in the series displayed?

For instance in our example you have sales data for Month 1,2,3,5 & 6 but there isn't anything for month number 4. You still want Month 4 displayed as a row, just with a null value.

The easiest way to do this is have a small table with numbers 1 to 12 that you can LEFT JOIN on to.

This function will output a table just like that.

CREATE FUNCTION dbo.fnNumberList
(
 @iStart int,
 @iEnd int,
 @iStep int = 1
)  
RETURNS @RtnValue table 
(
 ircNum int
) 

/*
 Returns a table with column name "ircNum" numbers from iStart to iEnd incrementing/decrementing by iStep
 Example:
 select * from dbo.NumberList(4,10,2);
 
 ircNum
 ------
 4
 6
 8
 10
*/

AS  
BEGIN 
 Declare @Cnt int
 Set @Cnt = @iStart

 if @iStep>0 
 While (@Cnt<=@iEnd)
 Begin
  Insert Into @RtnValue (ircNum) VALUES (@Cnt)
  Set @Cnt = @Cnt + @iStep
 End

 if @iStep<0 
 While (@Cnt>=@iEnd)
 Begin
  Insert Into @RtnValue (ircNum) VALUES (@Cnt)
  Set @Cnt = @Cnt + @iStep
 End
 
 Return
END

Handy SQL Functions: Reverse Week Number

I thought it was about time I posted some of the random little functions I've created in SQL that I use now and then.

So here's the first. It's very easy in SQL to get a week number (1..53) from a date - just use "datepart". However doing the reverse isn't. Say, for instance you wanted to know what the date was at the start of week 27 in 2012. How would you go about working that out? Here's my solution.

Just supply the function with the week number and year and out pops the date.

CREATE FUNCTION dbo.fnGetDateFromWeekNo
(@weekNo int , @yearNo  int)
RETURNS smalldatetime
AS
BEGIN 

DECLARE @tmpDate smalldatetime

IF @weekNo<1 set @weekNo=1

set @tmpdate= cast(cast (@yearNo as varchar) + '-01-01' as smalldatetime)
-- jump forward x-1 weeks to save counting through the whole year 
set @tmpdate=dateadd(wk,@weekno-1,@tmpdate)

-- make sure weekno is not out of range
if @WeekNo <= datepart(wk,cast(cast (@yearNo as varchar) + '-12-31' as smalldatetime))
BEGIN
 WHILE (datepart(wk,@tmpdate)<@WeekNo)
 BEGIN
  set @tmpdate=dateadd(dd,1,@tmpdate)
 END
END
ELSE
BEGIN
 -- invalid weeknumber given
 set @tmpdate=null
END


RETURN @tmpDate

END