This query provides the basic information for employees detail timecards. This does not include the overall clock in and clock out, but only the time cards that are created for production purposes.

None. This is a selection query and no data is modified in the running of it.

DECLARE @startdate datetime;
DECLARE @enddate datetime;
-- uncomment this for SQL, comment this out for Cyrious SQL Reports
-- set @startdate = '4/1/2010';
-- set @enddate   = '4/30/2010 11:59';
-- comment this out for SQL, uncomment this for Cyrious SQL Reports
SET @startdate = ;
SET @enddate   = ;
SELECT 
    (SELECT LastName +', '+FirstName FROM Employee WHERE ID = EmployeeID) AS EmployeeName,
    (SELECT OrderNumber FROM TransHeader WHERE ID = Journal.TransactionID) AS OrderNumber,
    (SELECT StationName FROM Station WHERE ID = Journal.StationID) AS Station,
    StartDateTime, Journal.EndDateTime, Description,
    (StraightTime + OverTime + DoubleTime + ShiftDiffTime) AS HoursOn,
    (CASE WHEN ManuallyAdjusted = 1 THEN 'Yes' ELSE 'No' END) AS Adjusted,
    Journal.ModifiedDate AS EntryDate,
    Journal.EmployeeID,
    Journal.StationID,
    Journal.TransactionID AS TransHeaderID,
    TransDetailID
FROM Journal
JOIN TimeCard ON Journal.ID = TimeCard.ID
WHERE startdatetime < > enddatetime AND Journal.ClassTypeID = 20051
  AND (StartDateTime BETWEEN @startdate AND @enddate 
       OR EndDateTime BETWEEN @startdate AND @enddate)
ORDER BY EmployeeName, StartDateTime DESC
  • Entered : 08/2010
  • Version : Control 4.3+
You could leave a comment if you were logged in.