SQL List of Numbers
Sometimes you want a sequence of numbers to join against, say a list of days in the month 1..31 and you want to have a row for each regardless of whether you have a matching row for that day, giving you a pivot table by day of the month. Here is a SQL Server 2000 function to do this:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION dbo.fn_getNumList (
/*
WRITTEN BY Adam Howitt adamhowitt@gmail.com
FUNCTION getDays
WRITTEN FOR SQL Server 2000
DATE 04.21.2006
Usage:
select currentNum
from fn_getNumList(0,60)
*/
@st_num int,
@num_to_add int )
RETURNS @numList TABLE ( currentNum int not null)
AS
BEGIN
WHILE (@num_to_add > 0)
BEGIN
SET @st_num = 1+@st_num
INSERT INTO @numList (currentNum)
VALUES (@st_num)
SET @num_to_add = @num_to_add - 1
END
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION dbo.fn_getNumList (
/*
WRITTEN BY Adam Howitt adamhowitt@gmail.com
FUNCTION getDays
WRITTEN FOR SQL Server 2000
DATE 04.21.2006
Usage:
select currentNum
from fn_getNumList(0,60)
*/
@st_num int,
@num_to_add int )
RETURNS @numList TABLE ( currentNum int not null)
AS
BEGIN
WHILE (@num_to_add > 0)
BEGIN
SET @st_num = 1+@st_num
INSERT INTO @numList (currentNum)
VALUES (@st_num)
SET @num_to_add = @num_to_add - 1
END
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
So for example, you can use the following to retrieve a count of orders for each day of the month, including those where there were no orders.
select t1.currentNum, ISNULL(t2.numOrders,0) as numberOfOrders
from fn_getNumList(0,60) t1
LEFT JOIN
(SELECT datepart(month,create_dt) as dom, count(*) as numOrders
FROM order_tb
GROUP BY datepart(month,create_dt)) t2
ON t1.currentNum = t2.dom