Hi,
What's the best way to calculate a customers age and value by month and year?
I need to be able to calculate customer value by month and year, and then to calculate their age at each month in time. I've found a way of grouping sales by month and year that includes age for a particular contact like this:
select fh.contact_number
, concat(year(fh.transaction_date), '-', month(fh.transaction_date)) as transaction_month_year
, cast(fh.transaction_date as date) as transaction_date
, sum(fh.amount) as ttl_amount_in_month
, floor(datediff(dd,c.date_of_birth,fh.transaction_date)/365.23076923074) as Age_at_Transaction_Date
from financial_history as fh
inner join contacts as c on fh.contact_number=c.contact_number
where c.contact_number = 38
group by fh.contact_number
, fh.transaction_date
, dateadd(mm, datediff(mm, 0, fh.transaction_date),0)
, c.date_of_birth
It seems to work, but I wondered if anyone knew a better way to achieve this?
Any help, advice or direction would be hugely appreciated as ever!
Thanks
Lins