Need Help with Query - 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 2005 Forums

Transact-SQL (2005)

Need Help with Query

Author

Topic

peryan77
Starting Member

8 Posts

Posted - 2009-12-28 : 11:05:20

Below are my fields. The goal is to return the first non zero value for each country for the most recent date, and it needs to be summed.

Sample Data: (Final Result is at bottom)

Country Date Value
US 1/1/2008 0
US 1/1/2009 23
US 1/1/2009 10
US 1/1/2010 24
France 1/1/2009 0
France 1/1/2010 0
France 1/1/2011 13

Result Should Be:
US 1/1/2009 33
France 1/1/2011 13

I am having difficulty getting this correct so any help would be appreciate.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-28 : 11:19:13

Try this

select * from 
(
select row_number() over (partition by country order by datecol) as seq,country,datecol,sum(val) as val
from table1
where val > 0
group by country,datecol
) t
where t.seq = 1

creieru
Starting Member

12 Posts

Posted - 2009-12-28 : 11:24:54

or try this:
select A.Country, A.Date, sum(A.Value)
from TabelaA A
inner join
(
select Country, min(Date) as Date
from TabelaA
where [Value]>0
group by Country
)as B on A.Country = B.Country and A.Date = B.Date
group by A.Country, A.Date

peryan77
Starting Member

8 Posts

Posted - 2009-12-28 : 11:37:48

Thanks that did the trick. :) First time I used partition by.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-28 : 11:41:28

Np..You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources