SQL Update a DateRange table from a SUM query of another table -
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 0 in totalvalue column)
i have table valuetab (dateofvalue, value) has data such as:
2010-01-02, £25 2011-01-01, £45 2011-05-04, £65
etc (i.e. lots of different rows dates , value each date)
i need update daterangetab totalvalue column sum of value valuetab table dateofvalue falls in range fromdate - todate.
i tried following :
update daterangetab set totalvalue = a.value (select sum(value) value valuetab vt join daterangetab dt on vt.dateofvalue between dt.startdate , dt.todate)
but puts same total in each row of daterangetab. hope can or direct me post related answer.
thanks
try this:
update daterangetab set totalvalue = (select sum(value) valuetab dateofvalue between a.fromdate , a.todate group dateofvalue)
i cannot test approximate... try , adapt needs.
Comments
Post a Comment