Jump to content


  • Content Count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About Squalus12

  • Rank
  • Birthday 10/06/1974

Profile Information

  • Location
    North Carolina
  1. TryFROM Guestbook instead of FROM `Guestbook`
  2. You could always use a Flash file that loops every 10 seconds or so and pulls it's data from a .php file that is counting the number of rows. I am using something similar for a Point of Sale Dashboard that calculates Net Sales, Customer Count, etc. in real time. Let me know and I can throw together a quick example.
  3. Squalus12

    SQL Query Help

    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
  4. Squalus12

    SQL Query Help

    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
  5. Squalus12

    SQL Query Help

    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
  6. Squalus12

    SQL Query Help

    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 = $r
  7. Squalus12


    Are you getting any errors when you run this?
  8. Squalus12

    SQL Query Help

    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 join
  9. Squalus12

    SQL Query Help

    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: F
  10. Squalus12

    SQL Query Help

    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.
  11. Squalus12

    SQL Query Help

    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:<
  12. Squalus12

    SQL Query Help

    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-------------------------------
  • Create New...