I need to know the rate of growth for the past five years. Essentially how many new customers were added last year (or season), and the year before, and before, etc. Any suggestions on a query? thanks!
Why not something like this:
select count(*),'6 Years Ago' from t_customer where create_dt < dateadd(year, -6,GETDATE()) union
union
select
select count(*),'1 Years Ago' from t_customer where create_dt < dateadd(year, -1,GETDATE())
union all
select count(*),'0 Years Ago' from t_customer where create_dt < dateadd(year, -0,GETDATE())
You may have to play around with where you want the dates. This produces 5 years ago as of the date you run the query. You might want to align this on your fiscal year or calendar year.
Then use this data ans say you were at this count 6 years ago. Subtract the 6 year ago count from 5 years ago count. The remainder is the 5 year ago growth. That will give you the number of folks you added 5 years ago. Then do this for 4year ago count - 5 year ago count. This will get you the growth for 4 years ago growth and so on.
This could be cleaned up by adding an additional where clause item to remove inactive accounts or merged accounts. You could also clean this up to do all of the arithmetic to give you the differences not the totals as of a particular year.
Hope this helps.