/* Shows a list of all non asset tracked gear in your system. */
SELECT i.groupfld AS [Group],
g.group_descv6 AS [Group Description],
i.category AS [Category],
c.cat_descv6 AS [Category Desc],
i.product_code AS [Product],
i.descriptionv6 AS [Product Desc],
i.on_hand AS [Qty on Hand]
FROM tblinvmas i
LEFT OUTER JOIN tblgroup g
ON g.group_code = i.groupfld
LEFT OUTER JOIN tblcategory c
ON c.category_code = i.category
WHERE asset_track <> 'Y'
ORDER BY G.seqno,
I.seq_no
/* Shows a list of all non asset tracked gear in your system ordered by entry date. */
SELECT i.groupfld AS [Group],
g.group_descv6 AS [Group Description],
i.category AS [Category],
c.cat_descv6 AS [Category Desc],
i.product_code AS [Product],
i.descriptionv6 AS [Product Desc],
i.on_hand AS [Qty on Hand],
Format(i.entrydate, 'yyyy-MMM-dd') AS [Entry Date],
CASE
WHEN i.lastupdate = '1899-12-30' THEN ''
ELSE Format(i.lastupdate, 'yyyy-MMM-dd')
END AS [Last updated]
FROM tblinvmas i
LEFT OUTER JOIN tblgroup g
ON g.group_code = i.groupfld
LEFT OUTER JOIN tblcategory c
ON c.category_code = i.category
WHERE asset_track <> 'Y'
ORDER BY entrydate DESC,
[last updated] DESC