Latest Dates - 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)

Latest Dates

Author

Topic

starnold
Yak Posting Veteran

83 Posts

Posted - 2007-03-26 : 08:43:25

I am trying to get the latest contract dates, my SQL so far is:

Select A.subr_nbr, max(cont_strt_dt),B.SUBR_STAT_CD
-- Contracts table
from [PC SMART].dbo.CONT A
--subscriber details
JOIN [CONTRACTS].DBO.SUBR_STAT B
ON A.SUBR_NBR = B.SUBR_NBR
and A.countrycode = b.countrycode
--contract type
where A.CONT_CHRG_TYP_CD = 'N'
and A.COUNTRYCODE = 'GBR'
--exclude inactive contracts
and B.subr_stat_cd not like 'D'
group by A.subr_nbr, cont_strt_dt,B.SUBR_STAT_CD

This gives me all the contracts per subscriber. What I need is to get the latest for each subscriber?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-26 : 08:56:41

group by A.subr_nbr ~~, cont_strt_dt~~ ,B.SUBR_STAT_CD

Peter Larsson
Helsingborg, Sweden

starnold
Yak Posting Veteran

83 Posts

Posted - 2007-03-26 : 08:57:59

But I need the other 3 columns as well? If I take them out it won't allow me to run the query

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-26 : 09:04:40

Something similar to

select t1.col1, t1.col2, t1.col3
from table1 as t1
where t1.col4 = (select max(t2.colA) from table2 as t2 where t2.colB = t1.col1 and t2.ColR = t1.Col7)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources