September 22, 2014 at 5:02 am
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
September 22, 2014 at 6:34 am
Not sure if it's weird to reply to my own thread(!) but this does seem to work loads better:
select year(fh.transaction_date) as [year]
, month(fh.transaction_date) as [month]
, sum(amount) as total
, floor(datediff(dd,c.date_of_birth,fh.transaction_date)/365.23076923074) as Age_at_Transaction_Date
from cis.financial_history as fh
inner join cis.contacts as c on fh.contact_number=c.contact_number
where fh.contact_number = 38
group by year(fh.transaction_date)
, month(fh.transaction_date)
, c.date_of_birth
, fh.transaction_date
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply