Summing volume by date range - SQL Server Forums

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)

Summing volume by date range

Author

Topic

emilly
Starting Member

4 Posts

Posted - 2008-06-16 : 11:36:34

I have a question on how to sum data by a certain date range. Here is the data I'm looking at. I have volume measured usually (but not always) every day. I want to sum the volume from the 2nd of the month to the first of the next month. I want to do this for every month. I have the columns of my data listed below. Can anyone help me with this? I've been trying to read up on it, but I'm not finding anything.

Entity Date Measured Volume
1 4/01/2008 5
1 4/02/2008 4
1 4/03/2008 6
1 4/04/2008 5
1 4/08/2008 7
1 4/12/2008 8
1 4/13/2008 5
1 4/14/2008 7
1 4/25/2008 8
1 4/30/2008 9
1 5/01/2008 6
1 5/02/2008 8

Thanks in advance for any help!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-06-16 : 11:39:12

moved from yak corral. hope you're using sql server 2005.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 11:41:44

[code]SELECT Entity,
CAST(MONTH(DateMeasured) AS varchar(2)) + CAST(YEAR(DateMeasured) AS varchar(4)) AS Period,
SUM(Volume)
FROM YourTable
GROUP BY Entity,
CAST(MONTH(DateMeasured) AS varchar(2)) + CAST(YEAR(DateMeasured) AS varchar(4))[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-16 : 11:58:36

If you use datetime column,


SELECT Entity,
       dateadd(month,datediff(month,0,DateMeasured),0) AS Period,
       SUM(Volume) 
FROM YourTable
GROUP BY  Entity,
dateadd(month,datediff(month,0,DateMeasured),0)

Madhivanan

Failing to plan is Planning to fail

emilly
Starting Member

4 Posts

Posted - 2008-06-16 : 12:02:43

This is working, but it's only summing the month. I need from the 2nd of the month to the 1st of the next. I think I might have it figured out, though. Thank you - I really appreciate it!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources