I gather its a marketing tool.
So you get a group of customers who bought in Year 2010 Q1 and you track the same groups spent through the years.
2010 Q2 - £50 AVG
2010 Q3 - £30
2010 Q4 - £25
so each month a new group appears and you track them.
YearQInitial#Q1Q2Q3Q4Q5Q6
20134$239 171
20133$338 19848
20132$268 2241515
20124$203 1608611
20123$229 20630846
20122$273 2021489512
20121$254 322241017102215
-----
So far I am getting the accounts by year and quarter and think to use that data in a CTE??
select
o.[account_id]
,orderyear
,orderquarter
from
[dbo].[orders] o
inner join [dbo].[orderlines] ol on o.[order_id] = ol.[order_id]
cross apply(values(year(o.[ship_date]))) as a1(orderyear)
cross apply(values(DatePart(quarter,(o.[ship_date])))) as a2(orderquarter)
where
product_code like ('ll%') and ship_date = (select min(ship_date) from [dbo].[orders] mo where mo.[order_id] = ol.order_id)
and total_value>0 AND order_status NOT LIKE 'return%' AND order_status NOT LIKE 'cancel%' AND order_status NOT LIKE 'freeze%'
Thanks