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.
| Author |
Topic |
|
fezz
Starting Member
12 Posts |
 Posted - 2010-11-23 : 11:11:29
|
| Hi All, Can someone point me in the correct direction for this issue:I have data like below, i need to create a table which tells me per month how much money is due in, so for ID 1 the value is 3000 over 3 months so 1000 a month starting in January through to MarchID, Start_Date, Period_Months, Value1, '01-01-2010 00:00:00.00', 3, 30002, '01-04-2010 00:00:00.00', 8, 800003, '01-08-2010 00:00:00.00', 1, 123456The End Result needs to look something like thisMonth, Total_ValueJanuary-10,1000February-10,1000March-10,1000April-10,10000May-10,10000June-10,10000July-10,10000August-10,124456Sept-10,10000Oct-10,10000Nov-10,10000Does anyone have any example code for this?? ThanksPete |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-11-23 : 16:12:51
|
| If you had a calendar table...[CODE]select cast(Month(c.CalDate) as varchar)) + '-' + right(cast(Year(c.CalDate) as varchar)) as [Month], Value / Period_Months as Total_Valuefrom MyTable tinner join Calendar con Day(c.CalDate) = 1and c.CalDate between t.Start_Date and DateAdd(Month, Period_Months - 1, t.Start_Date)[/CODE]=======================================No matter what side of the argument you are on, you always find people on your side that you wish were on the other. -Jascha Heifetz, violinist (1901-1987) |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
SQL Grouping by Date