How to produce a revenue forecast in SQL?

I have a table with a list of contract line items in the form of

CREATE TABLE contracts_lines (
  contract_id integer,
  product_id integer,
  contract_line_start datetime,
  contract_line_end datetime,
  amount float
)

What I would like to produce is a VIEW (or populate a table) that allows me to determine how much revenue I can expect each month - we have a simple rule that each line is recognized evenly over the term of the line (i.e. daily revenue = amount / (contract_line_end - contract_line_start) )

The VIEW will be used as the basis fo开发者_Go百科r a measure group in an SSAS cube, so its structure should be something like

montly_revenue_forecast (
  year int,
  month int,
  product_id int,
  contract_id int,
  amount float
)

I'd like to do 12 months worth of forecast, from the day it's run and it will run through SSIS so I have access to its contstructs (like looping, etc.) I'd prefer not to have to write any stored procedures.

Any help is appreciated

What you need to do is first generate a sequence of dates for each contract/product and then use the sequence to group.

The most convenient way to generate a sequence is to use the rather poorly-documented spt_values table, like so:

WITH Contracts_CTE (contract_id, product_id, contract_day, amount) AS
(
    SELECT 
        cl.contract_id,
        cl.product_id,
        DATEADD(DAY, v.number, cl.contract_line_start),
        cl.amount / DATEDIFF(DAY, cl.contract_line_start, cl.contract_line_end)
    FROM contracts_lines cl
    CROSS JOIN master.dbo.spt_values v
    WHERE v.type = 'P'
    AND DATEADD(DAY, v.number, cl.contract_line_start) < cl.contract_line_end
)
SELECT
    DATEPART(YEAR, c.contract_day) AS contract_year,
    DATEPART(MONTH, c.contract_day) AS contract_month,
    c.product_id,
    c.contract_id,
    SUM(Amount) AS contract_amount
FROM Contracts_CTE c
GROUP BY
    c.product_id,
    c.contract_id,
    DATEPART(YEAR, c.contract_day),
    DATEPART(MONTH, c.contract_day)

Just a word of caution - the numbers in spt_values only go from 0 to 2047, so if you have contracts that are more than about 5 years long, then you'll need to generate a longer sequence. Easiest way to do that is to CROSS JOIN the spt_values table to itself, i.e.:

SELECT (v1.number * 2048) + v2.number
FROM master.dbo.spt_values v1
CROSS JOIN master.dbo.spt_values v2
WHERE v1.type = 'P'
AND v2.type = 'P'
AND ((v1.number * 2048) + v2.number) < 100000

Obviously you'd have to integrate that into the above query, but if it's really necessary then it shouldn't be too hard.

This isn't the full solution but you probably want to calculate the Montly Revenue for each project based on say 30-day month.

Something like this:

select contract_id, product_id, 
       amount / (datediff(day, contract_line_start, contract_line_end)) * 30 as MontlyRevenue
from contracts_lines