civilwarjunky
IS-IT--Management
I am working on an inventory project, and I am new to SQL.
I have completed 2 querries, one pulls available inventory and the second one pull kits, and the items required to build them. I am trying to come up with an inventory check querry, so that when a kit id such as H01 called it will check the items required to make the kite and check against available inventory. Then subtract those items from the available inventory. I was told that a coalesce querry is what I need. I have never done one before.
Here are the two querries I have to work with, can you help or point me in the right direction
The first querry is available inventory:
SELECT
Inventory.InvtID,
ItemSite.QtyAvail
FROM
Inventory Inventory LEFT OUTER JOIN Kit Kit ON
Inventory.InvtID = Kit.KitID
LEFT OUTER JOIN ItemSite ItemSite ON
Inventory.InvtID = ItemSite.InvtID
LEFT OUTER JOIN Site Site ON
ItemSite.CpnyID = Site.CpnyID AND
ItemSite.SiteID = Site.SiteId
LEFT OUTER JOIN Snote Snote ON
ItemSite.NoteID = Snote.nID
LEFT OUTER JOIN ItemHist ItemHist ON
ItemSite.InvtID = ItemHist.InvtID AND
ItemSite.SiteID = ItemHist.SiteID
ORDER BY
Inventory.InvtId
compute sum(ItemSite.QtyAvail) by Inventory.InvtID
compute sum(ItemSite.QtyAvail)
The second querry pulls kits and the items required to build them.
SELECT
Inventory.InvtID,
ItemSite.QtyAvail
FROM
Inventory Inventory LEFT OUTER JOIN Kit Kit ON
Inventory.InvtID = Kit.KitID
LEFT OUTER JOIN ItemSite ItemSite ON
Inventory.InvtID = ItemSite.InvtID
LEFT OUTER JOIN Site Site ON
ItemSite.CpnyID = Site.CpnyID AND
ItemSite.SiteID = Site.SiteId
LEFT OUTER JOIN Snote Snote ON
ItemSite.NoteID = Snote.nID
LEFT OUTER JOIN ItemHist ItemHist ON
ItemSite.InvtID = ItemHist.InvtID AND
ItemSite.SiteID = ItemHist.SiteID
ORDER BY
Inventory.InvtId
compute sum(ItemSite.QtyAvail) by Inventory.InvtID
compute sum(ItemSite.QtyAvail)
Thanks
I have completed 2 querries, one pulls available inventory and the second one pull kits, and the items required to build them. I am trying to come up with an inventory check querry, so that when a kit id such as H01 called it will check the items required to make the kite and check against available inventory. Then subtract those items from the available inventory. I was told that a coalesce querry is what I need. I have never done one before.
Here are the two querries I have to work with, can you help or point me in the right direction
The first querry is available inventory:
SELECT
Inventory.InvtID,
ItemSite.QtyAvail
FROM
Inventory Inventory LEFT OUTER JOIN Kit Kit ON
Inventory.InvtID = Kit.KitID
LEFT OUTER JOIN ItemSite ItemSite ON
Inventory.InvtID = ItemSite.InvtID
LEFT OUTER JOIN Site Site ON
ItemSite.CpnyID = Site.CpnyID AND
ItemSite.SiteID = Site.SiteId
LEFT OUTER JOIN Snote Snote ON
ItemSite.NoteID = Snote.nID
LEFT OUTER JOIN ItemHist ItemHist ON
ItemSite.InvtID = ItemHist.InvtID AND
ItemSite.SiteID = ItemHist.SiteID
ORDER BY
Inventory.InvtId
compute sum(ItemSite.QtyAvail) by Inventory.InvtID
compute sum(ItemSite.QtyAvail)
The second querry pulls kits and the items required to build them.
SELECT
Inventory.InvtID,
ItemSite.QtyAvail
FROM
Inventory Inventory LEFT OUTER JOIN Kit Kit ON
Inventory.InvtID = Kit.KitID
LEFT OUTER JOIN ItemSite ItemSite ON
Inventory.InvtID = ItemSite.InvtID
LEFT OUTER JOIN Site Site ON
ItemSite.CpnyID = Site.CpnyID AND
ItemSite.SiteID = Site.SiteId
LEFT OUTER JOIN Snote Snote ON
ItemSite.NoteID = Snote.nID
LEFT OUTER JOIN ItemHist ItemHist ON
ItemSite.InvtID = ItemHist.InvtID AND
ItemSite.SiteID = ItemHist.SiteID
ORDER BY
Inventory.InvtId
compute sum(ItemSite.QtyAvail) by Inventory.InvtID
compute sum(ItemSite.QtyAvail)
Thanks