Till now i am good.then how would i check the condition duplicate count >1 then i need to execute query for further processing in order to display output as...

i have learned lot from ur suggestions.

i have executed above query it is giving results as previous

- I've formatted it and added a new column [RowType] to help you to understand what's happening.

-- Run it and investigate the output.

When i execute above query it is displaying...

yes need to fix the baove query

If duplicate record count >0

i need to execute below staement

SELECT @status =status from status_table where status_Id = 102

desired output would be @status is Duplicate

SELECT count(L1.AcctNo) totalcount,L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles

where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID

and L1.AcctNo=L2.AcctNo

and L1.MarketCode NOT IN (SELECT MarketCode from Markets where (AllowDupes = '1' OR UPPER(AllowDupes) = 'Y' ))

group by L1.AcctNo,L1.ReceivedFileID...

My Query is i want to pull duplicate records from the table and get the count of each duplicate record from the table.i wrote below query

IF EXISTS(

SELECT L1.AcctNo,L1.ReceivedFileID from Legacy_crfcard...

i could not understand below query.when i execute below query i am getting errors.

SELECT COUNT(*) -- count the dupesets

FROM (

SELECT n = 1 -- i dont understand this statement

FROM Legacy_crfcard...

Thanks for your help.i am getting the expected results.

sql server 2005

I was tried with your solution below but no luck.

select * from data_table where cast(CREAT_TM as DATE) = '2012-07-01'

i am getting error as

Type DATE is not a defined...