# question

## Query to identify high spending customers over variable time periods

Hi, sorry if this is a newbie question -

Simplifying our order table into CustomerId, OrderDate, OrderTotal

I've been asked to write a query to identify customers who have Sum(OrderTotal) > x in any 6 month period over the last 3 years. So for some customers they may exceed that Jan 25 '18 - Feb 25 '18, some could exceed it Dec 25 '18 - Apr 5 '19 etc.

I'm not a sql expert, am struggling, and have a rather tight deadline to get this done so I would be super grateful for any help with this query. Thank you.

Tony

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·

I think a lot of the implementation here is going to be driven by exactly how the data is stored, but hopefully this example will get you started in the right direction.

This is not the most efficient solution as it calculates the 6-monthly sales total for every given point in the base data.

In this example I use dateadd(month,6, OrderDate) to define a 6-month period, but you may need a more granular definition, i.e. 26 weeks or 182 days or whatever. Same for the definition of 'last 3 years' too.

```declare @YourTable table (CustomerId int, OrderDate date, OrderTotal int)

insert into @YourTable (CustomerId, OrderDate, OrderTotal) select 1, '1 Jan 2018', 10
insert into @YourTable (CustomerId, OrderDate, OrderTotal) select 1, '1 Feb 2018', 10
insert into @YourTable (CustomerId, OrderDate, OrderTotal) select 1, '1 Mar 2018', 15
insert into @YourTable (CustomerId, OrderDate, OrderTotal) select 2, '1 Jan 2018', 10
insert into @YourTable (CustomerId, OrderDate, OrderTotal) select 2, '1 Feb 2018', 10
insert into @YourTable (CustomerId, OrderDate, OrderTotal) select 2, '1 Aug 2018', 25
insert into @YourTable (CustomerId, OrderDate, OrderTotal) select 3, '1 Jan 2018', 100
insert into @YourTable (CustomerId, OrderDate, OrderTotal) select 4, '1 Jan 2018', 10
insert into @YourTable (CustomerId, OrderDate, OrderTotal) select 4, '1 Feb 2018', 10
insert into @YourTable (CustomerId, OrderDate, OrderTotal) select 4, '1 Sep 2018', 15

-- in this example the sales threshold is set at 30 over a period of 6 months

select
YT1.CustomerId,
(select sum(OrderTotal) from @YourTable YT2
where YT1.CustomerID = YT2.CustomerId
and YT2.OrderDate between YT1.OrderDate
and dateadd(month, 6, YT1.OrderDate) ) TotalSales6Monthly
from @YourTable YT1
where
(select sum(OrderTotal) from @YourTable YT2
where YT1.CustomerID = YT2.CustomerId
and YT2.OrderDate between YT1.OrderDate
and dateadd(month, 6, YT1.OrderDate) ) >= 30  --the sales threshold
and
YT1.OrderDate > dateadd(year, -3, getdate()) --last 3 years

CustomerId  TotalSales6Monthly
----------- ------------------
1           35
2           35
3           100
(3 rows affected)

```

Customer ID 4 is not returned as the threshold of 30 is not exceed in a 6 month period

1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·

Wow, thanks so much Kev. As it stands I've just now had a huge workload added to my plate on top of this so was getting very stressed. This is a massive help.

Efficiency isn't too much of an issue as it will be run in an overnight process, which we are only using a fraction of our available time for.

The only changes I made were removing the totalsales6monthly, as I didn't need the values in the results - just the customers' Id, and popping a DISTINCT in there as it returns mutliple results when you have many orders.

Once again, many thanks. :)

Best wishes,

Tony

0 Likes 0 ·