Sql Query to select missing records based on multiple hard coded ranges

Creating a SQL query that performs math with variables from multiple tables

This question I asked previously will help a bit as far as layout, for the sake of saving time I'll include the important bits and add in more detail for scenarios pertaining to this:

A mockup of what the tables look like:

Inventory

ID  |  lowrange  |  highrange | ItemType
----------------------------------------
1   |  15        |  20        | 1
2   |  21        |  30        | 1
3   |  null      |  null      | 1
4   |  100       |  105       | 2

MissingOrVoid

ID  |  Item  |  Missing  |  Void
---------------------------------
1   |  17    |   1       |  0
1   |  19    |   1       |  0
4   |  102   |   0       |  1
4   |  103   |   1       |  0
4   |  104   |   1       |  0

TableWithDataEnteredForItemType1

InventoryID| ItemID | Detail1 | Detail2 | Detail3
-------------------------------------------------
1          | 16     | Some    | Info    | Here
1          | 18     | More    | Info    | Here
1          | 20     | Data    | Is      | Here
2          | 21     | ....    | ....    | ....
2          | 24     | ....    | ....    | ....
2          | 28     | ....    | ....    | ....
2          | 29     | ....    | ....    | ....
2          | 30     | ....    | ....    | ....

TableWithDataEnteredForItemType2

InventoryID| ItemID | Col1 | Col2 | Col3
----------------------------------------
4          | 101    | .... | .... | ....

I attempted this. I know it is not functional but it illustrates what I'm trying to do and I personally haven't seen anything written up like this before:

SELECT CASE WHEN (I.ItemType = 1) THEN SELECT TONE.ItemID FROM 
   TableWithDataEnteredForItemType1 TONE WHEN (I.ItemType = 2) 
   THEN SELECT TTWO.ItemID FROM TableWithDataEnteredForItemType2 
   TTWO END AS ItemMissing Inventory I JOIN CASE WHEN (I.ItemType = 1) THEN
   TableWithDataEnteredForItem1 T WHEN (I.ItemType = 2) THEN
   TableWithDataEnteredForItem2 T END ON 
   I.ID = T.InventoryID WHERE ItemMissing NOT BETWEEN IN (SELECT 
   I.lowrange FROM Inventory WHERE I.lowrange IS NOT NULL) AND IN 
   (SELECT I.highrange FROM Inventory WHERE I.highrange IS NOT NULL) 
   AND ItemMissing NOT IN (SELECT Item from MissingOrVoid)

The result should be:

ItemMissing
----
15
22
23
25
26
27
105

I know I'm probably not even going in the right direction with my query,开发者_开发知识库 but I was hoping I could get some direction as to fixing it to get the results that are needed.

Thanks

Edit:

Specific requirements (thought I included this but appears I didn't) - return list of all items not accounted for in the system. There are two ways of something being accounted for: 1) an entry in the corresponding ItemType table 2) located in MissingOrVoid (known items to be missing or removed).

DDL (I had to look this up as I wasn't sure what was meant here. Being that I have very little experience creating tables by scripting, this will probably be psuedo-DDL):

Inventory

ID - int, identifier/primary key, not nullable
lowrange - int, nullable
highrange - int, nullable
itemtype - int, not nullable

MissingOrVoid

ID - int, foreign key for Inventory.ID, not nullable
Item - int, identifier/primary key, not nullable
missing - bit, not nullable
void - bit, not nullable

Tables for Item types:

IntenvoryID - int, foreign key for Inventory.ID, not nullable
ItemID - int, primary key, not nullable
Everything else - not needed for querying, just data about the item (included 
    to show that the tables aren't the same content)

Edit 2: Here's an incredibly inefficient C# and Linq way of doing it but maybe of some help:

List<int> Items = new List<int>();
List<int> MoV = (from c in db.MissingOrVoid Select c.Item).ToList();
foreach (Table...ItemType1 row in db.Table...ItemType1)
    Items.Add(row.ItemID);
foreach (Table...ItemType2 row in db.Table...ItemType2)
    Items.Add(row.ItemID);
List<Range> InventoryRanges = new List<Range>();
foreach (Inventory row in db.Inventories)
{
    if (row.lowrange != null && row.highrange != null)
        InventoryRanges.Add(new Range(row.lowrange, row.highrange));
}

foreach (int item in Items)
{
    foreach (Range range in InventoryRanges)
    {
        if (range.lowrange <= item && range.highrange >= item)
            Items.Remove(item);
    }
    if (MoV.Contains(item))
        Items.Remove(item);
}

return Items;

---

There's a ready made number table called master..spt_values, which can be quite helpful in this case. Note though, that you can use this table if the distance between lowrange and highrange cannot exceed 2047, otherwise create, populate and use your own number table instead.

Here's the method:

SELECT
  ItemMissing = i.Item
FROM (
  SELECT
    i.ID,
    Item = i.lowrange + v.number,
    i.ItemType
  FROM Inventory i
    INNER JOIN master..spt_values v
      ON v.type = 'P' AND v.number BETWEEN 0 AND i.highrange - i.lowrange
) inv
  LEFT JOIN MissingOrViod m
    ON inv.ID = m.ID AND inv.Item = m.Item
  LEFT JOIN TableWithDataEnteredForItem1 t1 ON inv.ItemType = 1
    AND inv.ID = t1.InventoryID AND inv.Item = t1.ItemID
  LEFT JOIN TableWithDataEnteredForItem2 t2 ON inv.ItemType = 2
    AND inv.ID = t2.InventoryID AND inv.Item = t2.ItemID
WHERE m.ID IS NULL AND t1.InventoryID IS NULL AND t2.InventoryID IS NULL

The subselect expands the Inventory table into a complete item list with item IDs as defined by lowrange and highrange (this is where the number table comes in handy). The obtained list is then compared against the other three tables to find and exclude those items that are present in them. The remaining items, then, constitute the list of 'items missing'.