Lets insert few more records into the #temp1 table in addition to the existing one..

INSERT INTO #temp1

SELECT 11,1,'2013-07-17 15:33:40.043'

UNION

SELECT 11,1,'2013-07-27 15:33:40.043'

UNION

SELECT 11,1,'2013-08-05 15:33:40.043'

UNION

SELECT 11,2,'2013-07-30 15:34:03.857'

UNION

SELECT 15,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'

now when you execute your query all I would get is for the Cno 15 you will Cid 3 but as per the logic it should be 2 ..

Logic..

1. More no of records created in the last 30 days fpr each Cno if not found

2. Get the last record created it need not be in the last 30 days

3. If no record available get the highest number..

The query doesn't work when I put the date filter ..

LEFT JOIN #temp1 T1

ON T.CNO = T1.CNo

AND T.CId = T1.CId

AND CDate >= GETDATE() -30