SQL Server Performance

How to find duplicate data in the payroll database

Discussion in 'SQL Server 2008 Questions [Archive Only - Closed]' started by arsanksa, Apr 21, 2010.

  1. arsanksa New Member

    Please help me to create a query to check no duplicate data created for any employee(s) in the same month in Payroll Database with following fields
    Select Emp_No, Emp_Name, Month, year, Salary from HR_Tsalary where Year='2010' and month ='3'
  2. satya Moderator

    Is this a classroom based question?
  3. arsanksa New Member

    Recently our database is transferred to SQL server and I am a beginner with Sql server, I use following query in Access working well but in Sql result is zero
    SELECT Emp_No, Emp_Name, Salary
    FROM HR_Tsalary
    WHERE month ='3' and year ='10' and (((Emp_No) In (SELECT [Emp_No] FROM [HR_Tsalary] As Tmp GROUP BY [Emp_No] HAVING Count(*)>1 )))
  4. FrankKalis Moderator

    [quote user="arsanksa"]Recently our database is transferred to SQL server and I am a beginner with Sql server, I use following query in Access working well but in Sql result is zero
    SELECT Emp_No, Emp_Name, Salary
    FROM HR_Tsalary
    WHERE month ='3' and year ='10' and (((Emp_No) In (SELECT [Emp_No] FROM [HR_Tsalary] As Tmp GROUP BY [Emp_No] HAVING Count(*)>1 )))
    [/quote]
    If you just want to check for duplicates why not use something like:
    SELECT
    Emp_No, COUNT(*)
    FROM
    HR_Tsalary
    WHERE
    [year] = '10' AND
    [month] = '3'
    GROUP BY
    Emp_No
    HAVING
    COUNT(*) > 1;
  5. satya Moderator

    Good to know the requirement from your explanatin, hope you got required from Frank's reply.
  6. Adriaan New Member

    Note that the Access query you posted does not find duplicates for Emp+Year+Month for a given month+year.
    It actually returns all rows from HR_Tsalary for month='3' and year ='10', for all employees who have more than one row in HR_Tsalary (regardless of month and year).
  7. arsanksa New Member

    I have payroll record of more than 500 employees for one year in the database, I want to find only duplicate entry in a month; For example: I want to check any employee has more than one entry in the Payroll in the month of March 2010. The query is working fine from the server query analyzer, but when I try thru adodb using VB the result is Zero. I think the problem with the VB code not sql query syntax. Thank you for everybody,
  8. Adriaan New Member

    Normally it can depend on whether you're using ADO to connect to SQL Server, and let the server execute the query, or if ADO is executing client-side, using the database engine of Access, called Jet. But since both your original query and Frank's are valid in both Jet-SQL and T-SQL, that would not be a problem.
    The difference is that ...
    (A) Frank's query actually finds duplicates on (Employee + month=3 + year=10).
    (B) Your original query finds all (year=10 + month=3) entries for employees with at least two rows in the whole table.
  9. arsanksa New Member

    Please find herewith code,which I use under the command butter to make query:Dim cnn As ADODB.ConnectionDim rst As ADODB.RecordsetDim strquery Set cnn = New ADODB.Connection Set rst = New ADODB.Recordset strconnect = "Provider=SQLOLEDB" _ & ";User ID=" & Me!txtuid _ & ";Password=" & Me!txtpwd _ & ";Initial Catalog=" & Me!txtdatabase _ & ";Data Source=" & Me!txtserver Cnn.Open strconnect Strquery = “SELECT Emp_No, Count(*) AS Expr1 FROM HR_Tsalary where month='3' and year =’10’ GROUP BY Emp_No HAVING Count(*)>1”
    rst.Open strquery, cnn, adOpenStatic, adLockOptimistic
  10. Adriaan New Member

    The code looks fine. It is a server-side query. And since it is a different query from the original one, the results are different.
  11. arsanksa New Member

    When I try with following command working fine
    SELECT * FROM HR_Tsalary where month='3' and year =’10’ : Result is 473 records and I found that 2 duplicate records
    But, when I change with group by or sum etc.. the result is Zero
  12. Adriaan New Member

    On the duplicate rows, is Emp_No null?
    I'd also take a good look at those single quotes around '10' - they appear to be RIGHT single quotes, and I'm not sure how the db engine will be handling those.
  13. arsanksa New Member

    Sorry Mr. Adriaan,
    By mistake I type it wrong original text is: Select * from HR_Tsalary where month='3' and year ='2010': The result is same from Server and Client side
    But when I try follwing command from Server the result is OK but from Client the result is -1
    SELECT Projectno, SUM(Bsalary) as "Total Project Salary" FROM HR_Tsalary where month='3' and year ='2010' GROUP BY projectno
    Thank you,
  14. Adriaan New Member

    Again, that's a totally different query from the one you posted, and from the one that Frank made for you based on the requirements.
    Different query, different results.
  15. arsanksa New Member

    I want to explaint that all functions from server side working fine, but when I try same query from client side some functions are working such as select, order by but some functions are not working such as group by, sum, count, having
  16. Adriaan New Member

    These are not "functions", they are syntax. SQL is a query language, it is centred around resultsets, rather than one-by-one pieces of information
  17. arsanksa New Member

    Finally I solved the problem myself. I add only connection.CursorLocation = adUseClient then all of my problems solved.
    Thanks for all replay

Share This Page