CODEWITHSUNDEEP
sql-server
Sisi
Sisi

Reputation: 111

SQL query with Northwind database

I have a little problem with some query. This is the task:

Create a query that displays the employees with no sale in the last 3 months to customers who are from "USA".

This is what i wrote: 

    Select emp.EmployeeID, (emp.FirstName + ' ' + emp.LastName) AS Name
From Employees AS emp
    Join Orders AS o ON emp.EmployeeID = o.EmployeeID
    Join Customers AS c ON o.CustomerID = c.CustomerID
Where c.Country LIKE 'USA';

One of the problem is that i don't know where to put this select query (it's for calculating the last 3 months but i'm not sure that this is true):

Select DATEDIFF(MM, '1998-02-01', '1998-05-31') From Orders

The second problem is that i don't have an idea for the part "employees with no sale" How can i find this?

Should i use other kind of joins or something else?

Sorry for my question but i'm new in SQL and i'll appreciate any kind of help.

If you have any questions, please ask. :)

Upvotes: 0

Views: 4930

Answers (4)

Beth
Beth

Reputation: 9617

If you're just learning SQL, please don't take one of these answers and use it to do your homework. You'll need to figure out what's going on for yourself.

Try to take this task in pieces. First, let's look at 'customers from USA.' You can probably write this one yourself:

Select * from customers where country = 'usa'

Next, consider how to find the orders for those customers (all of them, for now).

Since you've posted an example of an inner join, I'm going to assume you could write that one yourself, too:

Select o.* from customers c inner join orders o on
c.customerID = o.customerID
where country = 'usa'

Now you've asked where/how to apply the criteria for 'sales in the last 3 months.' Note each order has an orderDate, representing the date the order was placed. You need to use the order table's orderdate field with today's date and compare the number of months between them. The getdate() function returns the date and time from the server. Try executing:

select getdate()

then you can experiment with the datediff() function on the orderDates in the orders table:

Select orderid, getdate(), orderdate, datediff(mm,orderdate,getdate())
from orders

I think you'll have better luck, though, adding 3 months to orderdate and comparing with getdate():

Select orderid, orderdate, dateadd(month, orderdate, 3), 
getdate(), dateadd(month, getdate(), -3)
    from orders

Now you can see all the employee IDs you don't want, the ones that have orders within the last 3 months, and you already know how to limit orders by customers in USA. Those are the IDs you want to exclude from the employees table. You can do that in a couple of different ways, depending on what you've learned so far, but typically you're exposed to LEFT OUTER JOINs for this sort of thing. You'll want to left outer join your employee table with the set of IDs you don't want (the ones with orders in the past 3 months) on the employeeID field, and return rows where the employeeID from the order subquery is null.

Try:

select count(*) from employees  -- note rowcount

then:

select min(employeeID) from orders  -- pick one employee

then:

select count(e.employeeID) 
from employees e left outer join 
  (select * from orders
   where employeeID in (select min(employeeID) from orders) 
  ) o on e.employeeID = o.employeeID 
where o.employeeID is null

this count should be one less than the total number of rows in your employees table, it should exclude the lowest employeeID with an order.

Then see if you can figure out how to do your homework.

Upvotes: 1

Kevin
Kevin

Reputation: 473

Left/Right joins exist as well and you can somewhat think of them as Venn diagrams (Join or Inner Join is the intersect, Left Join is intersect and left circle, etc.). Its one perspective on how to think what will be returned. Fields returned which are not in the intersect like with a Left Join will be NULL.

The below is another way to do it:

Select emp.EmployeeID, (emp.FirstName + ' ' + emp.LastName) AS Name
From Employees AS emp
    Left Join Orders As o ON emp.EmployeeID = o.EmployeeID
        And o.OrderDate > DateAdd(month,-3,GetDate())
    Join Customers AS c ON o.CustomerID = c.CustomerID
Where c.Country = 'USA' And o.EmployeeID is Null
Group By emp.EmployeeID, emp.FirstName, emp.LastName;

Upvotes: 0

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

How you actually treat "last 3 months" depends on you, but this is one way to approach the task:

select e.*
from employees e
where not exists (
  select 1
  from orders o
  join customers c on c.customerid = o.customerid
  where e.employeeid = o.employeeid
    and c.country = 'USA'
    and c.orderdate > dateadd(month, datediff(month, 0, getdate()), 3)
  );

Last three months could mean months per se or just days that make exactly last three months (eg. 91)

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146541

Create a query that displays the employees with no sale in the last 3 months to customers who are from "USA"

Select * From Employees e
Where not exists
  (Select * from orders o join customers c 
      on c.CustomerID = o.CustomerID
   Where c.Country = "USA"
      and o.saleDate >= DateAdd(month, -3, getdate()))

Upvotes: 2

Related Questions