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...

Create the temp table before calling the proc.  Have a flag that is passed in to "tell" the proc to load its results into the temp table, or you could...

sql playing dumb on this case

if your software can be set to do other types of joins/cross applies or even to, as you said, generate the isnull (which works...

Not one of these are valid date/datetime values:

'Sep 01', '2001-Q3', '2001-Q2', 'Sep 01', '2001-Q3', '2001-Q2'

Whoever's teaching your class is not particularly expert at db design.  Storing NNN-NNN-NNN is just plain wrong; it's a waste of space to store fixed dashes in a column.

Gotta say this, since it's a pet peeve of mine = Using comments in place of accurate names.

Viz, why?:

FROM Tally t --Month count sequence starting at 0

rather than:

FROM Tally month_count...

Hi Scott,

like I mentioned earlier, the WHERE clause is added afterwards in my application (so the user can choose to see archived corporations only, active corporations only or both)....

Hi Scott, this is oversimplified but it reproduces the problem. The question becomes, what is missing so we do not have to wrap EA.IsArchived in a ISNULL?

            
September 24, 2021 at 6:32 pm
            
#3932851

            
            
                                                
        

Yeah, you can test for NULLs in columns and/or GROUPING_ID to determine: which rows are total rows and which rows are detail rows; to exclude total rows you don't want;...

Actually, if you want to comprehensively review the indexes on those table, run this script twice (once for 'Corporation%' tables and once for 'Entity%' tables) and post both result sets...

You should be able to script this out.  Make sure the "Script indexes" option is on.  Then Script the table; that should give you DDL for the table and all...

OK.  You said "instead of" so I figured you didn't want the other details in the new query.

Maybe try:

GROUP BY entity,groupname,description,effdate WITH ROLLUP

You can get rid of total lines you...

Not 100% sure what you need, but probably:

SELECT  entity
        ,description
        ,[Total] =...

Do you have an index on:

CorporationHistory ( CorporationID, EffectiveDate )?

If not, you need one.

I specialize in tuning SQL Server.  It's easy to overlook indexes if you don't have a lot...