Spread total value over years of term with sum

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 2000 Forums

Transact-SQL (2000)

Spread total value over years of term with sum

Author

Topic

Coremirror
Starting Member

1 Post

Posted - 2007-05-17 : 14:38:50

I would like to show data over months instead of 1 date.

Current Table:

Date: Value: Term Year:
1/1/2005 500 3
7/8/2006 800 1

Below is what I would like it to return:

Date: Value: Total:
1/1/2005 13.89 13.89
2/1/2005 13.89 27.78
3/1/2005 13.89 41.67
4/1/2005 13.89 55.56
5/1/2005 13.89 69.45
6/1/2005 13.89 83.34
7/1/2005 13.89 97.23
8/1/2005 13.89 111.12
9/1/2005 13.89 125.01
10/1/2005 13.89 138.90
11/1/2005 13.89 152.79
12/1/2005 13.89 166.68
1/1/2006 13.89 180.57
2/1/2006 13.89 194.46
3/1/2006 13.89 208.35
4/1/2006 13.89 222.24
5/1/2006 13.89 236.13
6/1/2006 13.89 250.02
7/1/2006 80.56 330.58
8/1/2006 80.56 411.14
9/1/2006 80.56 491.70
10/1/2006 80.56 572.26
11/1/2006 80.56 652.82
12/1/2006 80.56 733.38
1/1/2007 80.56 813.94
2/1/2007 80.56 894.50
3/1/2007 80.56 975.06
4/1/2007 80.56 1055.62
5/1/2007 80.56 1136.18
6/1/2007 80.56 1216.74
7/1/2007 13.89 1230.63
8/1/2007 13.89 1244.52
9/1/2007 13.89 1258.41
10/1/2007 13.89 1272.30
11/1/2007 13.89 1286.19
12/1/2007 13.89 1300.08

Thank you for any ideas

Coremirror

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-17 : 15:48:56

make a date table and join to it.
you can find it here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-17 : 19:29:02

[code]
declare @tbl table
(
[DATE] datetime,
VALUE decimal(10,2),
TERM_YEAR int
)

insert into @tbl
select '20050101', 500, 3 union all
select '20060708', 800, 1

declare @min datetime, @max datetime

select @min = min([DATE]),
@max = max(dateadd(year, TERM_YEAR, dateadd(month, datediff(month, 0, [DATE]), 0) - 1))
from @tbl t

select [DATE],
[VALUE] = convert(decimal(10,2), sum(VALUE)),
[TOTAL] = convert(decimal(10,2), sum(TOTAL))
from
(
select d.[DATE],
VALUE = case when d.[DATE] <= t.end_date
then round(t.VALUE / (TERM_YEAR * 12), 2)
else 0
end,
TOTAL = case when d.[DATE] <= t.end_date
then round(t.VALUE / (TERM_YEAR * 12), 2) *
(datediff(month, t.[DATE], d.[DATE]) + 1)
else round(t.VALUE / (TERM_YEAR * 12), 2) *
(t.TERM_YEAR * 12)
end
from dbo.F_TABLE_DATE(@min, @max) d
left join
(
select [DATE], [VALUE], [TERM_YEAR],
start_date = dateadd(month, datediff(month, 0, [DATE]), 0),
end_date = dateadd(year, TERM_YEAR, dateadd(month, datediff(month, 0, [DATE]), 0) - 1)
from @tbl
) t
on d.[DATE] >= start_date
where d.DAY_OF_MONTH = 1
) a
group by [DATE]
order by [DATE]
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources