In the following Inventory/Order system:

- Items are Ordered/Invoiced (Stored in tblInvoice and tblInvoiceItem)
- Items are then Shipped (Stored in tblShipment and tblShipmentItem)

This allows for items in Inventory to be "Allocated":


- Stock levels are maintained in each warehouse ( 5 in warehouse A, 10
in warehouse B etc.)

- Stock level is determined by the following calculation:
LastInventoryAdjustmentQuantity + (everything that has arrived and
shipped since the last InventoryAdjustment)



THE QUESTION:
How can I write a query to return a result set of InventoryItems that
shows for each warehouse the current onhand stock?

Here is the full query that is currently being used:
(This query will not run agaisnt the provided DB code but demonstrate
what I am trying to achieve)

select
	distinct i.InventoryItemID,
	stock.InventoryItemAdjustmentID,
	w.WarehouseID 'WarehouseID',
	w.Name 'WarehouseName',
	stock.CreatedDate as 'AdjustmentDate',
	coalesce(stock.ActualOnHand,0) as 'AdjustmentQuantity',
	(select COALESCE(sum(tblInvoiceItem.Quantity),0)
		from dbo.tblInvoiceItem
		inner join dbo.tblInvoice on 
			tblInvoice.InvoiceID = tblInvoiceItem.InvoiceID
		where 
			tblInvoice.InvoiceTypeID = 1
		and 
			tblInvoiceItem.InventoryItemID = i.InventoryItemID
		and 
			tblInvoiceItem.Brokered = 0
		and
			tblInvoice.InvoiceStatusID in (2,3,4,5,6)) as 'TotalSold', 
		(select COALESCE(sum(tblShipmentItem.Quantity),0)
		from dbo.tblShipmentItem
		inner join dbo.tblInvoiceItem on
			tblInvoiceItem.InvoiceItemID = tblShipmentItem.InvoiceItemID	
		inner join dbo.tblInvoice on 
			tblInvoice.InvoiceID = tblInvoiceItem.InvoiceID
		where 
			tblInvoice.InvoiceTypeID = 1
		and
			 tblInvoiceItem.InventoryItemID = i.InventoryItemID
		and 
			tblInvoiceItem.Brokered = 0
		and
			tblInvoice.InvoiceStatusID in (2,3,4,5,6)) as 'TotalShipped',
	(select sum(tblShipmentItem.Quantity) 
	from
		dbo.tblShipmentItem
	inner join dbo.tblInvoiceItem on
		tblShipmentItem.InvoiceItemID = tblInvoiceItem.InvoiceItemID
	inner join dbo.tblInvoice on
		tblInvoice.InvoiceID = tblInvoiceItem.InvoiceID
	where
		tblInvoiceItem.InvoiceItemStatusID = 2
	and
		tblInvoice.InvoiceTypeID = 3
	and
		tblInvoiceItem.InventoryItemID = i.InventoryItemID
	) as 'OnHold',
	(select coalesce(sum(tblShipmentItem.Quantity),0) from
dbo.tblShipmentItem
	inner join dbo.tblShipment on 
		tblShipment.ShipmentID = tblShipmentItem.ShipmentID
	inner join dbo.tblInvoiceItem on 
		tblShipmentItem.InvoiceItemID = tblInvoiceItem.InvoiceItemID
	where
		tblShipment.ShipmentTypeID  in (1,3)
	and
		tblShipment.FromWarehouseID = w.WarehouseID
	and
		tblInvoiceItem.InventoryItemID = i.InventoryItemID
	and
		tblShipment.ShipmentDate > coalesce(stock.CreatedDate,'1/1/2003')
	) as 'InBound',
	(select coalesce(sum(tblShipmentItem.Quantity),0) from
dbo.tblShipmentItem
	inner join dbo.tblShipment on 
		tblShipment.ShipmentID = tblShipmentItem.ShipmentID
	inner join dbo.tblInvoiceItem on 
		tblShipmentItem.InvoiceItemID = tblInvoiceItem.InvoiceItemID
	where
		tblShipment.ShipmentTypeID in (2,4)
	and
		tblShipment.FromWarehouseID = w.WarehouseID
	and
		tblInvoiceItem.InventoryItemID = i.InventoryItemID	
	and 
		tblShipment.ShipmentDate > coalesce(stock.CreatedDate,'1/1/2003')
	) as 'Outbound',
	(select coalesce(sum(tblShipmentItem.Quantity),0) from
dbo.tblShipmentItem
	inner join dbo.tblShipment on 
		tblShipment.ShipmentID = tblShipmentItem.ShipmentID
	inner join dbo.tblInvoiceItem on 
		tblShipmentItem.InvoiceItemID = tblInvoiceItem.InvoiceItemID
	where
		tblShipment.ShipmentTypeID = 3
	and
		tblInvoiceItem.InventoryItemID = i.InventoryItemID	
	) as 'TotalTransferInbound',
	(select coalesce(sum(tblShipmentItem.Quantity),0) from
dbo.tblShipmentItem
	inner join dbo.tblShipment on 
		tblShipment.ShipmentID = tblShipmentItem.ShipmentID
	inner join dbo.tblInvoiceItem on 
		tblShipmentItem.InvoiceItemID = tblInvoiceItem.InvoiceItemID
	where
		tblShipment.ShipmentTypeID = 4
	and
		tblInvoiceItem.InventoryItemID = i.InventoryItemID	
	) as 'TotalTransferOutbound'	
from 
	dbo.tblInventoryItem i
inner join dbo.tblInventory inv on
	i.InventoryID = inv.InventoryID
cross join 
	(select Name,WarehouseID from dbo.tblWarehouse) as w
/*
inner join #temp stock on
	(i.InventoryItemID = stock.InventoryItemID and w.WarehouseID =
stock.WarehouseID)
*/
inner join 
	(select 
		i.InventoryItemID,
		w.WarehouseID,		
		(select top 1 InventoryItemAdjustmentID
		from 
			dbo.tblInventoryItemAdjustment s
		where
			s.InventoryItemID = i.InventoryItemID
		and
			s.WarehouseID = w.WarehouseID
		order by CreatedDate desc
		) as InventoryItemAdjustmentID		
	from
		dbo.tblInventoryItem i
	cross join
		dbo.tblWarehouse w
	where
		w.WarehouseID > 1) as t
	ON
		(i.InventoryItemID = t.InventoryItemID and w.WarehouseID =
t.WarehouseID)
left join dbo.tblInventoryItemAdjustment stock on 
	stock.InventoryItemAdjustmentID = t.InventoryItemAdjustmentID	
	
where
	w.WarehouseID > 1




This query returns the results I want but VERY slow and extremely
inefficent.  I really hope there is a better way.

Note: The result must have each warehouse stock levels grouped by
InventoryItem becuase I use the data invdivually for each warehouse in
the application.

Thank you to anyone who can help!


Here is the CODE to create a very simplied DB as an example:
CREATE DATABASE MachDB
GO

USE MachDB
GO

CREATE TABLE [dbo].[tblInventoryAdjustment] (
	[InventoryAdjustmentID] [int] IDENTITY (1, 1) NOT NULL ,
	[CreatedDate] [datetime] NULL ,
	[ActualOnHand] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblInventoryItem] (
	[InventoryItemID] [int] IDENTITY (1, 1) NOT NULL ,
	[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblInvoice] (
	[InvoiceID] [int] IDENTITY (1, 1) NOT NULL ,
	[CreatedDate] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblInvoiceItem] (
	[InvoiceItemID] [int] IDENTITY (1, 1) NOT NULL ,
	[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
	[InvoiceID] [int] NULL ,
	[InventoryItemID] [int] NULL ,
	[Quantity] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblShipment] (
	[ShipmentID] [int] IDENTITY (1, 1) NOT NULL ,
	[CreatedDate] [datetime] NULL ,
	[WarehouseID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblShipmentItem] (
	[ShipmentItemID] [int] IDENTITY (1, 1) NOT NULL ,
	[InvoiceItemID] [int] NOT NULL ,
	[ShipmentID] [int] NULL ,
	[Quantity] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblWarehouseID] (
	[WarehouseID] [int] NULL ,
	[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblInventoryAdjustment] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblInventoryAdjustment] PRIMARY KEY  CLUSTERED 
	(
		[InventoryAdjustmentID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblInventoryItem] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblInventoryItem] PRIMARY KEY  CLUSTERED 
	(
		[InventoryItemID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblInvoice] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblInvoice] PRIMARY KEY  CLUSTERED 
	(
		[InvoiceID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblInvoiceItem] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblInvoiceItem] PRIMARY KEY  CLUSTERED 
	(
		[InvoiceItemID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblShipment] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblShipment] PRIMARY KEY  CLUSTERED 
	(
		[ShipmentID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tblShipmentItem] WITH NOCHECK ADD 
	CONSTRAINT [PK_tblShipmentItem] PRIMARY KEY  CLUSTERED 
	(
		[ShipmentItemID]
	)  ON [PRIMARY] 
GO

---

Request for Question Clarification by mathtalk-ga on 24 May 2004 18:21 PDT

Hi, aa7im-ga:

Is there a need to have the results returned by a single SQL query? 
Often the most efficient way to retrieve a heavily massaged result set
will invoke a stored procedure, or possibly will subdivide the result
set into a sequence of similar result sets.

What "client" will retrieve the results of the "query" you require?

regards, mathtalk-ga

---

Clarification of Question by aa7im-ga on 24 May 2004 20:46 PDT

The "client" is an ASP.NET web application.  Currently I have the
query within a View so that I can query the result even further by
saying something like: return only stuff with (QuanitityInStock > 0). 
I could break the query into small pieces within a Stored Proc and
then build a complete result set to return but I was concerned that
being able to filter the result set even further would be more
difficult with a stored proc of this nature.  I could have multiple
stored procs targeting different kinds of search results but then I
would have to maintain multiple procs...

---

Clarification of Question by aa7im-ga on 04 Jun 2004 02:00 PDT

Wow! That's alot of work you did there!

Thanks!, I will check it out and see what kind of performance I can
acheive using your methods...

I'll post the results here once I get it working with my actual tables

Here is a revised query that should dramatically speed things up. I
have done my best to make this syntactically correct, but since I
don't have the actual tables this code has never been tested...but in
theory it works :)

This query does make a couple of assumptions about your
tblShipmentItems, namely I assumed that there would only be one
ShipmentItem record for each InvoiceItem record. I couldn't tell from
the original query if this assumption was good. If there is more than
one Shipment record for each InvoiceItem then the you will need to add
other conditions to the case statments to limit which shipment records
it uses (e.g. only inbound shipments).

Because I couldn't test this, I am very curious to hear if this works
and improves the performance. Please post a comment as to it's
results.

I'd also be happy to help with any tweaking should it not be exactly
correct the first time.

Select  InventoryItemID           = Max(adj.InventoryItemID),
        InventoryItemAdjustmentID = adj.InventoryItemAdjustmentID,
        WarehouseID               = Max(adj.WarehouseID),
        WarehouseName             = Max(adj.WarehouseName),
        AdjustmentDate            = Max(adj.AdjustmentDate),
        AdjustmentQuantity        = Max(adj.AdjustmentQuantity),

        TotalSold = IsNull(Sum(Case When     tblInvoice.InvoiceTypeID = 1
                                         And tblInvoiceItem.Brokered  = 0
                                         And
tblInvoice.InvoiceStatusID in (2,3,4,5,6) Then tblInvoiceItem.Quantity
                                                                      
                Else 0
                               End), 0),

        TotalShipped = IsNull(Sum(Case When     tblInvoice.InvoiceTypeID = 1
                                            And tblInvoiceItem.Brokered  = 0
                                            And
tblInvoice.InvoiceStatusID in (2,3,4,5,6) Then
tblShipmentItem.Quantity
                                                                      
                   Else 0
                                  End), 0),

        OnHold = IsNull(Sum(Case When     tblInvoiceItem.InvoiceItemStatusID = 2
                                      And tblInvoice.InvoiceTypeID    
      = 3 Then tblShipmentItem.Quantity
                                                                      
          Else 0
                            End), 0),

        InBound = IsNull(Sum(Case When     tblShipment.ShipmentTypeID in (1,3)
                                       And tblShipment.FromWarehouseID
= adj.WarehouseID
                                       And tblShipment.ShipmentDate >
IsNull(adj.AdjustmentDate, '1/1/2003') Then tblShipmentItem.Quantity
                                                                      
                                      Else 0
                             End), 0),

        OutBound = IsNull(Sum(Case When     tblShipment.ShipmentTypeID in (2,4)
                                        And
tblShipment.FromWarehouseID = adj.WarehouseID
                                        And tblShipment.ShipmentDate >
IsNull(adj.AdjustmentDate, '1/1/2003') Then tblShipmentItem.Quantity
                                                                      
                                       Else 0
                              End), 0),


        TotalTransferInbound = IsNull(Sum(Case When
tblShipment.ShipmentTypeID = 3 Then tblShipmentItem.Quantity
                                                                      
            Else 0
                                          End), 0),

        TotalTransferOutbound = IsNull(Sum(Case When
tblShipment.ShipmentTypeID = 4 Then tblShipmentItem.Quantity
                                                                      
             Else 0
                                           End), 0)

From (Select  InventoryItemID           = stock.InventoryItemID,
              InventoryItemAdjustmentID = stock.InventoryItemAdjustmentID,
              WarehouseID               = stock.WarehouseID,
              WarehouseName             = w.Name,
              AdjustmentDate            = stock.CreatedDate,
              AdjustmentQuantity        = IsNull(stock.ActualOnHand, 0)
      From (Select InventoryItemAdjustmentID =
Max(LastAdjustmentIDOnLastDate.InventoryItemAdjustmentID)
            From (Select InventoryItemID, 
                         WareHouseID,
                         CreatedDate = Max(CreatedDate) 
                  From dbo.tblInventoryItemAdjustment 
                  Where WarehouseID > 1
                  Group By InventoryItemID, 
                           WareHouseID) As LastAdjustmentDate,
                 dbo.tblInventoryItemAdjustment As LastAdjustmentIDOnLastDate
            Where LastAdjustmentIDOnLastDate.InventoryItemID =
LastAdjustmentDate.InventoryItemID
              And LastAdjustmentIDOnLastDate.WareHouseID     =
LastAdjustmentDate.WareHouseID
              And LastAdjustmentIDOnLastDate.CreatedDate     =
LastAdjustmentDate.CreatedDate
            Group By LastAdjustmentIDOnLastDate.InventoryItemID, 
                     LastAdjustmentIDOnLastDate.WareHouseID, 
                     LastAdjustmentIDOnLastDate.CreatedDate) As
LastInventoryItemAdjustment,
           dbo.tblInventoryItemAdjustment stock,
           dbo.tblInventoryItem i,
           dbo.tblInventory inv,
           dbo.tblWarehouse w 
      Where stock.InventoryItemAdjustmentID =
LastInventoryItemAdjustment.InventoryItemAdjustmentID
        And i.InventoryItemID               = stock.InventoryItemID
        And inv.InventoryID                 = i.InventoryID 
        And w.WarehouseID                   = stock.WarehouseID) adj 
   
    Left Outer Join dbo.tblInvoiceItem  On
tblInvoiceItem.InventoryItemID = adj.InventoryItemID
    Left Outer Join dbo.tblInvoice      On tblInvoice.InvoiceID       
   = tblInvoiceItem.InvoiceID
    Left Outer Join dbo.tblShipmentItem On
tblShipmentItem.InvoiceItemID  = tblInvoiceItem.InvoiceItemID
    Left Outer Join dbo.tblShipment     On tblShipment.ShipmentID     
   = tblShipmentItem.ShipmentID

Group By adj.InventoryItemAdjustmentID

The changes to the query should speed it up when returning all rows. I
noticed you mentioned you were putting the query in a view and then
selecting from the view with additional criteria. The changes should
optimized the query on the criteria from the original query, however
if you put it in a view and add some additional criteria (such as
selecing only one InventoryItem) it will probably be even slower than
the original. In order to optimize it for your everyday use I would
need to know more accuratly what your other limiting criteria are.

Wow! That's alot of work you did there!

Thanks!, I will check it out and see what kind of performance I can
acheive using your methods...

I'll post the results here once I get it working with my actual tables