Managing inventory. Finding staff with 2 (OR more) of a certain device

All we need is an easy explanation of the problem, so here it is.

I work for the IT support desk of a particular organisation. Coding is something I don’t do professionally but am happy to broaden my skills a little. Any help here would be gladly appreaciated!!

We’ve recently had to dole out lots of new iPhones, as the older standard model couldn’t run the newest version of iOS (and therefore a certain app).

I look after one particular location, my colleagues tend to the other locations. I would like to see if we can find out which staff members who have not yet returned their original device. Dozens and dozens of tickets for replacements combined with most staff working from home means devices can be hard to track down using the front-end of the database.

The main columns are the "Employee_Name", "Device_Name" (e.g iPhone 5C), "Device_Serial" and "Status". The two states that a device can have for this particular column are either "With employee" or "With help desk"

Here is some SQL code use to create a sample table…

    CREATE TABLE IF NOT EXISTS helpdesk (
    `Employee_Name` VARCHAR(11) CHARACTER SET utf8,
    `Device_Name` VARCHAR(9) CHARACTER SET utf8,
    `Device_Serial` VARCHAR(9) CHARACTER SET utf8,
    `Status` VARCHAR(14) CHARACTER SET utf8
     );

    INSERT INTO helpdesk VALUES
    ('John Murphy','Apple iPhone SE','QWERTY123','With employee'),
    ('John Murphy','iPhone 8','TREWQ345','With employee'),
    ('IT Service','iPhone 6S','GHJKKJ45','With help desk'),
    ('Tammy Top','iPhone X','GFDSDC423','With employee'),
    ('Bugs Bunny','Brother Printer','TYU1267','With employee'),
    ('IT Service','iPhone XR','FDSASF456','With help desk');

I only want to get returns for employees that are currently in possession of TWO or more iPhones. Here "John Murphy" would need to be returned, but not "Tammy Top" (as she has only got one). Any entries with the employee name ‘IT Service’ can be left out – these items are already back with the IT desk.

Very glad of any help given!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

As you can see the WHERE clause removes all rows that are not valid for the purpose

In your case

  • Employee_Name can’t be ‘IT Service’
  • Device_Name has to be an ‘%IPHONE%’
  • Status has to be ‘With employee’

The Rest is a GROUP BY AND HAVING

    CREATE TABLE IF NOT EXISTS helpdesk (
    `Employee_Name` VARCHAR(11) CHARACTER SET utf8,
    `Device_Name` VARCHAR(20) CHARACTER SET utf8,
    `Device_Serial` VARCHAR(9) CHARACTER SET utf8,
    `Status` VARCHAR(14) CHARACTER SET utf8
     );
    INSERT INTO helpdesk VALUES
    ('John Murphy','Apple iPhone SE','QWERTY123','With employee'),
    ('John Murphy','iPhone 8','TREWQ345','With employee'),
    ('IT Service','iPhone 6S','GHJKKJ45','With help desk'),
    ('Tammy Top','iPhone X','GFDSDC423','With employee'),
    ('Bugs Bunny','Brother Printer','TYU1267','With employee'),
    ('IT Service','iPhone XR','FDSASF456','With help desk');
SELECT `Employee_Name` 
FROM helpdesk 
WHERE `Employee_Name`  <> 'IT Service'
    AND `Device_Name`  LIKE '%IPHONE%'
    AND `Status` = 'With employee'
GROUP BY `Employee_Name`
HAVING COUNT(*) > 1
| Employee_Name |
| :------------ |
| John Murphy   |

db<>fiddle here

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply