October 13, 2016 at 9:37 am
Hi everyone
I'm using 2008 and I need to calculate the time difference (in days) between my customers purchase dates i.e. Say Custommer 0002 has made 3 purchases over the last year I would like to know the time in days between each of these purchases. Also I would like the first purchase for any customer to return 0 as they didn't have a purchase prior to this.
I know in 2012 and later there is the lag function which I guess is what I need to re-create here?
Hope someone can help.
Thanks
BO
October 13, 2016 at 9:55 am
This should get you started. You'll need to tweak it, even if only to get the 0 for the first date.WITH NumberedDates AS (
SELECT
Customer
,PurchaseDate
,ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY PurchaseDate) AS RowNo
FROM Purchases
)
SELECT
n1.Customer
,n1.PurchaseDate
,DATEDIFF(day,n1.PurchaseDate,n2.PurchaseDate)
FROM NumberedDates n1
JOIN NumberedDates n2 ON n1.RowNo = n2.RowNo + 1
John
October 13, 2016 at 10:38 am
Do you want each individual date difference or do you just want an average days' difference between orders? The latter is much less work to calc :-).
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".
October 13, 2016 at 12:21 pm
Thank you both for responding so quickly and for the code.
I was looking to get the differences between each purchase date rather than the average, if that's possible.....
October 14, 2016 at 9:06 am
John Mitchell-245523 (10/13/2016)
This should get you started. You'll need to tweak it, even if only to get the 0 for the first date.WITH NumberedDates AS (SELECT
Customer
,PurchaseDate
,ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY PurchaseDate) AS RowNo
FROM Purchases
)
SELECT
n1.Customer
,n1.PurchaseDate
,DATEDIFF(day,n1.PurchaseDate,n2.PurchaseDate)
FROM NumberedDates n1
JOIN NumberedDates n2 ON n1.RowNo = n2.RowNo + 1
John
I believe that you are missing a join on Customer.
October 14, 2016 at 1:09 pm
Yes, I worked out to add the customer join - eventually!
Any thoughts on how I could measure the date difference between the first ever order of a given customer and their next order - not their last order but their second subsequent order (if they didn't order anything else then that would return 0).
As always any help much appreciated.
BO
October 14, 2016 at 1:38 pm
ByronOne (10/14/2016)
Yes, I worked out to add the customer join - eventually!Any thoughts on how I could measure the date difference between the first ever order of a given customer and their next order - not their last order but their second subsequent order (if they didn't order anything else then that would return 0).
As always any help much appreciated.
BO
If this does not provide the correct answer, please provide sample data and expected result.
CREATE TABLE #Purchases (
Customer VARCHAR(50)
, PurchaseDate DATE
);
GO
INSERT INTO #Purchases ( Customer, PurchaseDate )
VALUES ( 'Customer 0001', '2016-08-13' );
INSERT INTO #Purchases ( Customer, PurchaseDate )
VALUES ( 'Customer 0002', '2016-03-27' )
, ( 'Customer 0002', '2016-05-19' )
, ( 'Customer 0002', '2016-09-11' );
INSERT INTO #Purchases ( Customer, PurchaseDate )
VALUES ( 'Customer 0002', '2016-04-15' )
, ( 'Customer 0003', '2016-07-22' );
GO
WITH NumberedDates AS (
SELECT
Customer
, PurchaseDate
, minPurchDate = MIN(PurchaseDate) OVER (PARTITION BY Customer)
, ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY PurchaseDate) AS RowNo
FROM #Purchases
)
SELECT
n1.Customer
, n1.PurchaseDate
, PrevPurchDate = n2.PurchaseDate
, ISNULL(DATEDIFF(day, n2.PurchaseDate, n1.PurchaseDate), 0) AS DaysSincePrevPurch
, n1.minPurchDate
, ISNULL(DATEDIFF(day, n1.minPurchDate, n1.PurchaseDate), 0) AS DaysSinceFirstPurch
FROM NumberedDates AS n1
LEFT JOIN NumberedDates AS n2
ON n1.Customer = n2.Customer
AND n1.RowNo = n2.RowNo + 1
ORDER BY n1.Customer,n1.PurchaseDate;
GO
DROP TABLE #Purchases;
October 14, 2016 at 1:57 pm
Des - this is perfect - thank you so much for your help - much, much appreciated!
BO
October 14, 2016 at 1:58 pm
ByronOne (10/14/2016)
Yes, I worked out to add the customer join - eventually!Any thoughts on how I could measure the date difference between the first ever order of a given customer and their next order - not their last order but their second subsequent order (if they didn't order anything else then that would return 0).
As always any help much appreciated.
BO
LAG/LEAD may be a good fit for what you are trying to achieve
DECLARE @Offset INT = 2;
SELECT
Customer
, PurchaseDate
, FirstPurchDate = FIRST_VALUE(PurchaseDate) OVER (PARTITION BY Customer ORDER BY PurchaseDate)
, PrevPurchDate = LAG (PurchaseDate) OVER (PARTITION BY Customer ORDER BY PurchaseDate)
, NextPurchDate = LEAD (PurchaseDate) OVER (PARTITION BY Customer ORDER BY PurchaseDate)
, NextPurchDate2 = LEAD (PurchaseDate, @Offset ) OVER (PARTITION BY Customer ORDER BY PurchaseDate)
FROM #Purchases
ORDER BY Customer, PurchaseDate;
October 15, 2016 at 5:43 am
Awesome.
I will play around with both options.
Thanks again for alll your help.
BO
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply