Returning value based on latest date

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 2005 Forums

Transact-SQL (2005)

Returning value based on latest date

Author

Topic

jay83091
Starting Member

14 Posts

Posted - 2011-06-01 : 03:04:30

Hi Experts,

I am trying to extract sum units for each ID where date is max.

I've been trying different methods but I just can't get it.

Could anyone please help?

Thank you for your help in advance

NameID Date Units
44 2010-05-31 0.000
44 2010-06-30 1.000
44 2011-03-31 0.000
44 2011-03-31 892035.730
303 2010-05-31 100000.000
4004 2010-05-31 935000.000
4004 2010-07-31 970000.000
4004 2010-11-30 1000000.000
4004 2011-04-30 1085000.000

If returned correctly
NameID 44 should return 892035.730
NameID 303 should return 100000.000
NameID 4004 should return 1085000.000

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-01 : 03:30:22

select NameId, Units
from
(
select
row_number() over (partition by NameId order by Date DESC, Units ASC) as rownum,
*
from YourTable
)dt
where rownum = 1

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

jay83091
Starting Member

14 Posts

Posted - 2011-06-01 : 19:15:29

Hi webfred,

Thank you for your reply.

Your code works partially. it doesn't return the correct result for NameID 44.

It is returning 0 instead of 892035.730

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-01 : 20:16:14

Add a tweak to Fred's code like this:


select NameId, Units
from
(
   select
   row_number() over (partition by NameId order by Date DESC, Units ASC) as rownum,
   *
   from
   (
      select
         nameId,
         Date,
         sum(units) as units
      from
         YourTable
      group by
         nameId,
         Date 
   ) dt1
)dt
where rownum = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources