Database and index reviews cannot be automated.  No software now does even a reasonably decent job of doing that.  It must be done by a person.

Especially important is determining and...

That's all the biggest parts of it.

Sometimes you can also need data cardinality info, that is, how many different values / row counts there are for certain columns.

For example, say...

I have to admit, I really don't know how to debug "it doesn't like it."  I'm not even 100% sure what that specifically means.

Hyperbole.

But, c'mon man, an...

Start with data modeling first, so not with "tables"/"columns" but with entities and attributes.  Spend some time getting a full list of all data attributes (elements, "columns") that you need...

The key thing for performance is likely to be the indexing, and here specifically the clustered indexing.

Both IPS and V should be clustered on ( LocNum, InOrOut, AccountNumber ), in...

That code should return exactly the same datetime range as your original code.  If not, please post what strings your current code is returning for a couple of rows against...

Purely from a performance perspective, I think you'd be better off using another table -- CustomerRanges -- to reduce each range to a single number for the final lookup in...

First, get rid of the functions against table columns in the WHERE clause. Those are always a terrible idea for performance.  (Code reformatted but so that it can be read...

I remember someone testing and saying that it...

About the>= vs =, yes, you're correct, it can be = in this example. I didn't mention it, but often we rerun a few days in the back, so we...

Just an example of concatenation when using EXEC():

DECLARE @sql1 varchar(1000)

DECLARE @sql2 varchar(1000)

SET @sql1 = 'SELECT GETDATE() AS today'

SET @sql2 = 'SELECT COUNT(*) AS object_count FROM sys.objects'

EXEC(@sql1 + ';' + @sql2)

Oh, sorry, I didn't look closely enough, I thought it was EXEC() and not EXEC sys.sp_executesql.

For sp_executesql, you must combine code before running the proc.

DECLARE @sql NVARCHAR(4000)

,@SQL1 NVARCHAR(4000);

SELECT @sql =...

You left off the parens (and something to separate the new SELECT from the old command, either a space or a semi-colon):

EXEC sp_executesql (@SQL + ';' + @SQL1)

To determine proper clustering (PKs) for the tables, we'd need to know more about how the data is most often processed, by statistics type or entityid or timeperiod range.

However, as...