SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 MAX and Group By
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nnmmss
Starting Member

3 Posts

Posted - 12/08/2012 :  08:40:27  Show Profile  Reply with Quote
suppose i have a table with following data in it.
Id CompanyId PersonelNo ContractDate
==============================================
1 1 10 2011/12/29
2 1 11 2011/12/29
3 2 10 2011/12/29
4 1 10 2012/06/30
5 1 10 2012/12/29
6 2 10 2012/12/29

I need to have Maximum Date for the specified Year, I mean if i want to know maximum contractDate for 2011 i should have the following result
it.
CompanyId PersonelNo ContractDate
=========================================
1 10 2011/12/29
1 11 2011/12/29
2 10 2011/12/29

and if i need to have it for year 2012 the result should be
CompanyId PersonelNo ContractDate
==========================================
1 10 2012/12/29
2 10 2012/12/29

so i did like this

SELECT DISTINCT TOP 100 PERCENT MAX(ContractDate) AS ContractDate, PersonelNo, CompanyId
FROM dbo.Contract
GROUP BY PersonelNo, CompanyId , ContractDate
HAVING (ContractDateLIKE '2012%')

the result would be
CompanyId PersonelNo ContractDate
==========================================
1 10 2012/06/30
1 10 2012/12/29
2 10 2012/12/29

how should write the query?

thank you

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/08/2012 :  09:31:57  Show Profile  Reply with Quote
Remove ContractDate column from the group by clause
SELECT DISTINCT TOP 100 PERCENT MAX(ContractDate) AS ContractDate, PersonelNo, CompanyId 
FROM dbo.Contract
GROUP BY PersonelNo, CompanyId
HAVING (ContractDate LIKE '2012%')


From the way you have written the query, it looks like Contract Date is a character type column. It would be much better to have DATETIME as the data type for that.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/08/2012 :  13:11:46  Show Profile  Reply with Quote

Select C.* from Contract C
inner join
(
Select CompanyId,PersonelNo,MAX(ContractDate) ContractDate
from dbo.Contract
Group by CompanyId,PersonelNo
)CT.CompanyId = C.CompanyId and CT.PersonelNo = C.PersonelNo
and CT.ContractDate  = C.ContractDate
Where Year(ContractDate) = 2012
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000