Home
Join
check
  • You are describing a situation where you 2 tables containing the 1:N (one to many).  The bottom query will also work except use the JOIN command and list both table names by the index and/or common element.

     

    If you have all the information in a single table, then something like will work:

     

    SELECT TOP 100 emp.driverCode AS driverCode, count([driverCode]) AS occurs, sum(emp.driverJobs) AS driverJobs
    FROM EMPLOYEE  AS emp
    WHERE emp.driverDate>'20091010'
    GROUP BY emp.driverCode, emp.driverJobs
    ORDER BY [driverJobs], [driverCode];

    ========================================

    This will return the top 100 drivers based on what you use as the field name (I used driverCode).  The count is used for the top 100 drivers and sum will total on the second field name.

    EMPLOYEE is the table name and emp is variable.

     


     

    Was this post helpful? thumb_up thumb_down
  • View Best Answer in replies below

    7 Replies

    • There are not enough details here to provide real help but what you need to do is...

      SELECT * FROM .....no details provided.... WHERE ....no details provided.... = 'todays date'

      Was this post helpful? thumb_up thumb_down
    •  

       

      I have a table in that employee listed multiple times (multiple rows for each person in the table).  I
      wants to pick the most recent record for each person - pick one record per person - the most recent for example

       

      I would expect the query to return:
      101 4/5/2009 1 John Jones 12.34
      120 4/3/2009 0 Suzy Smith 12.34
      121 4/4/2009 0 Wily Coyote 10.45

       

      Was this post helpful? thumb_up thumb_down
    • Oh, that is a lot more complicated.

      Was this post helpful? thumb_up thumb_down
    • i have table consist of thousands of records of drivers trips. so every day i need to know that if i have 100 drives how many drivers i utilise today. so i want to get the last trip date of every driver to rotate when all. my table consist of Driver name, Mission, Driver ID, Date in, time in, date out, time out, status(in or out). another table Drives of Contract consist of drives on contract details, Driver name, Driver ID etc.

       

      Was this post helpful? thumb_up thumb_down
    • is there any way to figure out this.

      Was this post helpful? thumb_up thumb_down
    • I believe that your query should be along these lines:

      SELECT * FROM table WHERE date=
        (SELECT MAX(date) FROM table)

      This will work for a single record. You will then need to make it work for each user.

      Was this post helpful? thumb_up thumb_down
    • You are describing a situation where you 2 tables containing the 1:N (one to many).  The bottom query will also work except use the JOIN command and list both table names by the index and/or common element.

       

      If you have all the information in a single table, then something like will work:

       

      SELECT TOP 100 emp.driverCode AS driverCode, count([driverCode]) AS occurs, sum(emp.driverJobs) AS driverJobs
      FROM EMPLOYEE  AS emp
      WHERE emp.driverDate>'20091010'
      GROUP BY emp.driverCode, emp.driverJobs
      ORDER BY [driverJobs], [driverCode];

      ========================================

      This will return the top 100 drivers based on what you use as the field name (I used driverCode).  The count is used for the top 100 drivers and sum will total on the second field name.

      EMPLOYEE is the table name and emp is variable.

       


       

      Was this post helpful? thumb_up thumb_down

    Read these next...

    • Snap! Maggie malware, Bring Your Own Driver, Win11 remote desktop issues, & more

      Snap! Maggie malware, Bring Your Own Driver, Win11 remote desktop issues, & more

      Spiceworks Originals

      Your daily dose of tech news, in brief. Welcome to Thursday, October 6, 2022. If we roll back the calendar 39 years to 1983, today is the day that it went public after recording revenues of $12.8 million for the previous 12 months. It was over a de...

    • What does your IT team use for password management?

      What does your IT team use for password management?

      Security

      I use BitWarden for my own personal password management and it's fantastic. In the past, I've used a handful of different password managers in the workplace, including KeePass v2​, Secret Server​, LastPass​, and even just *cough* Excel.... 🥸Currently, we'...

    • Upgrade Exchange 2013 to Exchange 2019

      Upgrade Exchange 2013 to Exchange 2019

      Collaboration

      As the  Exchange 2013 is going to be end of life in April 2023, we will be upgrading / migrating our current setup to Exchange 2019.We are currently using MS Exchange 2013 Standard CU23 with Latest SU. We have 2 CAS servers in NLB and 4 Mailbox servers in...

    • Spark! Pro series - 6th October 2022

      Spark! Pro series - 6th October 2022

      Spiceworks Originals

      Today in History: 1866 -  The Reno brothers carry out the first train robbery in U.S. history On October 6, 1866, the brothers John and Simeon Reno stage the first train robbery in American history, making off with $13,000 from an Ohio and Mississ...

    • IT Site -Network Survey Tools

      IT Site -Network Survey Tools

      Software

      Hello,Need advice on any free forms or software to use to assist in doing IT site surveys of small business 50 users max with 4 different locations.Any not so expensive software that you have used let me know. We will be going onsite to each site first ti...