SQL find the date on have not recived any order

Question

I have some tables. I just want to write query which gives me the date on which no orders were placed. I am using left joins to get the result in null but problem is: after using this I am not able to get the date.

Please help me, my code is like this:

SELECT DAY(o.OrderDate) AS 'HavntRecivedOrder'
  FROM Products p
 INNER JOIN Suppliers sup   ON p.SupplierID = sup.SupplierID
 INNER JOIN OrderDetails od ON p.ProductID = od.ProductID
  LEFT JOIN Orders o        ON (od.OrderID = o.OrderID
                                AND o.OrderDate BETWEEN '1/1/1997' AND '1/31/1997')
 WHERE sup.CompanyName='Tokyo Traders'
   AND o.OrderDate IS  NULL

Answer 1

Please post the needed tables structures to get a better understanding of the problem.

Nevertheless from your question description I understand you want days with no orders posted, correct? Then I do not see why are you writing such complicated query. Maybe is just enough to do this...edited...

In case your Orders table has a SupplierID column then

DECLARE @firstdate DATETIME='19970101 00:00:00', 
        @lastdate DATETIME= '19970131 00:00:00'
DECLARE @datetable TABLE (auxDate DATE)

;WITH CTE_DatesTable
  AS
  (
    SELECT @FirstDate AS auxDate
    UNION ALL
    SELECT DATEADD(dd, 1, auxDate)
    FROM CTE_DatesTable
    WHERE DATEADD(dd, 1, auxDate) <= @LastDate
  )
  INSERT INTO @datetable ([auxDate])
  SELECT [auxDate] FROM CTE_DatesTable
  OPTION (MAXRECURSION 0);

SELECT auxDate AS 'HavntRecivedOrder'
      FROM @datetable auxTB
     WHERE DAY(auxTB.auxDate) NOT IN (SELECT DAY(OrderDate)
                                   FROM Orders, Supplier
                                  WHERE OrderDate = BETWEEN @firstdate AND @lastdate
                                    AND Supplier.SupplierID = Orders.SupplierID)

Answer 2

Logic behind the solution:

Get the dates in January 1997 on which Tokyo Traders received order and then find the dates in January,1997 on which orders are placed with excluding the dates on which Tokyo Traders received.

Here is solution:

select distinct day(OrderDate) 
as 'The days in January,1997 on which Tokyo Traders havent received any orders' 
from Orders  
where OrderDate between '1997-01-01' and '1997-01-31' 
and OrderDate not in  
(select ord.OrderDate from Orders ord 
join OrderDetails ode on ode.OrderID=ord.OrderID 
join Products prod on prod.ProductID=ode.ProductID 
join Suppliers sup on sup.SupplierID=prod.SupplierID 
where ord.OrderDate between '1997-01-01' and '1997-01-31' 
and sup.CompanyName like 'Tokyo Traders')