my bad.  i made a typo when i copied over the formula. ...

It's Friday afternoon and this was a nice small example.  Here's my offering:

-- Create sample data
DROP TABLE IF EXISTS #Example
CREATE TABLE #Example
    (DayNum...

no problem.  here you go:

CREATE TABLE...

You've already got restrictions based on @LOOK_BACK_PERIOD in the WHEREs, it would just be more efficient to pre-filter rather than waiting until some processing has been done on all...

duplicate post by accident

• This reply was modified 1 years, 8 months ago by water490.

Cool.  Can you show us what you settled...

Did you add the WHERE condition for the TXN_DATE?  If you have a long history in the table, that would help the most.

Then try an index on (txn_date, company)...

• This reply was modified 1 years, 8 months ago by water490.

Need the DDL for the table to sure of more specific recommendations for best speed, but, assuming you will keep a long history in this table:

(1) Either:...

• This reply was modified 1 years, 8 months ago by water490.

Need the DDL for the table to sure of more specific recommendations for best speed, but, assuming you will keep a long history in this table:

(1) Either: (A) cluster...

sure thing no problem.

I have attached an Excel sheet with sample data and expected outcome.  In case you prefer to not download file then here is a screenshot with description. ...

• This reply was modified 1 years, 8 months ago by water490.
• This reply was modified 1 years, 8 months ago by water490.
• This reply was modified 1 years, 8 months ago by water490.
• This reply was modified 1 years, 8 months ago by water490.

thank you both!  i know how to fix my query now because of your feedback.  thank you again for the help!!

thank you it worked!

this is super interesting.  i didn't know that.

this begs the question....how do you decide on an appropriate index?  is there a good resource you can suggest that goes over the...

• This reply was modified 1 years, 9 months ago by water490.

does it make sense that an unindexed query is faster than an indexed one?

thank you everyone.  What recommended  settings for parallelism should I set SS to?  Clearly the defaults are not recommended.