Basically im looking at a query for someome. Someone (not me) wrote this query to grab the punchin time for employees and punch out time. The problem is if an employed punches in more than once in a day, lets say they punch in at 9am, punch out at 12pm, then they punch in at 1pm and punch out 5pm their query only grabs the first punch in and the last punch out, so it looks like the person worked from 9am to 5pm. Based on what I understand at this point, the query isn't designed for multiple Punchin's because of the select top 1 min and max??

SELECT DISTINCT SUBSTRING(CAST(ISNULL(tbldepartment.DepartmentNumber,0) AS VARCHAR(10)),1,3) AS FacilityID

, empMain.employeeId AS EmployeeId,convert(varchar(10), [timecard_dt], 20) AS PunchDate,

convert(VARCHAR(19),(

SELECT Top 1 MIN(tw.workingpunch_ts)

FROM timeWorkingPunch tw

WHERE tw.employee_id = timeWorkingPunch.employee_id

AND tw.inpunch_dt = timeWorkingPunch.inpunch_dt

AND tw.active_yn = '1'

AND tw.breaktype_id = 3

AND tw.inout_id = 1),20) AS PunchIn,

convert(VARCHAR(19),(

SELECT TOP 1 MAX(tw.workingpunch_ts)

FROM timeWorkingPunch tw

WHERE tw.employee_id = timeWorkingPunch.employee_id

AND tw.inpunch_dt = timeWorkingPunch.inpunch_dt

AND tw.breaktype_id = 3

AND tw.active_yn = '1'

AND tw.inout_id = 2),20) AS PunchOut,

(SELECT SUM(tc.worked_hr) FROM timeCard tc WHERE tc.employee_id = timeCard.employee_id AND tc.timecard_dt = timeCard.timecard_dt) AS PaidTime,

'' AS JobCode,'' AS PayCode

FROM timeWorkingPunch

INNER JOIN timeCard ON timeWorkingPunch.employee_id = timeCard.employee_id

LEFT OUTER JOIN empMain ON empMain.employee_id = timeWorkingPunch.employee_id

LEFT OUTER JOIN tblDepartment ON tblDepartment.department_id = timeCard.department_id

WHERE inpunch_dt = timecard_dt AND inpunch_dt > dateadd(d,datediff(d,0, dateadd(d,-15,getdate())),0) AND empmain.active_yn = '1'

AND CAST(SUBSTRING(CAST(ISNULL(DepartmentNumber,0) AS VARCHAR(10)),1,3) AS INT) >= 409

AND CAST(SUBSTRING(CAST(ISNULL(DepartmentNumber,0) AS VARCHAR(10)),1,3) AS INT) <= 414

AND (

SELECT TOP 1 MIN(tw.workingpunch_ts)

FROM timeWorkingPunch tw

WHERE tw.employee_id = timeWorkingPunch.employee_id

AND tw.inpunch_dt = timeWorkingPunch.inpunch_dt

AND tw.active_yn = '1'

AND tw.breaktype_id = 3

AND tw.inout_id = 1) IS NOT NULL

Results look like (see attached).

So I dumped the results from the query into a table and used a cte to check the freq of PUnchdate and EmployeeID:

with cte as

(

select EmployeeID, PunchDate, PunchIn, PunchOut, COUNT(*) Over (PARTITION By EmployeeID, PunchDate) as Freq

from domintest

)

select * from cte

where Freq>1

order by employeeID, PunchDate

No results where Freq>1 so it appears the query is performing as designed?? Let me know if my logic is correct.