1 Reply Latest reply: Aug 20, 2013 10:27 AM by Andreas Weiden RSS

    Query building


      Hi all,

      I am writing a database application and have ran into an issues that I am having a hard time resolving and would like some help. The application tracks time and labor in my organization. What I am trying to is run a payroll report that calculate all employees time over a given date range.

      I would like the dataset to bring back a single instance of the name of the employee and all the time worked for the given date range.

      But when I run my query I get the employee name for each instance of the time entered for the the given date range. Here is my query. Can someone please help.


      Sincerely Traff


      ALTER PROCEDURE [dbo].[Sp_PayrollReport]

            -- Add the parameters for the stored procedure here

            @StartDate date,

            @EndDate date



            -- SET NOCOUNT ON added to prevent extra result sets from

            -- interfering with SELECT statements.

            SET NOCOUNT ON;


          -- Insert statements for procedure here

            SELECT Employeetb.EmployeeID, Employeetb.FName + ' ' + Employeetb.MName + ' ' + Employeetb.LName AS EmployeeName,DATENAME(dw,TimeLogged.StartDate)AS NameOfDay,

            CONVERT(TIME,TimeLogged.ClockedIn) AS ClockedIn,CONVERT(TIME,TimeLogged.ClockedOut) AS ClockedOut,CONVERT(varchar(10),TimeLogged.StartDate,101) AS TheDate,

            DATEDIFF(mi,OverTime.StartTime,OverTime.EndTime) / 60 AS Overtime,

            DATEDIFF(mi,TimeLogged.ClockedIn,TimeLogged.ClockedOut) / 60 + DATEDIFF(mi,OverTime.StartTime,OverTime.EndTime) / 60 AS TotalHours

            FROM Employeetb,TimeLogged,OverTime


            WHERE Employeetb.EmployeeID = TimeLogged.EmployeeID

                  AND TimeLogged.StartDate >= @StartDate AND TimeLogged.EndDate <= @EndDate

                  AND OverTime.OvertimeDate >= @StartDate AND OverTime.OvertimeDate <= @EndDate


            GROUP BY Employeetb.FName + ' ' + Employeetb.MName + ' ' + Employeetb.LName,TimeLogged.StartDate,TimeLogged.ClockedIn,TimeLogged.ClockedOut,TimeLogged.StartDate,

                     OverTime.StartTime,OverTime.EndTime,Employeetb.EmployeeID ORDER BY Employeetb.FName + ' ' + Employeetb.MName + ' ' + Employeetb.LName