this has been fixe byThis is incorrect as two payments on the same month but different year, say 11/2/2013 and 21/2/2014, will be averaged on 1 instead of 12 months
Code: Select all
count(distinct strftime('%Y%m', tran.TRANSDATE)) as [Months Active]
A little confusion about your definition upon 'Active Months'. Intuitively, the correct answer should be 2 here. therefore, i still don't get your point here. would you please provide more example to illustrate the calculation logic here.Also, half-year payments, say 15/2/2013 and 15/8/2013, will be averaged over 2 instead over 12 months.
From my understanding, 99% SQL for general report should be less 50 lines.