среда, 27 марта 2013 г.

Список дат (ShipDate), когда НЕ было отгрузки товара c номером 777 (ProductID)

-- Решение с помощью только соединений

DECLARE @PID AS INT = 777;

SELECT
    DISTINCT AllDates.ShipDate
FROM
    (
    SELECT
             CAST (OH.ShipDate AS DATE) AS ShipDate
     FROM AdventureWorks2012.Sales.SalesOrderHeader AS OH
    ) AS AllDates
         LEFT OUTER JOIN
    (
         SELECT
             CAST (OH.ShipDate AS DATE) AS ShipDate
         FROM AdventureWorks2012.Sales.SalesOrderHeader AS OH
             INNER JOIN
             AdventureWorks2012.Sales.SalesOrderDetail AS OD
         ON OD.SalesOrderID = OH.SalesOrderID
         WHERE ProductID = @PID
    ) AS PIDDates
ON AllDates.ShipDate = PIDDates.ShipDate
WHERE PIDDates.ShipDate IS NULL;