need help for a request - SQL Server Forums

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/

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

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!

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/

tngtng
Starting Member

7 Posts

Posted - 2010-03-17 : 04:02:24

thank you so much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

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

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources