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 |
|
VeselaApo
Posting Yak Master
114 Posts |
 Posted - 2010-11-19 : 20:06:49
|
| Hi I have this table:Cal_dates Days N/A Service charge Month Elapsed 1/2/11 1 1 400.00 6.001/3/11 1 2 200.00 6.001/4/11 1 3 133.00 6.001/5/11 1 4 100.00 6.001/6/11 1 5 80.00 6.001/7/11 1 6 66.00 6.001/8/11 1 7 57.00 6.001/9/11 1 8 50.00 6.001/10/11 1 9 44.00 6.001/11/11 1 10 40.00 6.001/12/11 1 11 36.00 6.001/13/11 1 12 33.00 6.001/14/11 1 13 30.00 6.001/15/11 1 14 28.00 6.001/16/11 1 15 26.00 6.001/17/11 1 16 25.00 6.001/18/11 1 17 23.00 6.001/19/11 1 18 22.00 6.001/20/11 1 19 21.00 6.001/21/11 1 20 20.00 6.001/22/11 1 21 19.00 6.001/23/11 1 22 18.00 6.001/24/11 1 23 17.00 6.001/25/11 1 24 16.00 6.001/26/11 1 25 16.00 6.001/27/11 1 26 15.00 6.001/28/11 1 27 14.00 6.001/29/11 1 28 14.00 6.001/30/11 1 29 13.00 6.001/31/11 1 30 13.00 6.002/1/11 2 31 12.00 6.002/2/11 2 32 12.00 6.002/3/11 2 33 12.00 6.002/4/11 2 34 11.00 6.002/5/11 2 35 11.00 6.002/6/11 2 36 11.00 6.002/7/11 2 37 10.00 6.002/8/11 2 38 10.00 6.002/9/11 2 39 10.00 6.002/10/11 2 40 10.00 6.002/11/11 2 41 9.00 6.002/12/11 2 42 9.00 6.002/13/11 2 43 9.00 6.002/14/11 2 44 9.00 6.002/15/11 2 45 8.00 6.002/16/11 2 46 8.00 6.002/17/11 2 47 8.00 6.002/18/11 2 48 8.00 6.002/19/11 2 49 8.00 6.002/20/11 2 50 8.00 6.002/21/11 2 51 7.00 6.002/22/11 2 52 7.00 6.002/23/11 2 53 7.00 6.002/24/11 2 54 7.00 6.002/25/11 2 55 7.00 6.002/26/11 2 56 7.00 6.002/27/11 2 57 7.00 6.002/28/11 2 58 6.00 6.00I want to create one last column that will sum up the service charge ($6) for each month. Thus, for january i will have 6*number of days in january-31, February = 6* number of days in Feb (28)..etc.What kind of looping statement and formula for counting days in any given month I should use? thanks! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-11-19 : 22:48:08
|
| SELECT DateName(month, Cal_dates) Month, Sum([Service Charge])FROM myTableGROUP BY DateName(month, Cal_dates) |
 |
|
|
VeselaApo
Posting Yak Master
114 Posts |
Posted - 2010-11-22 : 09:59:59
|
| Hi Rob -thanks - this helps! however, I have a list of dates for 3 years so when I execute this statement, it adds up the total service charge for 3 years together across each month...how can I separate the sum-ups by year as well? thanks! |
|
|
|
VeselaApo
Posting Yak Master
114 Posts |
Posted - 2010-11-22 : 10:01:26
|
| In addition, I need to display the result of the summation on the 10th of each month for the previous month..thanks! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-11-24 : 10:57:31
|
| SELECT DatePart(year, Cal_dates) Year, DateName(month, Cal_dates) Month, Sum([Service Charge])FROM myTableGROUP BY DatePart(year, Cal_dates), DateName(month, Cal_dates)I'm not clear what you mean by your last post. Do you mean you want the sum of Service charges from Oct. 10 through Nov. 10? |
|
|
|
|
|
|
|
|
Looping