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 ..
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman