you can't concatenate the name of the table in the FROM clause like that, if the CASE statement and separate subqueries isn't workable for some reason, you'd have to use...

your table has 6 columns in it, but your INSERT statement only has 5 columns.  If you don't specify a value for all 6 columns, you have to explicitly specify...

have a look at DBCC CHECKIDENT to fix the next identity value:
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql

is it possible that TradeActive table doesn't have statistics or has wildly out of date statistics on the column IDPosition?  That could cause a bad estimate of the join.

change your join X1 X2 to be a LEFT OUTER JOIN

or you could use:

SELECT x1.pID, LAG (Stat, 1, NULL) OVER (PARTITION BY x1.pID ORDER...

I don't think you need an IF or CASE here, you should be able to compute the starting OpenDate WHERE clause like one of these, depending on if you need...

I had a couple of questions about this query.
1.)  If your subquery isn't using t2, then why the extra reference to tblProducts?
2.)  using your sample of  'NonPot,Meter,2 Year'...

it looks like the recommended practice is 8K for testing for SQL Server usage:
https://technet.microsoft.com/en-us/library/dn894707(v=ws.11).aspx
https://sqlperformance.com/2015/08/io-subsystem/diskspd-test-storage

reminds me of a friend of mine who's last name is Bort, with a first initial of A.  At a certain place they worked, the login was first initial last...

Another option for SSAS would be what's called Role-based Security:
https://www.mssqltips.com/sqlservertip/1834/introduction-to-dimension-security-in-sql-server-analysis-services-ssas-2005/
That will ensure no matter how the user is connecting to the cube, they will only see what...