Numbers tables are well documented elsewhere, however they can now be created in a trivial fashion in Denali using a Sequence, with the following script.
SET nocount ON
IF EXISTS (SELECT 1
WHERE table_name = 'Numbers')
DROP TABLE numbers
CREATE TABLE numbers
NUMBER INT NOT NULL,
CONSTRAINT pk_numbers PRIMARY KEY CLUSTERED (NUMBER) WITH FILLFACTOR = 100
IF EXISTS (SELECT 1
WHERE name = 'SeqNumbers'
AND type_desc = 'SEQUENCE_OBJECT')
DROP sequence dbo.seqnumbers
CREATE SEQUENCE SeqNumbers
INSERT Numbers(Number) VALUES(NEXT VALUE FOR SeqNumbers)
This will give you a table of numbers from 0-10000.
Then a simple query will split a table into monthly records - or any other time period - by joining to the numbers table using the difference between the start and end dates of the rows you want to split. An example will probably make it clearer - we'll use the Production.BillOfMaterials in the AdventureWorks2008R2 database.
Firstly lets make the dates a little less uniform:
SET EndDate = '20040405'
SET StartDate='20040731', EndDate = '20040801'
SET StartDate='20041031', EndDate = '20051130'
Querying the Production.BillOfMaterials for an Id and start and end dates and quantity looks something like this:
Taking the difference between the start and end dates and joining to the numbers table gives one row per month that exists between the start and end dates. A little jiggery-pokery with the date functions gives you the start and end of each month over which the bill of materials spanned, like so;
You'll note that I also divided the PerAssemblyQty by the number of months (adding one because the DATEDIFF on two dates in the same month returns 0). Finally lets see the result set
DATEDIFF(m, StartDate, EndDate) NumberOfMonths,
CAST(CONVERT(CHAR(6), DATEADD(m, Number, StartDate), 112)+'01' AS DATETIME) MonthStartDate,
DATEADD(dd, -1, (CAST(CONVERT(CHAR(6), DATEADD(m, Number+1, StartDate), 112)+'01' AS DATETIME))) MonthEndDate,
CAST(PerAssemblyQty/(DATEDIFF(m, StartDate, EndDate)+1) AS DECIMAL(5,2))PerAssemblyQtyMonthly,
FROM [Production].[BillOfMaterials] b
INNER JOIN dbo.Numbers n ON DATEDIFF(m, StartDate, EndDate)>=n.Number
WHERE [EndDate] IS NOT NULL