Spreading A Field into multiple months

[Guest]

I'm currently working on a database where I need to spread a amount [Charge]
over many months.

The key Fields here are:

[JobCodeUnique] - Primary Key
[JobCode] - system code
[Charge] - Value in USD
[Chargebilledcurrency] - Value in Local Currency
[FixedFeeDate] - Date "Sold"
[Month] - Month Number
[Year] - Year
[Sub Start] - Date to start from
[Sub Period] - The number of periods to spread over

To keep it unique I have been doing this in Excel, but it's painful & time
consuming.

What I need as an output is a record for each month in the main database, so
that when I run revenue reports, I only pick up the relevant amount.

So for example if I have a job ABC1X1234 for £120,000, Starting on 1/1/2007
til 31/12/2007, what I need are records:

JobCodeUnique Local FixedFeeDate
ABC1X1234-0107 £10,000 01/01/2007
ABC1X1234-0207 £10,000 01/02/2007
.......
ABC1X1234-1207 £10,000 01/12/2007

I can work out the months and USD versions from there!

Many Thanks

 

[Sylvain Lafontaine]

The easiest way would be to create another table which will have one record
for each day of each job and with the corrected spreaded amount.

A second possibility would be to create a multi-statement table valued UDF
(User Defined Function) that will create and return a table with the correct
values; however, this will require that the computation be done each time:

http://www.devarticles.com/c/a/SQL-Server/Creating-User-Defined-Functions-In-SQL-Server-2000/4/

 

[Guest]

Thanks Sylvain!!