Update Query for range of dates

[robojam]

Hi

Im looking to update about 80 rows in a table where I need to sum some values from the source table.

Basically I want to run a query that does this:

UPDATE tblDaily
SET SALES = x, REDEMPTIONS = y, NET = (x + y)
WHERE FDATE > 13-Feb-2006
AND FDATE < 14-Jun-2006

The values of x and y are stored in another table, and I can get them using this query:

SELECT SUM(x), SUM, SUM(x + y)
FROM tblSTAGING
WHERE FDATE > 13-Feb-2006
AND FDATE < 14-Jun-2006

What Im looking to do is to combine these queries so that I can run the UPDATE query with the SELECT query as a sub-query.

Does anyone know how to do this?

Thanks in advance!

 

[Puiu]

The solution that comes to me would be this:

create a stored proc where you have something like this:

declare @x as int, @y as int, @Sum as int

select @x= SUM(x)
FROM tblSTAGING
WHERE FDATE > 13-Feb-2006
AND FDATE < 14-Jun-2006

select @y = SUM
FROM tblSTAGING
WHERE FDATE > 13-Feb-2006
AND FDATE < 14-Jun-2006

select @Sum = @x + @y

UPDATE tblDaily
SET SALES = @x, REDEMPTIONS = @y, NET = @Sum


this also might work:

update d set d.Sales = sum(t.x),
d.Redemptions = sum(t.y)
d.Net = ( sum(t.x) + sum(t.y) )
from tblStaging t, tblDaily d
where t.idSomething = d.idSomething
and t.FDATE > 13-Feb-2006
AND t.FDATE < 14-Jun-2006

however i dont think i tried this before...im not sure its gonna work
try it and see if you get what you wanted