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

I'd be real careful with that.  It uses the WEEKDAY date part, which is subject to change and most recommend not using it for such calculations.  I...

Create all temp tables separately from loading them.  Bonus: you could then also properly cluster the temp tables before loading them.

The direct load of table with SELECT ... INTO #temp...

#5 is completely wrong approach.  You should never use ISNULL() in a WHERE clause.  Instead move the condition from the WHERE clause to the LEFT JOIN.  You need to that...

Can't just take your word for that, still need to see DDL for the tables, including all index definitions.

Would we need to?  A single math calc vs iterating thru string generations and comparisons?

Sure, it's above; I posted it years ago, when this q was first asked.

Actually I should adjust it again, you only need to calc the very first Friday,...

Just out of curiosity, what is the recovery model on the model db?

I'm wondering if the db got created then restored over ?!

You don't need to iterate/loop thru values looking for 'Tuesday', and personally I wouldn't.  I find all such code much harder to adjust later, and generally less efficient.

I guess you...

• This reply was modified 4 years, 3 months ago by ScottPletcher. Reason: Adjusted code to add variable for relative week# within month