Creating view based upon date range

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Creating view based upon date range

Author

Topic

btamulis
Yak Posting Veteran

64 Posts

Posted - 2007-08-11 : 08:09:34

I checked BOL and could not find any information that would help with my situation - I searched this forum and found a really cool date function posted by a forum member - but it was far more complex than the solution I seek.

Here's the scenario - I need to create a SQL view that will represent a 12 month amortization schedule.

source data table:

sourcedoc - INV9
itemnumber - ABC
begdate - 12/01/07
enddate - 11/30/07
numberofperiods 12
dollars - $1200

desired result

sourcedoc - INV9
itemnumber - ABC
period1date - 12/01/07
period1dollars - $100
period2date - 1/1/08
period2dollars - $100
etc - with a date column and a dollar column for the 12 periods.
Note - some records will not have 12 periods - but no records will have more than 12 periods.

If the number of periods is < 12 - the record should have 12 columns with 'null'....

Like I said - really didn't see anything in BOL - thank you in advance for any assistance....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-11 : 10:23:08

Maybe if you posted a link to the function you found, we would see what you want.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-11 : 10:25:51

Heck.

What if you posted some proper sample data and your expected output?
What happens if the dollars are not evenly divided by numberofmonths? 1300 / 9 for example? Where will the 4 dollars left be?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources