Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Decent way to calculate Lapse?

Author  Topic 

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2008-04-02 : 13:39:51
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 MaxOrderDate	OrderFrequencyInDays 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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-04-02 : 14:29:16
Where is this Order Frequency field coming from? or is it a calculated one?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-04-02 : 15:27:33
I think OrderFrequence is (MAX(Date) - MIN(Date)) / COUNT(*)
I think DateDiff is MAX(Date) - SECONDMAX(Date).
I have no idea what the other two columns are and how they are calculated.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2008-04-02 : 15:35:45
Yeah, that is soemthing I calculate. It's the average numbe of days between orders for that customer.

For grins here is an example:
OrderDate
---------
20070101
20070110
20070115

So I take
20070110 - 20070101 = 10 days
20070115 - 20070110 = 5 days
15 days total / 3 orders for an average of 5 days (or as I listed above OrderFrequencyInDays)


I'll try to explain the other columns as didn;t doa very good job of that.

Hopefully MaxOrderDate is obvious. :)

The DateDif is the difference of GETDATE() and MaxOrderDate in days.

The TwoTimesFrequency is just 2 * the OrderFrequenceyInDays.

The IsLapsed is true if the DateDiff is less greater than the TwoTimesFrequency.


Hope that helps.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2008-04-02 : 16:39:12
are you sure that the OrderFrequencyInDays is correct for customers 2 and 3 in your example?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2008-04-02 : 16:55:00
acctually i think it's wrong for all of them. or i'm missing a part of a requirement here...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2008-04-02 : 17:14:44
2 and 3 are not as yours are but 4 and 5 are...

DECLARE @Customer TABLE
(
	CustomerID INT NOT NULL PRIMARY KEY
)

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

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

INSERT INTO @Order (OrderID, OrderDateNumberOld, OrderedByCustomerID, SoldToCustomerID)
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
-- set int to datetime
update @order 
set OrderDateNumber = convert(datetime, left(OrderDateNumberOld, 4) + 
							right(left(OrderDateNumberOld, 6), 2) + 
							right(OrderDateNumberOld, 2))


declare @boundryDates table (CustomerId int, MinDate datetime, MaxDate datetime, OrderCount decimal(14, 2))
insert	@boundryDates
select	C.CustomerId, 
		min(O.OrderDateNumber), max(O.OrderDateNumber), 
		count(*)
from	@Order O 
		join @Customer C on C.CustomerID in (OrderedByCustomerID, SoldToCustomerID)			
group by C.CustomerId

select	CustomerId, 
		convert(decimal(14, 2), datediff(day, MinDate, MaxDate)/OrderCount) as OrderFrequencyInDays,
		convert(decimal(14, 2), (datediff(day, MinDate, MaxDate)/OrderCount)) * 2 as TwoTimesFrequency,
		datediff(day, MaxDate, GETDATE()) as DateDiffOfLastOrder	
from	@boundryDates	
	
select	CustomerId
from
(
	select	CustomerId, 
			convert(decimal(14, 2), datediff(day, MinDate, MaxDate)/OrderCount) as OrderFrequencyInDays,
			convert(decimal(14, 2), (datediff(day, MinDate, MaxDate)/OrderCount)) * 2 as TwoTimesFrequency,
			datediff(day, MaxDate, GETDATE()) as DateDiffOfLastOrder	
	from	@boundryDates	
) t1
where DateDiffOfLastOrder > TwoTimesFrequency



_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2008-04-02 : 17:17:48
Good question, I belive that they are all correct. And I think I know what is missing from my requirements...

For CustomerID 2 they had 3 orders on 20071105. I'm only interested in a "puchase day." So, for N number of orders for a particular customer on a particular day is only counted once. So, I'm grouping by day: 
SELECT DISTINCT
	OrderDateNumber 
FROM 
	@Order
WHERE 
	SoldToCustomerID = 2
	OR OrderedByCustomerID = 2
I appreciate you guys taking a look and I'm sorry the logic is not explained the best way.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2008-04-02 : 17:20:00
simple correction then:

declare @boundryDates table (CustomerId int, MinDate datetime, MaxDate datetime, OrderCount decimal(14, 2))
insert	@boundryDates
select	C.CustomerId, 
	min(O.OrderDateNumber), max(O.OrderDateNumber), 
	count( distinct O.OrderDateNumber)
from	@Order O 
		join @Customer C on C.CustomerID in (OrderedByCustomerID, SoldToCustomerID)			
group by C.CustomerId

select	CustomerId, 
	convert(decimal(14, 2), datediff(day, MinDate, MaxDate)/OrderCount) as OrderFrequencyInDays,
	convert(decimal(14, 2), (datediff(day, MinDate, MaxDate)/OrderCount)) * 2 as TwoTimesFrequency,
	datediff(day, MaxDate, GETDATE()) as DateDiffOfLastOrder	
from	@boundryDates	
	
select	CustomerId
from
(
	select	CustomerId, 
		convert(decimal(14, 2), datediff(day, MinDate, MaxDate)/OrderCount) as OrderFrequencyInDays,
		convert(decimal(14, 2), (datediff(day, MinDate, MaxDate)/OrderCount)) * 2 as TwoTimesFrequency,
		datediff(day, MaxDate, GETDATE()) as DateDiffOfLastOrder	
	from	@boundryDates	
) t1
where DateDiffOfLastOrder > TwoTimesFrequency


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2008-04-02 : 17:53:18
Nice! Thanks Spirit, That's pretty similar to what I had come up with. Although, mine had a couple of extra superfolous bits as I couldn't see the forest through the trees. :)
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2008-04-02 : 18:04:21
you're welcome!

do show yours


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site