CODEWITHSUNDEEP
sqlsql-servert-sql
mimozz
mimozz

Reputation: 94

SQL Query Returns Duplicate Rows

SELECT  E.SSN AS SocialSecurityNumber ,
        E.Name + ' ' + E.LastName AS FullName ,
        J.Job ,
        R.Date ,
        R.STime AS StartTime,
        R.ETime AS EndTime,
        CASE WHEN R.Date BETWEEN O.SDate AND O.EDate THEN O.OffID
             ELSE 0
        END AS OffReason
FROM    Resume AS R
        INNER JOIN Employees AS E ON E.ID = R.EmpID
        INNER JOIN Jobs AS J ON J.ID = E.JobID
        LEFT JOIN Offs AS O ON E.ID = O.EmpID
                               AND R.Date BETWEEN O.SDate AND O.EDate
WHERE   E.JobLeft = 0
        AND R.Date BETWEEN '2014-11-26 00:00:00'
                   AND     '2014-11-26 23:59:59'
ORDER BY FullName

I wrote this SQL Query for my employee resume report program. I want to retrieve when the employees start to work and if they have a reason for not coming to work, return the reasonId or if they don't retrieve 0. In this query, the result returns duplicate rows when there is two or more offreasons for the same employee. Like vacation & national holiday. I searched for this but can't find the same situation. The removed rows are not important; one offreason is enough for me. So I tried the DISTINCT keyword but it doesn't solve the problem. So what can I do to solve this?

Upvotes: 0

Views: 96

Answers (1)

xlecoustillier
xlecoustillier

Reputation: 16361

The simplest would be to select only one of the reasons, using MAX for instance:

SELECT E.SSN                     AS SocialSecurityNumber, 
       E.Name + ' ' + E.LastName AS FullName, 
       J.Job, 
       R.DATE, 
       R.STime                   AS Start, 
       R.ETime                   AS END, 
       MAX(CASE 
             WHEN R.DATE BETWEEN O.SDate AND O.EDate THEN O.OffID 
             ELSE 0 
           END)                  AS OffReason 
FROM   Resume AS R 
       INNER JOIN Employees AS E 
               ON E.ID = R.EmpID 
       INNER JOIN Jobs AS J 
               ON J.ID = E.JobID 
       LEFT JOIN Offs AS O 
              ON E.ID = O.EmpID 
                 AND R.DATE BETWEEN O.SDate AND O.EDate 
WHERE  E.Left = 0 
       AND R.DATE BETWEEN '2014-11-26 00:00:00' AND '2014-11-26 23:59:59' 
GROUP  BY E.SSN, 
          E.Name + ' ' + E.LastName, 
          J.Job, 
          R.DATE, 
          R.STime, 
          R.ETime 
ORDER  BY FullName

Upvotes: 2

Related Questions