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
 Other SQL Server 2008 Topics
 need help for a request

Author  Topic 

tngtng
Starting Member

7 Posts

Posted - 2010-03-15 : 10:11:49
Hello, I have 2 tables:

order: reference (primary key), date (datetime, may be null), provider_reference (int), amount (decimal(18,2)), status ('valided', 'accepted', 'saved', null)

provider: reference (primary key, which is order.provider_reference), annuel_amount(decimal(18,2))

I'd like to add all amounts (order.amount) of orders validated (order.status='validates') of the current year (something like year(order.date)=year(getdate())) & put this result into provider.annuel_amount.

How can I do, thanks for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 10:20:22
[code]
update p
set p.annuel_amount=o.totamnt
from provider p
inner join (select reference,sum(amount) as totamnt
from order
where date >= dateadd(yy,datediff(yy,0,getdate()),0)
and date < dateadd(yy,datediff(yy,0,getdate())+1,0)
and status = 'validated'
group by reference)o
on o.reference=p.reference
[/code]

EDIT:Included missing column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-15 : 10:43:26
quote:
Originally posted by visakh16


update p
set p.annuel_amount=o.totamount
from provider p
inner join (select reference,sum(amount) as totamnt
from order
where date >= dateadd(yy,datediff(yy,0,getdate()),0)
and date < dateadd(yy,datediff(yy,0,getdate())+1,0)
and status = 'validated'
group by reference)o
on o.reference=p.reference


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

tngtng
Starting Member

7 Posts

Posted - 2010-03-15 : 11:48:40
oups, sorry, I mistaked: I have 3 tables:

order: reference (primary key), date (datetime, may be null), provider_reference (int), budget_reference (int), status ('valided', 'accepted', 'saved', null)

provider: reference (primary key, which is order.provider_reference), annuel_amount(decimal(18,2))

budget: reference (primary key, which is order.budget_reference), amount (decimal(18,2))

(amount is in another table, not in the order table). Please help me again!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 11:53:42
[code]update p
set p.annuel_amount=tmp.totamnt
from provider p
inner join (select o.reference,sum(b.amount) as totamnt
from order o
join budget b
on b.reference = o.reference
where o.date >= dateadd(yy,datediff(yy,0,getdate()),0)
and o.date < dateadd(yy,datediff(yy,0,getdate())+1,0)
and o.status = 'validated'
group by o.reference)tmp
on tmp.reference=p.reference
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

tngtng
Starting Member

7 Posts

Posted - 2010-03-17 : 04:02:24
thank you so much!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 04:28:35
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -