Finding the difference in a date range 1

[rdeleon]

sql 2005

I have a table that looks like:

Date BeginValue EndValue
12/1/2008 04:00:00 0 100
12/1/2008 10:00:00 100 600
12/1/2008 18:00:00 600 400
12/2/2008 11:00:00 0 200
12/3/2008 10:00:00 300 1000
12/3/2008 19:00:00 1000 1100


what I need is the following:

Date Change
12/1/2008 400
12/2/2008 200
12/3/2008 800

Any help is appreciated.

 

[bborissov]

How you get to this values?
400 is EndValue of the 12/1.
200 is (400-200)? maybe
800 is ?????? (1100 - 200 = 900)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[PDreyer]

select dateadd(dd ,datediff(dd,'20000101',Date) ,'20000101')
, sum(EndValue-BeginValue)
from atable
group by dateadd(dd ,datediff(dd,'20000101',Date) ,'20000101')

 

[rdeleon]

Thanks PDreyer.

I just realized that I also need to be able to group by week and month.

(I realize my previous data was off in the last post)

Date BeginValue EndValue
12/1/2008 04:00:00 0 100
12/1/2008 10:00:00 100 600
12/1/2008 18:00:00 600 400
12/2/2008 11:00:00 400 200
12/3/2008 10:00:00 200 1000
12/3/2008 19:00:00 1000 1100
12/9/2008 08:00:00 1100 1400

what I need is the following:

for week
Date Change
12/7/2008 1100
12/14/2008 300

for month
Date Change
12/31/2008 1400

Thanks in advance

 

[PDreyer]

Instead of dd use wk or mm