Sum Price Based on Date - 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

General SQL Server Forums

New to SQL Server Programming

Sum Price Based on Date

Author

Topic

yawnzzzz
Starting Member

13 Posts

Posted - 2010-06-11 : 09:22:48

Example Table:

Order Number--Seq Number--Price--Date Added
1-------------1-----------$10----6/10/2010
1-------------2-----------$15----6/10/2010
1-------------2-----------$12----6/09/2010
2-------------1-----------$1-----6/10/2010
2-------------1-----------$2-----6/09/2010

This is my table representing orders placed and the products included. The table records every time a change happens by keeping track of a 'seq number'. So if two products have the same 'seq number' that means the one with the newest date replaced the one with the older date. So using that logic, I need to sum the total price for each order. So my end result would be a query that returns:

Order Number--Price
1-------------$25 ($10 + $15)
2-------------$1

Any direction would be helpful. Thanks!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-11 : 09:31:59

[code]select Order_Number, SUM(Price) from
(
select row_number() over (partition by Order_Number,Seq_Number order by Date_Added desc ) as seq , * from ExampleTable
) t
where seq = 1
group by Order_Number[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-11 : 09:32:38

select
orderno,
sum(price) as price
from
(
select
row_number() over (partition by orderno,seqno order by dateAdded DESC) as rnum,
*
from your_table
)dt
where rnum=1
group by orderno

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-11 : 09:33:13

No, you're never too old to Yak'n'Roll if you're too young to die.

yawnzzzz
Starting Member

13 Posts

Posted - 2010-06-11 : 09:39:14

Wow, thanks for the quick responses. I'm going to throw them into my query and see if they work right now. Thanks!

yawnzzzz
Starting Member

13 Posts

Posted - 2010-06-11 : 09:46:07

Just ran into one problem. This database is on SQL Server 2000, and it appears that the row_number() function didn't come out until 2005. Any alternatives?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-11 : 10:42:29

Try this

select a.Order_Number,SUM(a.Price) as Price
from ExampleTable a
inner join
(
select Order_Number,Seq_Number,MAX(Date_Added) as Date_Added
from ExampleTable 
group by Order_Number,Seq_Number
) b
on a.Order_Number = b.Order_Number and a.Seq_Number = b.Seq_Number and a.Date_Added = b.Date_Added
group by a.Order_Number

yawnzzzz
Starting Member

13 Posts

Posted - 2010-06-11 : 10:51:31

I'm combining it with another part of my query, but it looks like it will work. Thanks again.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-11 : 11:00:13

Np. Let us know if it doesn't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources