SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Update daterange table with SUM query of another
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jitasb
Starting Member

United Kingdom
1 Posts

Posted - 08/19/2013 :  19:16:07  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 08/20/2013 :  02:33:00  Show Profile  Reply with Quote
UPDATE DateRangeTab
SET TotalValue = (SELECT SUM(VT.Value) FROM ValueTab VT WHERE VT.DateofValue between Fromdate AND ToDate)


--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000