Update daterange table with SUM query of another

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 2008 Forums

Transact-SQL (2008)

Update daterange table with SUM query of another

Author

Topic

jitasb
Starting Member

1 Post

Posted - 2013-08-19 : 19:16:07

Table DateRangeTab (Fromdate, Todate, TotalValue) has data such as:

2010-01-01, 2011-01-01, 0
2010-01-01, 2012-01-01, 0
etc (i.e lots of date ranges all with 0 in the TotalValue column)

I also have table ValueTab (DateofValue, Value) which has data such as:
2010-01-02, £25
2011-01-01, £45
2011-05-04, £65
etc (i.e. lots of different rows with dates and a value for each date)

I need to update DateRangeTab TotalValue column with SUM of Value from ValueTab table where the DateofValue falls in the range FromDate - Todate.

I tried something like the following :

Update DateRangeTab
set TotalValue = a.Value
from (
select sum(value) as Value from ValueTab VT join DateRangeTab DT on VT.DateofValue between DT.startdate and DT.ToDate) a

But it puts same total in each row of the DateRangeTab.
Hope someone can help or direct me to another post with a related answer.

Thanks

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-20 : 02:33:00

UPDATE DateRangeTab
SET TotalValue = (SELECT SUM(VT.Value) FROM ValueTab VT WHERE VT.DateofValue between Fromdate AND ToDate)

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources