SQL join to itself get second row with first and no duplicates

Hi Trying to get a clean reult of self join in SQL

Here is SQL:

SELECT DISTINCT(a.EVENTDATE)'Date',
                a.PERSONFULLNAME'Employee',
                a.STARTDTM'ShiftStart', 
                a.OUTPUNCHDTM'BreakStart',
                b.STARTDTM 'BreakEnd'开发者_运维问答,
                b.OUTPUNCHDTM 'ShiftEnd' 
FROM vp_timesheetpunch a 
LEFT OUTER JOIN vp_timesheetpunch b ON 
                a.EVENTDATE=b.EVENTDATE AND 
                a.STARTDTM <> b.STARTDTM AND 
                a.PERSONNUM=b.PERSONNUM AND 
                a.STARTDTM < b.STARTDTM 
WHERE a.PERSONNUM='104739' 
GROUP BY a.EVENTDATE, 
         a.PERSONFULLNAME, 
         a.STARTDTM, 
         b.STARTDTM, 
         a.OUTPUNCHDTM, 
         b.OUTPUNCHDTM
ORDER BY a.EVENTDATE

RESULT(2 Records on days with breaks recorded*):

2011-06-24  LAST, F  2011-06-24 07:30  2011-06-24 14:00  2011-06-24 15:00  2011-06-24 20:36 
2011-06-24 00:00  LAST, F  2011-06-24 15:00  2011-06-24 20:36  NULL  NULL

*I need to suppress the second result on any given day

@X-Zero has a point -- the query returns two columns for BreakStart and BreakEnd, so if an employee took TWO breaks, there's simply no way to show meaningful results.

Assuming that employees either take 0 or 1 break per day, this will probably give you what you asked for:

-- This part gets data for everyone that took a break.
-- Hopefully they took only ONE break
-- (if they took more, we'll get multiple rows here)
Select A.EventDate      'Date'
      ,A.PersonFullName 'Employee'
      ,A.StartDtm       'ShiftStart'
      ,A.OutPunchDtm    'BreakStart'
      ,B.StartDtm       'BreakEnd'
      ,B.OutPunchDtm    'ShiftEnd'
  From vp_TimeSheetPunch A
  Join vp_TimeSheetPunch B On A.EventDate=B.EventDate
                         And A.PersonNum=B.PersonNum
                         And A.StartDtm<B.StartDtm
Union
-- This part gets data for everyone that did not take a break.
Select C.EventDate      'Date'
      ,C.PersonFullName 'Employee'
      ,C.StartDtm       'ShiftStart'
      ,Null             'BreakStart'
      ,Null             'BreakEnd'
      ,C.OutPunchDtm    'ShiftEnd'
  From vp_TimeSheetPunch C
 Where Not Exists(Select *
                    From vp_timesheetpunch D
                   Where C.EventDate=D.EventDate
                     And C.PersonNum=D.PersonNum
                     And C.StartDtm<>D.StartDtm)

-- This orders the result using column numbers
Order By 1,2,3,4

This should do it.

If there are only maximum two rows for every employee and date:

SELECT a.EVENTDATE      'Date',
       a.PERSONFULLNAME 'Employee',
       a.STARTDTM       'ShiftStart', 
       a.OUTPUNCHDTM    'BreakStart',
       b.STARTDTM       'BreakEnd',
       b.OUTPUNCHDTM    'ShiftEnd' 
FROM 
    ( SELECT EVENTDATE,
             PERSONNUM,       
             PERSONFULLNAME, 
             MIN(STARTDTM)    AS STARTDTM,       
             MIN(OUTPUNCHDTM) AS OUTPUNCHDTM  
      FROM vp_timesheetpunch
      WHERE PERSONNUM = '104739' 
      GROUP BY EVENTDATE, 
               PERSONNUM, 
               PERSONFULLNAME
    ) AS a
LEFT OUTER JOIN vp_timesheetpunch b ON 
                a.EVENTDATE = b.EVENTDATE AND 
                a.PERSONNUM = b.PERSONNUM AND 
                a.STARTDTM  < b.STARTDTM 
ORDER BY a.EVENTDATE