I am querying two tables:tblPurhcaseOrders
- holds purchase orders with various columns, one of which is a foreign key called OrderTypeIDtblOrderTypes
- holds 4 types of purchase orders
One of the order types in tblOrderTypes was added later than the first 3 basic ones. Thus, instances of purchase orders with this newer order type appear only from a certain point of time (Oct. 2012). I'm writing an aggregated query which should ultimately bring me the sum of all purchase orders, grouped by their type. I do want the results to me 0 or NULL, even if I query a point of time in which the newer order type had not existed yet.
SELECT MONTH(tblPurchaseOrders.DeliveryDate) AS sMonth,
Year(tblPurchaseOrders.DeliveryDate) AS sYear,
SUM(ISNULL(TotalPrice,0)) AS sValue
FROM tblPurchaseOrders RIGHT OUTER JOIN
tblOrderTypes ON tblPurchaseOrders.OrderTypeID = tblOrderTypes.OrderTypeID
WHERE MONTH(DeliveryDate) = 1 AND
YEAR(DeliveryDate) = 2012
GROUP BY MONTH(DeliveryDate), Year(DeliveryDate), tblOrderType.OrderTypeName
Now, the query above works just fine and provides me with correct aggregation, only it leaves out the newer order type (reminder, it didn't exist in JAN-2012 yet).
If I change the query condition to
MONTH(DeliveryDate) = 2 AND YEAR(DeliveryDate) = 2012
it appears. I've tried full join and also tried switching between the order of the two tables and toggled to LEFT OUTER JOIN, no luck.