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 |
|
topher
Starting Member
7 Posts |
 Posted - 2007-02-12 : 23:47:07
|
| Hi,trying to develop a report that shows the sum total for each month during a specified date range.I have parameters asking for the start date and end date of the date range, the report needs to show the months in between this date range.One of the fields in the dataset is durationminutes which I need to sum for each month then divide by 60 to show hours. I then need to show the total of each month in a bar graph (with each month along the x axis).If anyone can shed some light on how I am going to do this it would be greatly appreciated.Thanks. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-02-13 : 01:20:44
|
| [code]SELECT DATEADD(month, DATEDIFF(month, 0, {YourDateTimeColumnHere}), 0), SUM(DurationMinutes) / 60.0FROM {YourTableNameHere}WHERE {YourDateTimeColumnHere} >= @StartDate AND {YourDateTimeColumnHere} < DATEADD(day, 1, @EndDate)GROUP BY DATEADD(month, DATEDIFF(month, 0, {YourDateTimeColumnHere}), 0)ORDER BY DATEADD(month, DATEDIFF(month, 0, {YourDateTimeColumnHere}), 0)[/code]Peter LarssonHelsingborg, Sweden |
 |
|
|
topher
Starting Member
7 Posts |
Posted - 2007-02-13 : 19:18:48
|
| Perfect!Peter, you have saved me a day of headache and hassle, thank you very much. |
|
|
|
jhermiz
3564 Posts |
Posted - 2007-02-14 : 15:03:23
|
quote:
Originally posted by topher
Perfect!Peter, you have saved me a day of headache and hassle, thank you very much.
That translates to, "Peter thanks for doing my job for me, I still get my paycheck even though I did nothing!"
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
|
topher
Starting Member
7 Posts |
Posted - 2007-02-18 : 18:01:47
|
| Jhermiz, the least I can do is say thank you to someone who goes out of there way to help me. Seeing as you are so quick to criticise, where was your response with help? Keep your negativity to yourself. |
|
|
|
jhermiz
3564 Posts |
Posted - 2007-02-19 : 10:19:17
|
quote:
Originally posted by topher
Jhermiz, the least I can do is say thank you to someone who goes out of there way to help me. Seeing as you are so quick to criticise, where was your response with help? Keep your negativity to yourself.
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
|
|
|
|
|
|
Rolling month graph