i have a problem with using numbers like 1111, 8888, 7777 etc in that sort field... whats the point of limiting a "sort" field to these numbers? i dont...

first of all naming a column and the table "Students is no good!

, this gave me a duplicate and counted the number of duplicates

SELECT Student, IDNo,...

kevin_nikolai (4/23/2013)

---

Hi Sergiy, firstly thanks to you and everyone else who responded to my post.

I think I should rather have named the title of my post:

How to find duplicates...

how about if you join the fields?

Try this:

WITH CTE (emppin,trxdate, trxtime, DuplicateCount)

AS

(

SELECT emppin,trxdate, trxtime,

ROW_NUMBER() OVER(PARTITION BY emppin,trxdate, trxtime ORDER BY emppin) AS DuplicateCount

FROM dbo.rawtrx

)

DELETE

FROM CTE

WHERE DuplicateCount > 1

you need...

okay, after reading further your questions and scenarios... i doubt that my last post would work...

how many times do you have the same student recurring? do you ever have...

SELECT EmpPin, trxdate, trxtime, COUNT(*) AS dupes

FROM dbo.rawtrx

GROUP BY EmpPin, trxdate, trxtime

HAVING ...

exec sp_addlinkedserver @server='FTDP',

@srvproduct= 'Access',

@provider= 'Microsoft.ACE.OLEDB.12.0',

@datasrc= 'C:\ftdp.mdb'

you need to download the oledb.12.0 provider from microsoft website. just google it.

my problem is becoming even more interesting. I installed sql server...

Thanks for the reply. I guess the only way to find out is to actually try it.

Gonna let the forum know what my results are ... Speed wise, i...

can i just delete this topic? i mean no one responding to u...and i would rather it deleted.

thanks.

Okay, thanks for the reply. The system is running well for the time. I am 90% satisfied with its performance.

This is a Time and Attendance / Payroll system. It collects...

ah! yes, i did try this ...i got an error but i know what the reason for the error is so it will work. Thanks.

ALTER PROCEDURE dbo.zz_sp_testing_inout

AS

WITH rows AS (SELECT *, row_number() OVER (ORDER BY emppin, trxdate) AS rownum

FROM ...

with rows as (

select *, row_number() over (order by emppin, trxdate) as rownum

from rawtrx)

select *, rowsMinusOne.trxtime as trxtimeIN,

ISNULL(rows.trxtime,1) as trxtimeOUT

from rows as rowsMinusOne

...

To the original poster...If you haven't figured this out yet, let us know. I got the perfect solution...i feel so much like a genius day by day...

i think jack pretty much has the idea...i'm looking for a solution for this problem right now my self.

i originally used the quirky update to test the transaction number for...