Firstly Thanks a lot for the response ..

Like I mentioned I need to check the records created only in the last 30 days and find out the max count for each Cid and if there are no records in the last 30 days then I should get the last transaction based on datestamp if nothing then based on CID desc

Now in the below example I don't have any records for CNo in the last 30 days so I need to get the last record CId else the Higher number of CId for each CNo

O/P

10--> 2

11--> 1

12--> 2

13--> 1

14--> 2

15--> 3

Is there a way to get the below result in single query or should we be creating multiple temp tables and do the calc..

CREATE TABLE #temp

( CNO INT NOT NULL,

CId int NOT NULL

)

INSERT INTO #temp

SELECT 10 , 1

UNION

SELECT 10,2

UNION

SELECT 11 , 1

UNION

SELECT 12,2

UNION

SELECT 13 , 1

UNION

SELECT 14,2

UNION

SELECT 14 , 1

UNION

SELECT 15,2

UNION

SELECT 15,3

SELECT * FROM #temp1

CREATE TABLE #temp1

( CNo INT NOT NULL,

CId INT NOT NULL ,

CDate DATETIME NOT NULL

)

INSERT INTO #temp1

SELECT 10,1,'2013-05-07 15:33:40.043'

UNION

SELECT 10,1,'2013-06-07 15:33:40.043'

UNION

SELECT 10,1,'2013-06-05 15:33:40.043'

UNION

SELECT 10,2,'2013-06-31 15:34:03.857'

UNION

SELECT 10,2,'2013-06-03 15:34:03.857'

UNION

SELECT 10,2,'2013-06-04 15:34:03.857'

UNION

SELECT 10,2,'2013-06-02 15:34:11.027'

Thanks in advance ..