Decent Way To Calculate Lapse?

I’m checking to see if anyone might know of a good way to accomplish what I’m trying to do. I have Customers and Orders. A customer might be the one who ordered a product or whom the product was sold to (yes they can be different. Think of a person ordering for a company). Now my requirement is to find the customers that Lapsed. A Lapsed customer is defined as a customer that has not made a purchase within 2-times their average time period between purchases. For example, if a customers purchase frequency averages 180 days. Then if they have not made a purchase within 360 days of today (2 x 180), then they are lapsed. Hopefully, that makes sense. If you need more information, just ask.

Here is some sample data (Just ignore customer ID 6):DECLARE @Customer TABLE
(
CustomerID INT NOT NULL PRIMARY KEY
)

DECLARE @Order TABLE
(
OrderID INT NOT NULL PRIMARY KEY,
OrderDateNumber INT NOT NULL,
OrderedByCustomerID INT NOT NULL,
SoldToCustomerID INT NOT NULL
)

INSERT @Customer
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5


INSERT @Order
SELECT 1, 20070702, 2, 6
UNION ALL SELECT 2, 20040805, 3, 3
UNION ALL SELECT 3, 20071031, 2, 6
UNION ALL SELECT 4, 20080228, 2, 6
UNION ALL SELECT 5, 20070316, 2, 6
UNION ALL SELECT 6, 20070425, 5, 6
UNION ALL SELECT 7, 20070523, 5, 6
UNION ALL SELECT 8, 20070418, 3, 6
UNION ALL SELECT 9, 20051019, 3, 3
UNION ALL SELECT 10, 20010226, 2, 6
UNION ALL SELECT 11, 20050930, 3, 3
UNION ALL SELECT 12, 20050819, 3, 3
UNION ALL SELECT 13, 20060510, 3, 6
UNION ALL SELECT 14, 20070608, 2, 6
UNION ALL SELECT 15, 20061030, 3, 6
UNION ALL SELECT 16, 20050824, 3, 3
UNION ALL SELECT 17, 20061109, 5, 6
UNION ALL SELECT 18, 20071120, 2, 6
UNION ALL SELECT 19, 20050919, 4, 4
UNION ALL SELECT 20, 20071105, 2, 6
UNION ALL SELECT 21, 20071105, 2, 6
UNION ALL SELECT 22, 20050923, 3, 3
UNION ALL SELECT 23, 20050923, 3, 3
UNION ALL SELECT 24, 20040809, 3, 3
UNION ALL SELECT 25, 20050715, 3, 3
UNION ALL SELECT 26, 20000731, 2, 6
UNION ALL SELECT 27, 20011114, 2, 6
UNION ALL SELECT 28, 20040916, 4, 4
UNION ALL SELECT 29, 20071105, 2, 6
UNION ALL SELECT 30, 20070415, 3, 3
UNION ALL SELECT 31, 20070717, 3, 6
UNION ALL SELECT 32, 20080207, 4, 4
UNION ALL SELECT 33, 20050802, 3, 3
UNION ALL SELECT 34, 20041022, 4, 4
UNION ALL SELECT 35, 20060510, 2, 6
UNION ALL SELECT 36, 20061017, 4, 4
UNION ALL SELECT 37, 20050228, 3, 3
UNION ALL SELECT 38, 20070109, 5, 6
UNION ALL SELECT 39, 20071115, 2, 6
UNION ALL SELECT 40, 20080225, 4, 4
UNION ALL SELECT 41, 20030820, 3, 3
UNION ALL SELECT 42, 20071106, 2, 6
UNION ALL SELECT 43, 20070209, 5, 6
UNION ALL SELECT 44, 20070628, 4, 6
UNION ALL SELECT 45, 20051028, 3, 3
UNION ALL SELECT 46, 20051103, 3, 3
UNION ALL SELECT 47, 20070703, 2, 6
UNION ALL SELECT 48, 20080207, 2, 6
UNION ALL SELECT 49, 20070711, 3, 3
UNION ALL SELECT 50, 20070417, 3, 3
UNION ALL SELECT 51, 20051013, 3, 3
UNION ALL SELECT 52, 20050719, 3, 3
UNION ALL SELECT 53, 20071130, 2, 6
UNION ALL SELECT 54, 20070725, 2, 6
UNION ALL SELECT 55, 20070713, 3, 3
UNION ALL SELECT 56, 20070522, 5, 6
UNION ALL SELECT 57, 20050819, 3, 3
UNION ALL SELECT 58, 20050823, 4, 4
UNION ALL SELECT 59, 20041109, 3, 3
UNION ALL SELECT 60, 20031023, 4, 4
UNION ALL SELECT 61, 20000523, 2, 6
UNION ALL SELECT 62, 20051007, 3, 3
UNION ALL SELECT 63, 20050727, 3, 3
UNION ALL SELECT 64, 20051027, 3, 3
UNION ALL SELECT 65, 20021112, 3, 3
UNION ALL SELECT 66, 20050824, 3, 3
UNION ALL SELECT 67, 20070615, 5, 6
UNION ALL SELECT 68, 20050428, 2, 6
UNION ALL SELECT 69, 20060324, 3, 3
UNION ALL SELECT 70, 20070215, 5, 6
UNION ALL SELECT 71, 20070713, 3, 6
UNION ALL SELECT 72, 20050930, 3, 3
UNION ALL SELECT 73, 20070613, 2, 6


Here is the expected output(EDIT for correct output):CustomerID
3
5

Here are some more detailed results to see some of the calculations I performed:CustomerID MaxOrderDateOrderFrequencyInDays DateDiffOfLastOrder TwoTimesFrequency IsLapsed
-------------------------------------------------------------------------------------------
2 2008-02-28 141.850000 34 283.700000 0
3 2007-07-17 58.896551 260 117.793102 1
4 2008-02-25 176.222222 37 352.444444 0
5 2007-06-15 27.250000 292 54.500000 1


Yes, I already have a solution, but I didn’t want to post it yet because I didn’t want skew anyone’s answer. :)


PS, Sorry for the long post, but I wanted to provide some decent sample data.