Reporting Services - Calculate Time Clock punchtimes

I am build a report using SSRS that shows an employee weekly time punches and calculate the employees hours.  So far, I am able to show the punches and clock actions but I am having a breakdown on calculating the time between clock in and clock out; thusly giving me the weekly sum of hours.

I must take into account any missed punches....  Any assistance is greatly appreciated.
SELECT     EmployeeNum_1.EmployeeID, EmployeeNum_1.TempId, EmployeeNum_1.Unldr_#, EmployeeNum_1.Last_name, EmployeeNum_1.First_name, 
                      EmployeeNum_1.Active, EmployeeNum_1.PayRateHourly, EmployeeNum_1.PayRatePercentage, EmployeeNum_1.SpecialPay, 
                      EmployeeNum_1.HolidayPay, EmployeeNum_1.SalaryPay, WorkDays_1.Workdate, WorkdayPunches_1.Punchkey AS PunchKey, 
                      WorkdayPunches_1.PunchTime, WorkdayPunches_1.Action, WorkdayPunches_1.Note, WorkdayPunches_1.Deleted AS PunchDelete, 
                      WorkDays_1.deleted AS WorkDayDelete, EmployeeNum_1.JobTitle
FROM         EmployeeNum AS EmployeeNum_1 INNER JOIN
                      WorkDays AS WorkDays_1 ON EmployeeNum_1.Unldr_# = WorkDays_1.Unldr_# INNER JOIN
                      WorkdayPunches AS WorkdayPunches_1 ON WorkDays_1.UID = WorkdayPunches_1.Punchkey
WHERE     (EmployeeNum_1.Active = 1) AND (DATEPART(wk, WorkdayPunches_1.PunchTime) = DATEPART(wk, @Date))
ORDER BY WorkDays_1.Workdate, WorkdayPunches_1.PunchTime

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris LuttrellSenior Database ArchitectCommented:
Can you give some example data and the expected results?  Include an example of a Missed Punch and what you want to do with it in the results.
NCSOAuthor Commented:
If an employee misses a time punch the supervisor is responsible for correcting it before the pay period ends; I must calculate the span of time between a clock in and a clock out.  Graphically, it would be nice to show the hours on the clock out line and then show a cumulative total for the period in the "Total"

You need to change your sql to get the hours for the start time and end time.

Add an  join to your statement that will get the last clock time.

this way you can then place the hours in the hour column since you can take the 2 times and do a date diff by hours on it . You can then sum the hours to get the proper total hours

NCSOAuthor Commented:
Can you give me an example for the join statement?
give some sample data from

FROM         EmployeeNum  
INNER JOIN       WorkdayPunches

Need to use this to get the next record. but I need to see the data and table formats to get it
inner join (select PunchTime as Punchtime1 from WorkdayPunches_1
      Where   Max(WorkDays_1.UID) < WorkDays_1.UID ) as Time2 on  WorkDays_1.UID = WorkdayPunches_1.Punchkey                    

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Reporting Tools

From novice to tech pro — start learning today.