Drew I appreciate your feedback.  Please let me know if I articulated my goal.  if so, can you please suggest a better way of getting to the end result.  

Can i attach an excel file? will make it easier

When its all said and done, I need to be able to indentify when a client added an account...

I have a dynamic process that adds months and assess whether the account/product combination unique id is NEW, RETAINED from the previous month, or lost.  The table i have does...

valid point.  Sorry about that.  Please see below.  

CREATE TABLE #mytable
   (
   ID integer,
   "2017-06-30" varchar,
   "2017-07-31" varchar,
  ...

My table is dynamic as it keeps a track of retained/loss/gained products for any given client globally.  We use this information to gain a deeper understanding of our clients purchasing...

fair point.. unfortunately, I am not as advanced.  Not sure where to start.  I built a dynamic excel model that works well but not efficient.  Looking to get input from...

Caps out at 100 rows where there should be more? this i would def hope to get your input on

Thank you for your help and for being so...

I think i figured it out.  I was only changing the dayadd formula in the select to 'day'.  Just changed the datediff in the cross apply and i think it...

I appreciate your help on the end of month.

I am trying but failing at augmenting the code to break down the difference between start and end date by...

hi,
I was hoping you'd be able to help me to augment the code so that the date is always the last day of the month for each individual month.

I appreciate the feedback.  Def have to comment it.

The final view is powering a BI tool which is scheduled for an hourly refresh.  The view allows the data...

Thank you for the follow up.  Please see code.

The view creation is taking just under 3 min. It's not terrible but if there is a better way to...

drop view vw_active_program_product_p1
go
--drop table tbl_active_program_products
--create index indx_active_program_products on (account_id, product_id, product_start_date, product_end_date)

create view vw_active_program_product_p1 as
select
        account_id, account_name, product_id, selling_office, region, business_industry, market_segment, product_type,...