Help - Simple date 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 2000 Forums

Transact-SQL (2000)

Help - Simple date query

Author

Topic

dean.c.cummins
Starting Member

6 Posts

Posted - 2010-11-08 : 22:25:42

Im struggling to get my head around this one...

i have the following sql query...

select t_cpls,t_pric,t_stdt
from ttdsls032100
where t_item = '980100626000'
order by t_cpls, t_stdt DESC

which give the following dataset...

001 5.3 2010-01-01 00:00:00.000
001 5.9 2009-01-01 00:00:00.000
001 6.2 2008-01-01 00:00:00.000
002 7.43 2010-01-01 00:00:00.000
002 7.43 2009-01-01 00:00:00.000
002 7.43 2008-01-01 00:00:00.000
003 6.5 2010-01-01 00:00:00.000
003 6.5 2009-01-01 00:00:00.000
003 5.49 2008-01-01 00:00:00.000
005 5.93 2009-12-04 00:00:00.000
005 5.75 2009-01-01 00:00:00.000
006 6.12 2009-04-12 00:00:00.000
006 5.94 2009-01-01 00:00:00.000
007 6.34 2010-01-01 00:00:00.000
008 5.25 2010-01-01 00:00:00.000

ive highlighted the problems with this in bold. i basically want to only return the maximum date for a year per t_cpls! so it want it to produce the following dataset...

001 5.3 2010-01-01 00:00:00.000
001 5.9 2009-01-01 00:00:00.000
001 6.2 2008-01-01 00:00:00.000
002 7.43 2010-01-01 00:00:00.000
002 7.43 2009-01-01 00:00:00.000
002 7.43 2008-01-01 00:00:00.000
003 6.5 2010-01-01 00:00:00.000
003 6.5 2009-01-01 00:00:00.000
003 5.49 2008-01-01 00:00:00.000
005 5.93 2009-12-04 00:00:00.000
006 6.12 2009-04-12 00:00:00.000
007 6.34 2010-01-01 00:00:00.000
008 5.25 2010-01-01 00:00:00.000

any help would be greatly appreciated.

many thanks

Dean x

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-11-09 : 01:41:38

[code]
select t_cpls,t_pric,t_stdt
from ttdsls032100 t
inner join
(
select t_cpls, t_stdt = max(t_stdt)
from ttdsls032100
group by t_cpls, datepart(year, t_stdt)
) m on t.t_cpls = m.t_cpls and t.t_stdt = m.t_stdt
where t_item = '980100626000'
order by t_cpls, t_stdt DESC
[/code]

KH
[spoiler]Time is always against us[/spoiler]

dean.c.cummins
Starting Member

6 Posts

Posted - 2010-11-09 : 06:01:47

many thanks :-) x

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources