cheer up I have 2 tables - Orders , Customers in Orders have columns RequiredDate, ShippedDate in Customers ContactName column

I need to know the names of those whose orders were sent after RequiredDate , during 1997. What did I get: Select distinct contactname,orderdate from customers left join orders on year(orderdate) = 1997 how to find out whose were sent after RequiredDate ?

will it be right after .... on year(ordardate) = "1997" add where requireddate < shippeddate ?

if so, how to additionally output the total number of orders shipped at the wrong time for each customer

  • How are these two tables related? What field? - MaxU
  • @maxu customerID - DoneBass

1 answer 1

Something like this:

 select c.ContactName, count(*) as overdue_orders_no from Customers c join Orders o on o.CustomerID = c.CustomerID where o.OrderDate >= cast('1997-01-01' as datetime) and o.OrderDate < cast('1998-01-01' as datetime) and o.ShippedDate > o.RequiredDate; group by c.ContactName;

PS I don’t have access to SQL Server, so I couldn’t test this code