Using a Coalesce function

[civilwarjunky]

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

 

[ceill44]

Perhaps if you showed us the table structure with column name and attributes then we could help you. For example you have inventory and kit tables so more details about what your working with would help.

Anyway.
I would look at the update syntax to update your numbers after your conditions have been meet.

I look forward to seeing more details.

 

[civilwarjunky]

H01 H14 1.0
H01 H104 1.0
H01 H174 1.0
H01 S15 1.0
H01 H17 0.0
this is the resaults of the component querry.
Kit Ho1 has the above items in it.

My Inventory querry tells us how much available inventory on hand. I am needing to create an inventory check that when H01 entered it checks each one of theses items against available inventory then sub track it from avaible inventory. The Table structure is huge for I am dealing with an accounting software with over 345 tables. Here are the attributes for the component table

CREATE TABLE [Component] (
[BomUsage] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_BomUsage] DEFAULT (' '),
[CmpnentID] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_CmpnentID] DEFAULT (' '),
[CmpnentQty] [float] NOT NULL CONSTRAINT [DF_Component_CmpnentQty] DEFAULT (0),
[CpnyID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_CpnyID] DEFAULT (' '),
[Crtd_DateTime] [smalldatetime] NOT NULL CONSTRAINT [DF_Component_Crtd_DateTime] DEFAULT (rtrim(convert(varchar(30),convert(smalldatetime,getdate())))),
[Crtd_Prog] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_Crtd_Prog] DEFAULT (' '),
[Crtd_User] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_Crtd_User] DEFAULT (' '),
[Deviation] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_Deviation] DEFAULT (' '),
[EngrChgOrder] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_EngrChgOrder] DEFAULT (' '),
[ExplodeFlg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_ExplodeFlg] DEFAULT (' '),
[KitID] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_KitID] DEFAULT (' '),
[KitSiteID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_KitSiteID] DEFAULT (' '),
[KitStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_KitStatus] DEFAULT (' '),
[LineID] [int] NOT NULL CONSTRAINT [DF_Component_LineID] DEFAULT (0),
[LineNbr] [smallint] NOT NULL CONSTRAINT [DF_Component_LineNbr] DEFAULT (0),
[LineRef] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_LineRef] DEFAULT (' '),
[LUpd_DateTime] [smalldatetime] NOT NULL CONSTRAINT [DF_Component_LUpd_DateTime] DEFAULT ('01/01/1900'),
[LUpd_Prog] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_LUpd_Prog] DEFAULT (' '),
[LUpd_User] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_LUpd_User] DEFAULT (' '),
[NoteID] [int] NOT NULL CONSTRAINT [DF_Component_NoteID] DEFAULT (0),
[RtgStep] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_RtgStep] DEFAULT (' '),
[S4Future01] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_S4Future01] DEFAULT (' '),
[S4Future02] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_S4Future02] DEFAULT (' '),
[S4Future03] [float] NOT NULL CONSTRAINT [DF_Component_S4Future03] DEFAULT (0),
[S4Future04] [float] NOT NULL CONSTRAINT [DF_Component_S4Future04] DEFAULT (0),
[S4Future05] [float] NOT NULL CONSTRAINT [DF_Component_S4Future05] DEFAULT (0),
[S4Future06] [float] NOT NULL CONSTRAINT [DF_Component_S4Future06] DEFAULT (0),
[S4Future07] [smalldatetime] NOT NULL CONSTRAINT [DF_Component_S4Future07] DEFAULT ('01/01/1900'),
[S4Future08] [smalldatetime] NOT NULL CONSTRAINT [DF_Component_S4Future08] DEFAULT ('01/01/1900'),
[S4Future09] [int] NOT NULL CONSTRAINT [DF_Component_S4Future09] DEFAULT (0),
[S4Future10] [int] NOT NULL CONSTRAINT [DF_Component_S4Future10] DEFAULT (0),
[S4Future11] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_S4Future11] DEFAULT (' '),
[S4Future12] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_S4Future12] DEFAULT (' '),
[ScrapPct] [float] NOT NULL CONSTRAINT [DF_Component_ScrapPct] DEFAULT (0),
[Sequence] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_Sequence] DEFAULT (' '),
[SiteID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_SiteID] DEFAULT (' '),
[StartDate] [smalldatetime] NOT NULL CONSTRAINT [DF_Component_StartDate] DEFAULT ('01/01/1900'),
[Status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_Status] DEFAULT (' '),
[StdQty] [float] NOT NULL CONSTRAINT [DF_Component_StdQty] DEFAULT (0),
[StockUsage] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_StockUsage] DEFAULT (' '),
[StopDate] [smalldatetime] NOT NULL CONSTRAINT [DF_Component_StopDate] DEFAULT ('01/01/1900'),
[SubKitStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_SubKitStatus] DEFAULT (' '),
[SupersededBy] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_SupersededBy] DEFAULT (' '),
[Supersedes] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_Supersedes] DEFAULT (' '),
[User1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_User1] DEFAULT (' '),
[User2] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_User2] DEFAULT (' '),
[User3] [float] NOT NULL CONSTRAINT [DF_Component_User3] DEFAULT (0),
[User4] [float] NOT NULL CONSTRAINT [DF_Component_User4] DEFAULT (0),
[User5] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_User5] DEFAULT (' '),
[User6] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_User6] DEFAULT (' '),
[User7] [smalldatetime] NOT NULL CONSTRAINT [DF_Component_User7] DEFAULT ('01/01/1900'),
[User8] [smalldatetime] NOT NULL CONSTRAINT [DF_Component_User8] DEFAULT ('01/01/1900'),
[UTEFlag] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_UTEFlag] DEFAULT (' '),
[WONbr] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Component_WONbr] DEFAULT (' '),
[tstamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO