Calculate number of months between dates for multiple records

Question

Calculate number of months between dates for multiple records

LeeFAR

SSCarpal Tunnel

Points: 4644
More actions
October 7, 2015 at 2:08 pm

#305281

I have a challenge and I'm not sure the best route to go. Consider the following dataset.
I have a table of sales. The table has fields for customer number and date of sale. There are 1 - n records for a customer. What I want is a record per customer that has the customer number and the average number of months between purchases. For example, Customer 12345 has made 5 purchases.
CustomerNumber SalesDate
1234 05/15/2010
1234 10/24/2010
1234 02/20/2011
1234 05/02/2012
1234 12/20/2012
What I want to know is the average number of months between the purchases. And do this for each customer.
I know DATEDIFF and probably some partitioning is required here. But I'm stumped on getting something workable. Thoughts?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

LeeFAR (10/7/2015)
I have a challenge and I'm not sure the best route to go. Consider the following dataset.
I have a table of sales. The table has fields for customer number and date of sale. There are 1 - n records for a customer. What I want is a record per customer that has the customer number and the average number of months between purchases. For example, Customer 12345 has made 5 purchases.
CustomerNumber SalesDate
1234 05/15/2010
1234 10/24/2010
1234 02/20/2011
1234 05/02/2012
1234 12/20/2012
What I want to know is the average number of months between the purchases. And do this for each customer.
I know DATEDIFF and probably some partitioning is required here. But I'm stumped on getting something workable. Thoughts?

first off...can you please clarify for the example you have give, what you expect the answer to be please.

it would also be very helpful if you can expand your example data to show all possible variants....please read /refer to this post http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum and post back accordingly.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

John Rowan SSC Guru Points: 56440 More actions · Answer 2

Not sure how large your table is, but here's an example of how to solve the problem with a cte...

DECLARE @Purchases TABLE(CustomerID int, PurchaseDate date)

INSERT INTO @Purchases

SELECT 1234, '05/15/2010' UNION ALL

SELECT 1234, '10/24/2010' UNION ALL

SELECT 1234, '02/20/2011' UNION ALL

SELECT 1234, '05/02/2012' UNION ALL

SELECT 1234, '12/20/2012'

; WITH ctePurchases (RowNum, CustomerID, PurchaseDate)

AS (

SELECTROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY PurchaseDate) RowNum

, CustomerID

, PurchaseDate

FROM@Purchases

)

SELECTp.CustomerID

, AVG(CAST(DATEDIFF(mm, p2.PurchaseDate, p.PurchaseDate) as decimal(10,2))) as avgMonthsDIff

FROMctePurchases p

LEFT JOIN ctePurchases p2 ON p2.RowNum = p.RowNum - 1

GROUP BY p.CustomerID

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

LeeFAR SSCarpal Tunnel Points: 4644 More actions · Answer 3

Thank you John. You have pointed me in a direction that will work. I think kicker was the -1 and I was just not thinking.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

LeeFAR (10/8/2015)
Thank you John. You have pointed me in a direction that will work. I think kicker was the -1 and I was just not thinking.

Maybe. What do you define as a "month"? For example, the following dates are only 3 milliseconds apart but DATEDIFF says they're a month apart. In fact, DATEDIFF also says they're a year apart. This is because it doesn't measure months. Instead, it simply counts temporal boundaries that are crossed in the data.

SELECT Months = DATEDIFF(mm,testdata.StartDt,testdata.EndDT)

,Years = DATEDIFF(yy,testdata.StartDt,testdata.EndDT)

FROM (

SELECT '2015-12-31 23:59:59.997','2016-01-01 00:00:00.000'

) testdata (StartDT,EndDT)

;

Results:

Months Years

----------- -----------

1 1

(1 row(s) affected)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 5

Another possibility to consider:

SELECT CustomerNumber,

CAST(CAST(DATEDIFF(DAY, MIN(SalesDate), MAX(SalesDate)) AS decimal(9, 2)) / (COUNT(*) - 1) / 30.0 AS decimal(9, 2)) AS Avg_Months_Btwn_Sales

FROM dbo.your_tablename

GROUP BY CustomerNumber

--ORDER BY CustomerNumber

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Barcelona10 SSCertifiable Points: 5372 More actions · Answer 6

I have a task similar to this one.

i have client table which has client_id Eff_from and Eff_to columns.Eff_from and Eff_to are the dates that client is eligible for service.I need to know the average number of days from the day that he became not eligible and new eligibility date .

CLIENT_IDEFF_FREFF_TO

1001 12/24/200712/8/2010

1001 12/13/20123/26/2013

1001 5/27/2013 8/2/2013

1001 9/24/2013 10/30/2016

for expl days between

12/8/2010 and 12/13/2012

3/26/2013 and 5/27/2013

8/2/2013 and 9/24/2013

then AVG them

Thank you advance