I wasn't as concerned with the random data generation as I was with the queries for the actual data.  Thank you though as it is much better. I tried it...

After some larger data set testing I found the solution above does not work for all data sets. Mainly those with duplicate active_dates for an account_id. I also got several...

• This reply was modified 5 years, 4 months ago by  John Gundrum. Reason: Updated code
• This reply was modified 5 years, 4 months ago by  John Gundrum.

That does work, however, I didn't mention that there is an older environment where this needs to run and LAG is not available.

I did get a pre-LAG solution that works....

Finally, something that worked! Thanks!

Do you have some screen shots?  In TFS Pro 2013 when a .sql file is right-clicked there is no Open With...

I am having the same problem with SSMS 2012. Unfortunately, sqlwb.sql.9.0 is not for SSMS 2012 so the above will not work.

Does anyone have a solution for SSMS 2012?

I should have mentioned there is a numbers table (referred to as a tally table here) in the database already. I'll modify the examples to use that instead.

My initial...

Chris,

Just finished reading the Tally table article and it seems to be exactly what I was looking for!

Thanks alot!

John G.

Chris,

No, I have never worked with a tally table. Can you explain what it is?

Is it possibly a table of all possible dates?

Thanks,

John G.