SQL Query Help

[Squalus12]

I am trying to get payroll data from our POS system SQL database over to our iSeries. I basically need to get the Employee name and Hours worked within a date range. Employee Names are stored in one table and their clock in and out times are stored in another table with "EmpID" being the link between the two tables. Here are the 2 tables that store the data:Employee TableEmpID | LastName | FirstName---------------------------------------------------------11 | Jones | SamTimeCard TableEmpID | CardDate | PunchType---------------------------------------------------------11 | 2011-02-15 23:14:44.730 | 1011 | 2011-02-15 23:15:44.730 | 12PunchType is '10' for clock in and '12' for clock out.I've been able to pull all the Sales Data from the POS System with no problem but this task has me stumped. Thanks in advance for any help.

[[dx]]

SELECT Employee.LastName, Employee.FirstName, TimeCard.PunchType (or whatever you need here)FROM Employee INNER JOIN TimeCard ON Employee.EmpID=TimeCard.EmpID

Try it.

[Squalus12]

The Query works but still does not give all the information I need. I added 'CardDate' and a Date condition to the query you provided:

$mDate = Date("Y-m-d"); //isset($_REQUEST["date1"]) ? $_REQUEST["date1"] : "";$query = "SELECT Employee.LastName, Employee.FirstName, TimeCard.PunchType, TimeCard.CardDateFROM Employee INNER JOIN TimeCardON Employee.EmpID=TimeCard.EmpIDWHERE CONVERT(char(10),CardDate,120) = '$mDate'";$result = mssql_query($query);while($row = mssql_fetch_array($result)){  echo "<b>Employee: </b>" .$row['LastName']. ", ".$row['FirstName']. " <b>Punch Date:</b> " .$row['CardDate'].  "<b> Punch Type: </b>" .$row['PunchType'];  echo "<br/>";}//Close the Connectionmssql_close($dbhandle);

Result: Employee: Jones, Sam Punch Date: Feb 16 2011 6:31AM Punch Type: 10 Employee: Jones, Sam Punch Date: Feb 16 2011 10:47AM Punch Type: 12I imagine that I will need some sort of subquery using datediff to get the hours worked from the two results.

[davej]

TimeCard TableEmpID | CardDate | PunchType---------------------------------------------------------11 | 2011-02-15 23:14:44.730 | 1011 | 2011-02-15 23:15:44.730 | 12PunchType is '10' for clock in and '12' for clock out.

Interesting problem. Is the type of database changing? What database(s) are you using? MySQL? SQL Server? Oracle? I don't know if you could extract the hours without writing a procedure that could use cursors.

[Squalus12]

What exactly do you mean by "changing"? The Database is SQL Server 2008. The end result I need is just "Last Name, First Name and Hours Worked". The end user will run a report (.php) at the end of the pay period which will run the query and display the hours worked for each employee during the selected date range and then write the records to a file on our AS400. If I can get the data, writing it to the 400 should be a piece of cake.Thanks.

[davej]

What exactly do you mean by "changing"? The Database is SQL Server 2008. The end result I need is just "Last Name, First Name and Hours Worked". The end user will run a report (.php) at the end of the pay period which will run the query and display the hours worked for each employee during the selected date range and then write the records to a file on our AS400. If I can get the data, writing it to the 400 should be a piece of cake. Thanks.

Oh, I thought you were moving the database over to a new system. I guess you are saying you want to get the result of the query over to the new system. How big are these tables? So far I have;SELECT E.EmpID, E.LastName, E.FirstName, T.CardDate, T.PunchTypeFROM EMPLOYEE AS E, TIMECARD AS TWHERE E.EmpID = T.EmpID ORDER BY T.CardDate

[Squalus12]

The System has only been in place for 2 weeks and we will only be keeping about 3 months worth of data in the POS(SQL) database since everything will be transmitted over to the iSeries (Sales Data(daily) and Employee Hours(bi-weekly). As of right now the TimeCard Table has 2600 rows and only occupies 0.148 MB of Data Space. The Employee table should never change that much unless an employee is added or deleted.I ran the query you provided, here is what it produced(names changed of course :-)):Employee: Jones, Sam Punch Date: Feb 16 2011 5:58AM Punch Type: 10Employee: Jonesey, Sam Punch Date: Feb 16 2011 6:03AM Punch Type: 10Employee: Jones, Sammy Punch Date: Feb 16 2011 6:03AM Punch Type: 10Employee: Jones, Samuel Punch Date: Feb 16 2011 6:09AM Punch Type: 10...Thanks for your help and let me know if there is any additional info about the database you need.Thanks again.

[davej]

The System has only been in place for 2 weeks and we will only be keeping about 3 months worth of data in the POS(SQL) database since everything will be transmitted over to the iSeries (Sales Data(daily) and Employee Hours(bi-weekly). As of right now the TimeCard Table has 2600 rows and only occupies 0.148 MB of Data Space. The Employee table should never change that much unless an employee is added or deleted.

I think the only way to deal with this in a reliable way would be to write a stored procedure. Otherwise, if you just use a complex query, you will risk getting a bogus result with no error or warning message. The stored procedure would use cursors and would match up the clock-in and clock-out times and make sure each conversion to hours was reasonable.

[Squalus12]

I was able to throw this together to pull a specific employees hours worked. Granted it relies on them punching in twice and out twice throughout the day. Please take into consideration my knowledge of PHP and SQL is just what I've taught myself over the years, be gentle :-) .

$query = "select a.EmpID, a.CardDate, a.PunchType, b.FirstName, b.LastNamefrom TimeCard as a joinEmployee as bon a.EmpID = b.EmpIDwhere CONVERT(char(10),a.CardDate,120) = '2011-02-14'AND a.EmpID = '58'AND PunchType = '10'";$query2 = "select a.EmpID, a.CardDate, a.PunchType, b.FirstName, b.LastNamefrom TimeCard as a joinEmployee as bon a.EmpID = b.EmpIDwhere CONVERT(char(10),a.CardDate,120) = '2011-02-14'AND a.EmpID = '58'AND PunchType = '12'";$result = mssql_query($query);$result2 = mssql_query($query2);$EmpFName = mssql_result($result,0,'FirstName');$EmpLName = mssql_result($result,0,'LastName');$EmpID = mssql_result($result,0,'EmpID');$ClockIn = strtotime(mssql_result($result,0,'CardDate'));$ClockOut = strtotime(mssql_result($result2,0,'CardDate'));$ClockIn2 = strtotime(mssql_result($result,1,'CardDate'));$ClockOut2 = strtotime(mssql_result($result2,1,'CardDate'));$TotalHours = number_format(($ClockOut - $ClockIn)/60,2);$TotalHours2 = number_format(($ClockOut2 - $ClockIn2)/60,2);$TotalHoursF = number_format($TotalHours/60,2);$TotalHours2F = number_format($TotalHours2/60,2);echo "<b>Name:</b> " .$EmpFName. " " .$EmpLName. "";echo "<br/>";echo "<b>Sub Hours:</b> " .$TotalHoursF. "";echo "<br/>";echo "<b>Sub Hours:</b> " .$TotalHours2F. "";echo "<br/>";echo "<b>Total Hours:</b> " .(number_format($TotalHoursF + $TotalHours2F,2)). "";

Produces:Name: Sam JonesSub Hours: 5.12Sub Hours: 1.38Total Hours: 6.50

[Squalus12]

Made some progress this morning. Here is the Query I used:

$mDate = Date("Y-m-d"); //isset($_REQUEST["date1"]) ? $_REQUEST["date1"] : "";$query = "SELECT E.EmpID, E.LastName, E.FirstName, T.CardDate, T.PunchType, DATEDIFF(minute,T.CardDate,U.CardDate) as HoursFROM EMPLOYEE AS E, TIMECARD AS T, TIMECARD as UWHERE E.EmpID = T.EmpIDAND T.PunchType = '10'AND U.PunchType = '12'AND T.EmpID = U.EmpIDAND CONVERT(char(10),T.CardDate,120) = '$mDate'AND CONVERT(char(10),U.CardDate,120) = '$mDate' ORDER BY E.LastName";$result = mssql_query($query);while($row = mssql_fetch_array($result)){  $HoursWrk = $row['Hours'];  $HoursWrkF = number_format($HoursWrk/60,2);  echo "<b>Employee: </b>" .$row['LastName']. ", ".$row['FirstName']. " <b>Employee ID:</b> " .$row['EmpID'].  "<b> Hours: </b>" .$HoursWrkF;  echo "<br/>";}

Which gave me these results:Employee: Jones, Sam Employee ID: 56 Hours: 4.25Employee: Jones, Sammy Employee ID: 64 Hours: 2.83Employee: Jones, Samuel Employee ID: 73 Hours: 5.23Employee: Jones, Sampson Employee ID: 85 Hours: 4.95Employee: Jones, Nancy Employee ID: 115 Hours: 2.10Employee: Jones, Bob Employee ID: 152 Hours: 5.10As far as I can tell it is yielding what I need. My only concern is using number_format, not sure but doesn't it always round? Also, if an employee punches in and out more than once in a day, how would I go about adding their hours together for that day?Thanks!Edit: Well, this almost worked. I ran the query using yesterdays date and got some weird results. I think it is due to the fact that are multiple punch in and out times for each employee and it must be doing a calculation for each one (ex. diff between punch time #1 and punch time #2, punch time #1 and punch time #4, etc.)Results:Employee: Abraham, Annie Employee ID: 51 Hours: 3.50Employee: Abraham, Annie Employee ID: 51 Hours: 8.80Employee: Abraham, Annie Employee ID: 51 Hours: -1.00Employee: Abraham, Annie Employee ID: 51 Hours: 4.30

[Squalus12]

Well I was able to get the query to not compare all of the CardDate entries by adding "AND T.CardDate < U.CardDate". But it still does a DATEDIFF on the very first Punch In and very Last Punch Out, example:Employee: Abraham, Annie Employee ID: 51 Hours: 3.50Employee: Abraham, Annie Employee ID: 51 Hours: 8.80Employee: Abraham, Annie Employee ID: 51 Hours: 4.30Its basically giving the differnence between #1 and #2, #1 and #4, then #3 and #4

[davej]

Its basically giving the differnence between #1 and #2, #1 and #4, then #3 and #4

Can you solve this with MIN( ) ?

[Squalus12]

Not sure, any ideas on where I would use MIN() in this scenario?FYI, I pulled in the CardDate fields from each line that are being compared to better explain what it's doing currently:Employee: Abraham, Annie Employee ID: 51 Hours: 3.50 Punch Times Compared: Feb 17 2011 10:35AM And Feb 17 2011 2:05PMEmployee: Abraham, Annie Employee ID: 51 Hours: 8.80 Punch Times Compared: Feb 17 2011 10:35AM And Feb 17 2011 7:23PM <---Comparison that needs to be ignoredEmployee: Abraham, Annie Employee ID: 51 Hours: 4.30 Punch Times Compared: Feb 17 2011 3:05PM And Feb 17 2011 7:23PMHere you can see where an employee has punched in 3 times and punched out 3 times with multiple invalid DATEDIFF calculations:Employee: Bostick, Antoine Employee ID: 60 Hours: 2.82 Punch Times Compared: Feb 17 2011 3:04PM And Feb 17 2011 5:53PMEmployee: Bostick, Antoine Employee ID: 60 Hours: 5.67 Punch Times Compared: Feb 17 2011 3:04PM And Feb 17 2011 8:44PM <---Comparison that needs to be ignoredEmployee: Bostick, Antoine Employee ID: 60 Hours: 2.12 Punch Times Compared: Feb 17 2011 6:37PM And Feb 17 2011 8:44PMEmployee: Bostick, Antoine Employee ID: 60 Hours: 3.08 Punch Times Compared: Feb 17 2011 11:00AM And Feb 17 2011 2:05PMEmployee: Bostick, Antoine Employee ID: 60 Hours: 6.88 Punch Times Compared: Feb 17 2011 11:00AM And Feb 17 2011 5:53PM <---Comparison that needs to be ignoredEmployee: Bostick, Antoine Employee ID: 60 Hours: 9.73 Punch Times Compared: Feb 17 2011 11:00AM And Feb 17 2011 8:44PM <---Comparison that needs to be ignored

[davej]

I just don't trust SQL for this. I know people write extremely complex SQL queries and trust them, but I don't. Since you have PHP I would wonder if you couldn't just load the data into two arrays and then process it in a logical top-down fashion. It would eliminate all the crazy repeating that the join does. Plus you could detect errors such as missing clock-in or clock-outs or absurdly long clocked-in periods.One array would get the clock-in data for the week;SELECT EmpID, CardDate AS INTIMEFROM TIMECARDWHERE PunchType = 10AND CONVERT(char(10),CardDate,120) >= '$mDate_Monday'AND CONVERT(char(10),CardDate,120) <= '$mDate_Sunday'ORDER BY EmpID, CardDate;The other array would get the clock-out data for the week;SELECT EmpID, CardDate AS OUTTIMEFROM TIMECARDWHERE PunchType = 12AND CONVERT(char(10),CardDate,120) >= '$mDate_Monday'AND CONVERT(char(10),CardDate,120) <= '$mDate_Sunday'ORDER BY EmpID, CardDate;And then a final array would get the EmpID and names.EDIT-Alternately you could load the arrays for just one employee at a time and process the arrays that way.I've tried more SQL with no success. Obviously one solution would be to build a new table that pairs up each ClockIn with the minimum positive Clockout for that EmpID i.e. MIN(DATEDIFF(minute, I.CardDate, O.CardDate) WHERE I.CardDate < O.CardDateBut SQL won't do this. Or maybe it would be better to just load one array.

[davej]

This may be getting closer, but obviously it does not test for invalid times like the PHP code can.CREATE VIEW V_WEEKHOURS ASSELECT I.EmpID, CONVERT(char(10),I.CardDate,120) AS [Date],MIN(DATEDIFF(minute,I.CardDate,O.CardDate))/60.0 AS [Hours]FROM TIMECARD AS I, TIMECARD as OWHERE I.EmpID = O.EmpIDAND I.PunchType = 'I'AND O.PunchType = 'O'AND I.CardDate < O.CardDateGROUP BY I.EmpID, I.CardDate;SELECT *FROM V_WEEKHOURS;SELECT [DATE], EmpID, SUM([Hours])FROM V_WEEKHOURSGROUP BY [Date],EmpIDORDER BY [EmpID];Anyone wishing to experiment with this problem can use my mockup;-- Create a database TIMEKEEPINGCREATE TABLE EMPLOYEE (EmpID Integer PRIMARY KEY,LastName Char(25),FirstName Char(25));INSERT INTO EMPLOYEE (EmpID, LastName, FirstName)Values(11, 'Jones', 'Sam');INSERT INTO EMPLOYEE (EmpID, LastName, FirstName)Values(23, 'Smythe', 'Frank');CREATE TABLE TIMECARD (ID Integer IDENTITY(1,1) PRIMARY KEY,EmpID Integer,CardDate DateTime,PunchType CHAR(1));INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(11,'2011-01-15 03:14:44.330','I');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(11,'2011-01-15 05:14:14.880','O');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(11,'2011-01-16 14:51:34.750','I');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(11,'2011-01-16 23:35:22.200','O');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(23,'2011-01-17 03:14:54.190','I');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(23,'2011-01-17 07:44:21.700','O');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(23,'2011-01-19 05:50:00.420','I');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(23,'2011-01-19 12:45:04.230','O');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(11,'2011-01-20 12:45:04.230','I');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(11,'2011-01-20 13:15:04.200','O');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(11,'2011-01-20 15:45:04.200','I');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(23,'2011-01-20 15:46:15.200','I');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(11,'2011-01-20 17:22:44.200','O');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(23,'2011-01-20 17:22:47.200','O');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(23,'2011-01-20 18:00:00.000','I');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(23,'2011-01-20 20:08:00.000','O');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(23,'2011-01-21 06:55:05.000','I');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(11,'2011-01-21 06:56:05.000','I');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(23,'2011-01-21 08:05:01.000','O');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(23,'2011-01-21 08:35:30.000','I');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(23,'2011-01-21 09:05:45.000','O');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(23,'2011-01-21 11:05:45.000','I');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(11,'2011-01-21 11:05:48.000','O');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(23,'2011-01-21 13:30:25.000','O');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(23,'2011-01-21 13:35:15.000','I');INSERT INTO TIMECARD (EmpID, CardDate, PunchType)VALUES(23,'2011-01-21 13:55:50.000','O');

[Squalus12]

Thanks for your help on this davej. I had to shelf this project for a few days but I hope to get back to it before weeks end. I look forward to trying out your latest idea.Thanks again