Fishbowl Query: All Product Inventory

SELECT
PART.NUM AS "Part Number",

COALESCE(
(
SELECT VP.LASTCOST
FROM VENDORPARTS VP
WHERE VP.PARTID = PART.ID
AND COALESCE(VP.LASTCOST, 0) <> 0
ORDER BY
VP.DEFAULTFLAG DESC,
VP.LASTDATE DESC,
VP.ID DESC
LIMIT 1
),
PART.STDCOST,
0
) AS "Cost",

PART.DATELASTMODIFIED AS "Date",

COALESCE(QTY.AVAILABLE, 0) AS "Available"

FROM
PART

LEFT JOIN (
SELECT
QIT.PARTID,
SUM(
COALESCE(QIT.QTYONHAND, 0)
- COALESCE(QIT.QTYALLOCATED, 0)
- COALESCE(QIT.QTYNOTAVAILABLE, 0)
+ COALESCE(QIT.QTYDROPSHIP, 0)
) AS AVAILABLE
FROM
QTYINVENTORYTOTALS QIT
GROUP BY
QIT.PARTID
) QTY
ON QTY.PARTID = PART.ID

WHERE
PART.TYPEID = 10

ORDER BY
PART.NUM;